"Where" clause in DoCmd, OutputTo acQuery 1

[StayAtHomeMom]

I am using VB code to run a query, but I want to put a Where clause in, so it only runs the query for the RepNo=100 (then RepNo=120, then RepNo=180, etc). Originally, (before I applied VB to it), the query had a parameter that prompted for the RepNo. (RepNo is one of many fields in the design grid for the query) I am trying to avoid that and make everything run automatically, without user intervention. (The RepNos are in a separate table that I'm using a Do-Loop to loop thru and run the query against every RepNo in that table). I cannot find a way to insert a Where clause for the [RepNo]=100, et al. I see how to do it when running a report, or outputting to a form, but not a query. Any suggestions?

 

[vb6novice]

The way to do it is to redefine the query from code.

Sub query_definition_modifier()
Dim myquery As QueryDef
Dim dbs as database
Dim RepNos_rst as Recordset
Set dbs = currentDb
Set myquery = dbs.QueryDefs("name_of_your_query")

Set RepNos_rst = dbs.OpenRecordset("RepNos table")

Do Until RepNos_rst.EOF = True
 
name_of_your_query = "SELECT tblName.FieldName1, tblName.RepNo FROM tblName WHERE (((tblName.RepNo) = " & RepNos_rst![RepNo] & "));"
myquery.SQL = name_of_your_query 

run the query and do whatever you are going to do with it

RepNos_rst.MoveNext
Loop

myquery.Close
dbs.Close

End Sub

The final condition of the query will be looking for a match on whatever is the last RepNo in the RepNo table.

 

[StayAtHomeMom]

Thank you vb6novice!!

After many go arounds with syntax (my query is over 5 lines long with inner joins and left joins, etc), IT WORKED!!! Thanks for helping me save HOURS of manual labor!