General discussion

Locked

Save filter as query, MSAccess 97

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

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Save filter as query, MSAccess 97

by shmaltz In reply to Save filter as query, MSA ...

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.

Collapse -

Save filter as query, MSAccess 97

by M.R.Chambers In reply to Save filter as query, MSA ...

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

Collapse -

Save filter as query, MSAccess 97

by Bob Sellman In reply to Save filter as query, MSA ...

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.

Collapse -

Save filter as query, MSAccess 97

by M.R.Chambers In reply to Save filter as query, MSA ...

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

Collapse -

Save filter as query, MSAccess 97

by donq In reply to Save filter as query, MSA ...

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.

Collapse -

Save filter as query, MSAccess 97

by M.R.Chambers In reply to Save filter as query, MSA ...

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

Collapse -

Save filter as query, MSAccess 97

by M.R.Chambers In reply to Save filter as query, MSA ...

This question was closed by the author

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums