Pass parameter to query...

[Wrangler36]

I have a listbox and I need to pass all the items selected to a query.

1) If the result I want to pass is:

In (2,3,4,5)

2) and I store this in a text box on my form named "textbox"

3) And the query definition is:

qrydef![Forms!myForm!textbox] = Forms!myForm!textbox

I'm getting the following error: "Item not found in this collection". What am I missing? Do I need quotes or something.

Thanks.

 

[seaport]

If you are using Access 2003, I recommend using the expression build tool to write the query definition since Access 2003 is not so tolerant on code as Access 2000. (I cannot say more since I do not have Access 2003 on my home computer.)

Also, I do not understand what you mean by:

qrydef![Forms!myForm!textbox]

In DAO, the "Querydefs" collection contains all queries.

Seaport

 

[Wrangler36]

My apologies! I left out some important information.

I define the query definition with this stmt:
Set qrydef = db.QueryDefs("myQuery")

The next stmt is supposed to assign the items selected from the list box to the parameter in the query (this is where my error is):
qrydef![Forms!myForm!textbox] = Forms!myForm!textbox


The "qrydef![Forms!myForm!textbox]" is the parameter in the query

The "Forms!myForm!textbox" is the text box located on the form that holds a string containing the items selected from the listbox.

The value of the textbox is, for example, "In (2,5,7,8)"


Is it seeing the "In" stmt as a string when it is passed to the parameter? I'm not sure what the problem is.

 

[seaport]

Let's assume that the 2,5,7,8 are customer IDs in a table tblCustomers. Then the code for the query is:

qrydef.sql="select * from tblCustomers where customerID " & Forms!myForm.form!textbox

Seaport

 

[Wrangler36]

Seaport, the next step after I get the selected items from the listbox is to output the data from the query so that a mailmerge can be run from the data in the query - this is the next stmt:

DoCmd.OutputTo acOutputQuery, "myQuery", acFormatRTF, strDataSource, False

Two questions:
1) Can I use your method to create a physical query using the CreateQueryDef command? I think there has to be a physical query for the mailmerge to work (not sure).

2) If I have multiple users using the same front end (they don't want me to put copies on each user's local drive), and each of them creates a query, how would that work? If they each need to generate different letters at any given time, is there some kind of temporary query I can create that affects only the current user - so that they are not all using the same query to run their mailmerges?

 

[seaport]

To question 1:
You do not need to create a new query every time. You only need to create a query - myQuery - beforehand and then modify its sql statement every time. So the code stays the same.

Set qrydef = db.QueryDefs("myQuery")
qrydef.sql="select * from tblCustomers where customerID " & Forms!myForm.form!textbox
DoCmd.OutputTo acOutputQuery, "myQuery", acFormatRTF, strDataSource, False

To question 2:
I do not think this will be a problem, unless two users click on the button at exactly the same second. If you do want to play safe, here is another set of code.

on error resume next
DoCmd.DeleteObject acQuery, "MyQuery_" & CurrentUser
on error goto 0
Set qrydef = db.CreatequeryDef("myQuery")
qrydef.sql="select * from tblCustomers where customerID " & Forms!myForm.form!textbox
DoCmd.OutputTo acOutputQuery, "myQuery", acFormatRTF, strDataSource, False

I assume that each user needs to log into the database with their user name and password.

Seaport

 

[Wrangler36]

Thanks Seaport. Can you briefly explain the reasoning behind your second suggestion. Are you saying that each query object is tied to each user's logon name? So if you delete it using CurrentUser, you are only deleting one user's copy of the query and creating a new one for them - and the other users are not affected?

I am a complete novice when it comes to query definitions. Please help me understand!

 

[seaport]

Yes, you are right.

Seaport

 

[Wrangler36]

Ok. I will give your method a try. Thank you!