General discussion

  • Creator
    Topic
  • #2096141

    Save filter as query, MSAccess 97

    Locked

    by m.r.chambers ·

    I need to know how to save a filter as query programatically, without having to prompt the user for a query name and whether they want to save over an existing query of the same name.
    The situation is a search-engine type form where the user selects the criteria for the search from a form (thus creating the filter – filter by form). To print the search results, a need to save the filter as a query named “Print”, which has a report based on it. At the moment I have to ask the user to name the query and to OK copying over the existing query.
    Is there a way to do this with VBA?

    M.R.Chambers

All Comments

  • Author
    Replies
    • #3834927

      Save filter as query, MSAccess 97

      by shmaltz ·

      In reply to Save filter as query, MSAccess 97

      Why use filter by form?
      Create a form that collects info from the user and then use the info collected to create a query.
      You crate a form that has a List box control or a combo box that lists all tables in the database by using the table collection of the database object. The user selects the table they want to filter by, then you give the user a list with all Fields in that table they want to filter by and then the values they want to filter by then you collect the info in a string and you issue a openrecordset command with the created string. You can even have a table that saves the query for the users so they dont have to recreate it.

      • #3834923

        Save filter as query, MSAccess 97

        by m.r.chambers ·

        In reply to Save filter as query, MSAccess 97

        Reponse 1: Nope, the situation calls for a filter by form: I’m on a time constraint and already have the filter by form part working properly except for the save as query so I can print a report. All I need to know is how to name a new query in VBA and get rid of all the prompts.
        Thanks anyway

    • #3836467

      Save filter as query, MSAccess 97

      by bob sellman ·

      In reply to Save filter as query, MSAccess 97

      I believe I’ve done something similar to this where I was building a query from a form which allows the user to build the criteria and then saves the results as a query for use later.

      Obviously, the query name must be one which you don’t use anywhere else and you must have the confirmations of query updates, deletes, etc., turned off (in Tools).

      Here’s the relevant part of the code I used:

      ‘ set the database variable dbs to the
      ‘ current database
      Set dbs = CurrentDb
      ‘ trap the error that occurs when trying
      ‘ to delete the query if it does not
      ‘ currently exist
      On Error GoTo NoQuery
      ‘ delete previous definition of query
      dbs.QueryDefs.Delete (“Yourqueryname”)
      NoQuery:
      ‘ set your SQL string (be sure to properly
      ‘ handle string and date variables when
      ‘ building the SQL string
      strSQL = {enter your SQL statment here}
      ‘ create the query
      Set qdf = dbs.CreateQueryDef (“Yourqueryname”, strSQL)
      ‘ open the query (or do whatever you need,
      ‘ such as printing the reportbased on
      ‘ the query
      DoCmd.OpenQuery qdf.Name, , acReadOnly
      ‘ reset the database variable
      Set dbs = Nothing

      This has worked quite well for me with a user form I made for the user to build the selection and sorting parameters, since I wanted the user to be able to look both at details and at summary (grouping) results to show subtotals based on the sorting selections made by the user.

      • #3848423

        Save filter as query, MSAccess 97

        by m.r.chambers ·

        In reply to Save filter as query, MSAccess 97

        This is quite similar to what I ended up doing yesterday: I went to the MS site and found some code to do the trick. I ended up sending the filter infor straight to a report based on the same query that the search form was, skipping the search query altogether. But thanks anyway.

        M.R.Chambers

    • #3836401

      Save filter as query, MSAccess 97

      by donq ·

      In reply to Save filter as query, MSAccess 97

      I do exactly what you are attempting all the time!

      First I build a Select query that produces exactly what I want and save it as “MyQueryName”.

      Then I build a report based on the query formatted as required and save it as “MyReportName”.

      Then I construct an unattached Form named “GetMyFilters” with one field for each “constraint” I entered in MyQueryName. I generally try to restrict data entry using combo boxes in GetMyFilters so users can’t enter something that doesn’t exist.

      I then re-open MyQueryName and replace each constraint with the appropriate “Forms!GetMyFilters.FieldNameX” and test it when the form is open.

      Once that works I go to the control that calls MyQueryName and change it to open GetMyFilters. Then on GetMyFilters I add a “Go” button that calls MyReportName which in turn calls MyQueryName from the filter form insuring it is open when the query is ultimately called.

      Good luck.

      • #3848424

        Save filter as query, MSAccess 97

        by m.r.chambers ·

        In reply to Save filter as query, MSAccess 97

        Thanks…not quite what I was after: I already had the DB structure complete and didn’t have time to change much (it had to be finished by today – left it till the last minute as per usual!) This may well have worked better for what I want than what I did, but I’ll have to wait for next time.

        M.R.Chambers

    • #3848422

      Save filter as query, MSAccess 97

      by m.r.chambers ·

      In reply to Save filter as query, MSAccess 97

      This question was closed by the author

Viewing 3 reply threads