Web Development

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.

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.

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.

This question was auto closed due to inactivity