Setting criteria in a query with VBA

[cola125]

Very junior question, but I am stuck. I have a query ("Query1&quot that contains a list of numbers. I want to run an append query ("Query2&quot that sets the criteria for a field ("field1&quot based on the value in query1.

What I have in mind is code that opens query2, then looks at the first record in query1 and sets the criteria for field1. Then it appends to the table. Loop. Go to the next record in query1 and run query2 again, and so on. Any ideas?

 

[davidwv]

I'm a little confused:
Where does "field1" live?
When you say "Open query2", do you mean execute it?
And does "appends to the table" mean "by running query2"?

Flesh it out just a little more and I'm sure we'll be able to help.

-w

 

[cola125]

field1 is in query2.

Say the first value in query1 is 373. I want the code to open query2 and place "373" in the criteria for field1. The query will append all records where field1 = "373" to the table.

The idea behind the looping is that the code would open query2 again and use the next value in query1 to set the criteria in field1, say "374".

Thanks for your response, hope that clarifies it for you.

 

[davidwv]

Do you have to have 2 queries? Without knowing more detail, it sounds like a single query could handle it (maybe -- sometimes Access doesn't have the flexibility you'd like).

In "normal" SQL, it would be something like (Table2 is the table or tables that Query2 is based on...likewise for Table1):
INSERT INTO MyTable (col1,col2,col3...)
SELECT col1, col2, col3...
FROM Table2 T2
JOIN Table1 T1 ON T1.Field1 = T2.Field1

This may or may not be feasible, depending on the complexity of Query1 and Query2. If you want to copy and paste the SQL behind those queries, I'll take a look.

-w

 

[cola125]

I am looking for something like this below. The difference is that this is filtering a report. I want to filter a query and append to a table.

Function Level_Five()

Dim rs As Recordset
Dim CC_Criteria As String
Dim rpt As Report
Dim FileName As String
Dim Path1 As String
Dim Path2 As String
Dim File As String

Const rptName = "RPT_GLDETAIL_A_CURRENT_MONTH_ROLLUPS_LEVEL_FIVE"
DoCmd.OpenReport rptName, acViewDesign
Set rs = CurrentDb.OpenRecordset("QRY_Region_Name&quot
rs.MoveFirst

Path1 = "\\trndalfs\groups\fpa\public\"
Path2 = "\Exhibits\June\"
File = "Expense_Detail.doc"

Do While Not rs.EOF

CC_Criteria = rs("region&quot
Set rpt = Reports(rptName)
rpt.Filter = "[QRY_GLDetail_A_Current_Month]![region]=" & "'" & CC_Criteria & "'"
rpt.FilterOn = True
FileName = Path1 & CC_Criteria & Path2 & File
DoCmd.OutputTo acReport, rptName, "RichTextFormat(*.rtf)", FileName, False, ""
rs.MoveNext

Loop

DoCmd.SetWarnings False
DoCmd.Close acReport, "RPT_GLDETAIL_A_CURRENT_MONTH_ROLLUPS_LEVEL_FIVE"
DoCmd.SetWarnings False

rs.Close
Set rpt = Nothing
Set rs = Nothing

DoCmd.SetWarnings True

End Function