Solved

Saving a filter as a query

Posted on 1998-12-02
21
547 Views
Last Modified: 2008-02-26
I am trying to save a filter as a query with a predefined name which is defined in the code.  If I use the VB statement
  DoCmd.RunCommand acCmdSaveAsQuery
a window appears asking for a filename.
I have exactly the same problem when trying to make a table query using
  DoCmd.RunCommand acCmdQueryTypeMakeTable
  Please can anyone tell me how I supply this name as part of the code.

Thanks in anticipation
John
0
Comment
Question by:jcolles
  • 10
  • 7
  • 3
  • +1
21 Comments
 

Author Comment

by:jcolles
Comment Utility
Edited text of question
0
 
LVL 3

Expert Comment

by:Stefaan
Comment Utility
Hi Jcolles,

What you should do is create a querydefinition.  To do that, you should use something like this :

Dim MyDB As Database, Myqdf As QueryDef

' Use the currently opened Database
Set MyDB = CurrentDB()

' Create the QueryDefinition
Set MyQdf = MyDB.CreateQueryDef("MyQyery", "SELECT * FROM <TableName>;")

.

This sample code will create a QueryDefinition called MyQuery which will select all records from a certain table.  Once you have done this you will find the "MyQuery" on you Query's tabpage from you access database.

Greetings and best regards,


Stefaan Lesage

0
 

Author Comment

by:jcolles
Comment Utility
Stefaan.
Many thanks for your reply.   I think I see what you have done, but I do not see how to use the code.

My form displays a table containing many records of mail order customer details. Each record also contains a number of yes/no fields indicating previous purchases.
Using filter by form an operator can select particular customers for a mail shot, based on previous purchases.   If I could save this filter a query, I could then use it to provide data to a report to print the labels, but every time I have to enter a filename when I save the filter as a query.   If only I could save this filter as a query using a fixed, pre-defined name I would be smiling again.   So near yet so far !!
I hope this helps to explain the situation better

Best Regards John.
0
 
LVL 3

Expert Comment

by:Stefaan
Comment Utility
Why don't you just add a button which saves the current Query and Filter ?

0
 

Author Comment

by:jcolles
Comment Utility
Stefaan.
If I do that then I am back at square one being asked for a filename, and it is this that I am trying to avoid.

Regards John.

0
 

Author Comment

by:jcolles
Comment Utility
The problem still exists, I still cannot save a query as a filter without the operator being asked for a filename.
0
 
LVL 3

Expert Comment

by:Stefaan
Comment Utility
JColles,

If you put a button on your form, and you paste my code in the OnClick event of the button, then it will save the SQL Statement to a Query called MyQuery, or whatever  you want to call it.  If you want to give it a name like "MyFilteredQuery" then the following code will save the query as such :

Dim MyDB As Database, Myqdf As QueryDef

' Use the currently opened Database
Set MyDB = CurrentDB()

' Create the QueryDefinition
Set MyQdf = MyDB.CreateQueryDef("MyFilteredQuery", "SELECT * FROM <TableName>;")


The code won't ask you for a name, since you specified one in the CreateQueryDef function.  You have give it the name MyFilteredQuery, so that is how it will be saved.
0
 

Author Comment

by:jcolles
Comment Utility
Stefaan.
Firstly my apologies for not responding for so long.   Other jobs became more important!.
I have now had a go at using your code, a query is being made, but no filter criteria are added, so it just produces the unfiltered output of the queery 'qAllClients'.  This is the query which is the source for the form which I am using to generate the filter by form.  Have I done something wrong?  The exact code which I am using is:-

Private Sub savquery_Click()
  Dim MyDB As Database, Myqdf As QueryDef

' Use the currently opened Database
Set MyDB = CurrentDb()

' Create the QueryDefinition
Set Myqdf = MyDB.CreateQueryDef("MyFilteredQuery", "SELECT * FROM qAllClients;")

End Sub


Many thanks for your help, this is the nearest I have got yet to a solution !!

Regards John
0
 
LVL 3

Expert Comment

by:Stefaan
Comment Utility
The part "SELECT * FROM qAllClients" will need to be adapted to your needs.  You will have to add a WHERE clausule to the SELECT statement. e.g. :

SELECT * FROM qAllClients WHERE ClientName = "Smith"

Assuming you have a ClientName field, this select statement will select all qAllClients records where the ClientName is equal to Smith.
0
 

Author Comment

by:jcolles
Comment Utility
This is where I seem to go full circle again.  The filter has been designed using filter by form. A number of records have been selected by using 'filter by form', the selection being based on customers who have puchased various items in the past.   It is this filter that I need to save.  I cannot enter a specific string into the query as it will change each time the operator runs a filter.
Perhaps this explains the situation better.
Regards John
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:jcolles
Comment Utility
The original problem still exists, perhaps somebody else could help me.

Stefaan, thanks for responding to my query
0
 
LVL 10

Accepted Solution

by:
brewdog earned 200 total points
Comment Utility
jcolles:

I don't know if you're still working on this. It got buried quite a ways back. I just created a query without the popup box, taking off on the idea that Stefaan had. How about this behind the button on the form for the user who wants to save the filter:

    Dim strSQL As String
    Dim qdf As QueryDef
       
    strSQL = "select * from Table2 where "
    strSQL = strSQL & Me.Filter & ";"
    Set qdf = CurrentDb().CreateQueryDef("FilterNumberOne", strSQL)

This works. My question, though, is wouldn't the user *want* a way to name the query. Otherwise, how would they know how to load it in again if they want to run the same filter? (I'm assuming that's why you want to save it.) At the least, I would think you might want a msgbox telling them the name you gave the query or something.

Hope this helps . . .

brewdog
0
 

Author Comment

by:jcolles
Comment Utility
Brewdog.
Many thanks for your reply I will give it a go.
The reason for this is that the operator selects previous customers off a form by selecting those that have had a particular previous purchase.  these are then printed for a mail shot.  If the printer jams or runs out of ink, I want to be able to reproduce the label print run, allowing the operator to edit it and remove the addresses that have already printed.   I am doing this by saving the filter to a known name so the filter can be re-run at the click of a button.
Perhaps there is a better way of doing this ?

Regards john
0
 
LVL 3

Expert Comment

by:Stefaan
Comment Utility
JColles,

Well, If you let it save the QueryDefenition to a name you specify, it is always possible to reopen that query definition at a later point, by pressing a button or whatever.

I don't quite understand what your actual problem is.  The things brewdog and me told should work, so if you have any other problems, please discribe your question in a more detailled way, so that we can take a look at the actual problem.

0
 
LVL 10

Expert Comment

by:brewdog
Comment Utility
I think this would be a pretty good way to do it, jcolles, if you want to be able to rerun it later. The naming is the only thing that would concern me: if the printer jams and the user needs to rerun the query later, how are they going to know which query to rerun?
0
 

Author Comment

by:jcolles
Comment Utility
Brewdog.
There will be one filename used all the time by the software, so the operator clicks on a button which reopens the filter query 'xxx' or whatever the name is.   Print runs are only done every few weeks, so the query can be overwritten next time a print run is done.
Regards John
0
 
LVL 10

Expert Comment

by:brewdog
Comment Utility
okay, that solves that problem. Does the rest work for you?
0
 
LVL 3

Expert Comment

by:Stefaan
Comment Utility
Hi,

I think the proposed solution should work in that case.  Simply saving the Query using a QueryDef and giving it the appropriate name.  Later you will be able to open the query again using the name you specified.

0
 

Author Comment

by:jcolles
Comment Utility
Brewdog.
Very many thanks for your help, at last  I am getting rid of this long lasting niggle in the database.   It works!!
Just one final query.   Now that I can save the filter as a query, I am opening the query then changing it to a makeTable query so that I can keep an editable list for printing labels.   I use the VBA line.

DoCmd.RunCommand acCmdQueryTypeMakeTable

I now get the same problem again that I have to enter a filename, and I would like to enter a fixed name in the VBA code.   If you could help me with this one, then that is the last of the unsolved niggles.

Thanks again for your help
John.
0
 
LVL 3

Expert Comment

by:Stefaan
Comment Utility
Hi JColes,

You can use the same principle as for saving the Query.  But this time you don't create a Query definition, insted you create a Table Definition and some fields.  Once you have done this you can simply copy all record from the Query to the Table.

Sub NewTable()
   Dim dbs As Database, tdf As TableDef, fld As Field

   Set dbs = CurrentDb
   Set tdf = dbs.CreateTableDef("Contacts")
   Set fld = tdf.CreateField("Contact_Name", dbText, 30)
   tdf.Fields.Append fld
   dbs.TableDefs.Append tdf
End Sub

Or is it something else you want to do ?

0
 
LVL 1

Expert Comment

by:Moondancer
Comment Utility
GREETINGS!

This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.  Also in the Community Support link is a question on how experts can help, if they wish, on the cleaning of old and abandoned questions.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thank you,
Moondancer
Moderator @ Experts Exchange
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now