Web Development·11,946 discussions

Programmatically save a form as a query

Locked

I have a form called Searchform, and I need to be able to save this form (after a filter has been applied, because I need to print off part of the results from the filter) as a query called SearchResults. I have a report based on this query, so everytime the form is saved as a query, it must overwrite the existing query of that name.

The problem is, this database is for use by people with little or no Access skills, and I don’t want to risk giving them the opportunity to make a mistake. Isthere some way to programmatically save the form as a query and automatically save it over the existing query, all without the user having to do a thing?

That’s not very clear, but it’ll have to do.

Topic

Programmatically save a form as a query

In reply to Programmatically save a form as a query

That seems rather complicated, and unless it is a stand alone system (not networked) you are opening up yourself for some real errors. Why not just set the selection criteria on a form, keep that form open and have a query use those parameters from the form. Then run your report and stuff off that query? If you really need to save it, save those parameters in a special table you setup to recall later.

Programmatically save a form as a query

In reply to Programmatically save a form as a query

The question was auto-closed by TechRepublic

Programmatically save a form as a query

In reply to Programmatically save a form as a query

In order to accomplish your current goals programmatically, you can delete the current query through code, then create a new Query Definition based upon your “new” criteria from the form and save it as the Query.

If you do NOT delete your previous query first, an error will occur. I would suggest error trapping anyway, just in case your query was not deleted in the first place.

This would allow you another chance to delete the query or at least notify the user of a potential problem.

In addition, if saving the Query is not necessary, then you could add a command button to your form to run/preview/print the report directly based on the outcome of the search.

I hope this helps.

Programmatically save a form as a query

In reply to Programmatically save a form as a query

The question was auto-closed by TechRepublic

Programmatically save a form as a query

In reply to Programmatically save a form as a query

Here’s another approach. Use your form to build the selection and ordering criteria you want to use for the report. Save them as a PUBLIC variable (let’s call it strWhere).

Your report(s) can then be run from a standard table or query, as appropriate, but use the following code to actually run the report:

DoCmd.OpenReport “reportname”,,strWhere

This will apply the selection and sorting criteria specified in strWhere (which does not include the starting text “WHERE “) to the report.
Note the second comma. And be sure to build strWhere correctly by concatenating dates with # and text with single quote (‘), as is strWhere = “[date] = #” & forms!SearchForm.[specifieddate] & “#”.

Good luck.

Programmatically save a form as a query

In reply to Programmatically save a form as a query

The question was auto-closed by TechRepublic

Programmatically save a form as a query

In reply to Programmatically save a form as a query

This question was auto closed due to inactivity

[Author]

Replies