loop though list of values as criteria for insert statement

[DylansStrings]

Hi I have a basic insert query as below.
basically inserting from another table where ColumnA matches the entered parameter [value]
I have about 500 values to enter from a list or a table though and I wanted to use VBA to loop through the list of values and insert into the tableA for each value. Would I need to use docmd.runSQL enclosed in a foreach loop or loop through the values in a recordset.
Any recommendations would be helpful thanks.

INSERT INTO tableA ( columnA, columnB )
SELECT tableB.ColumnA, tableB.columnB
FROM tableB
WHERE ((tableB.ColumnA)=[Value])

thanks

 

[DrallocD]

You could use:

WHERE tableB.ColumnA IN (100,101,102,103)

or

WHERE tableB.ColumnA IN (SELECT ColumnA FROM tableC)

or did you specifically need to use VBA?

 

[DylansStrings]

thanks, I figured out how to do it in VBA too

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim qdfCurr As DAO.QueryDef
Dim strFolder As String
Dim strSQL As String
  
  Set dbCurr = CurrentDb()
  Set rsCurr = dbCurr.OpenRecordset("SELECT column1 FROM table1")
  On Error Resume Next
DoCmd.RunSQL "DROP QUERY qryTemp"
   Set qdfCurr = dbCurr.CreateQueryDef("qryTemp")
  Do While rsCurr.EOF = False
    strSQL = "INSERT INTO Table1 (column1, column2, column3)" & _
     " SELECT table2.column1, table2.column2, table2.column3" & _
    " FROM Table2 WHERE column1 = '" & rsCurr!column1 & "'"
    qdfCurr.SQL = strSQL
    qdfCurr.Close
   
   DoCmd.RunSQL strSQL
    rsCurr.MoveNext
  Loop
  rsCurr.Close
  Set rsCurr = Nothing
  Set qdfCurr = Nothing
  Set dbCurr = Nothing
End Sub

 

[warzer]

I have same issue and test your suggest code and solved ,,

SELECT Table1.ID, Table1.number
FROM Table1, Table2
GROUP BY Table1.ID, Table1.number
HAVING (((Table1.ID) In (SELECT idd FROM table2)));



tanks for all


warzer hassan