General discussion

  • Creator
    Topic
  • #2082942

    Programmatically save a form as a query

    Locked

    by m.r. chambers ·

    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.

All Comments

  • Author
    Replies
    • #3897572

      Programmatically save a form as a query

      by fofa ·

      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.

    • #3897526

      Programmatically save a form as a query

      by adamdrobnis ·

      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.

    • #3895098

      Programmatically save a form as a query

      by bob sellman ·

      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.

    • #3742182

      Programmatically save a form as a query

      by m.r. chambers ·

      In reply to Programmatically save a form as a query

      This question was auto closed due to inactivity

Viewing 3 reply threads