VBA variable needed in Query

[Kicker]

I have searched throughout this forum and have come close but haven't been able to light the cigar yet.

I have an Access Query that joins 2 tables. From a single command button click, I need to run the Query 90 times with different criteria in 2 of the fields. I don't have a problem getting the vba variables. On a table, I can easily use

docmd.applyfilter "Revision = '" & strRevision which returns the appropriate filtered recordset. But this doesn't work in a query.

However, I must use a query and haven't been able to set the "filter" using vba.

I can use Parameters in the query, but I don't have the liberty of entering them individually.

Believe it or not, we are using Access 2002. There is no option on the version.

Any suggestions?
:banghead:

[geekgirlau]

I'm assuming this is an action query - if it just selects records we need to do something different.


Dim qdf As DAO.QueryDef


Set qdf = CurrentDb.QueryDefs("MyQuery")
qdf.Parameters("ParamName") = strRevision
qdf.Execute

Set qdf = Nothing


Also, can you give us some further information about what's happening? There may be a way to structure it so that you're not having to run 90 separate queries!

[Kicker]

Actually, there are two tables that need to be compared on a daily basis. I am looking for adds and drops between the two tables. I was playing with the numbers last night and it is only 30 combinations per day. My reference table is 15 X 3 which makes 90 if you count adds and drops. But once I have the original lists, I only go for the 15 weeks X adds/drops.

Thank you very much for the answer. I will try it when I get to work.

[CreganTur]

You don't have to use a query def to use a VBA variable in a Query.

If the value is in an object on your form, like a textbox, then you can do this simply. In the criteria row where you want to use this value, enter:
[Forms]![FormName]![ObjectName]
where FormName is the name of the form and ObjectName is the name of the object whose value you want to use.

Then click Query-> Parameters and enter the same value in the first column and then select the correct data type in the second column.

If you want to use a straight variable value, then you need to use a Global variable and create a Function that returns the value of that variable. In your query's criteria row, you call that Function by name and that will pull in the value. The function could be like this:
Public Function ReturnDate() As String
ReturnDate = FormattedDate
End Function
You would call this in your query using ReturnDate()

HTH:thumb

[Kicker]

Thanks Randy. The only thing I have on the form is the command button to begin the vba. From what I have, it would be counter productive to have the Revision Codes show on the form. And, yes, I know I can set the visibility to No.

I got the QueryDef working this morning and can live with that. I will, however, play with your idea for some other things I am working on. Thank you both very much.