I need to use the values from a SQL statement in a new SQL INSERT INTO Statement. How can I do that?
Here is my current code:
[indent]
Private Sub cmdSelect_Click()
Dim SQL As String
Dim strSQL As String
Dim sWhere As String
Dim iCount As Integer
Dim i As Integer
Dim j As Integer
Dim iSelectHowMany As Integer
Dim aSel() As Integer
Dim IsRepeated As Boolean
Dim vItem As Variant
Dim tdf As DAO.TableDef
Dim Fld As DAO.Field
Dim rst As DAO.Recordset
Dim strSQL1 As String
Dim strSQL2 As String
Dim strTableName As String
iSelectHowMany = 25
DoCmd.SetWarnings False
‘********************************
DoCmd.OpenQuery “qry_DeleteArchive”
DoCmd.OpenQuery “qry_RandomAudits”
Call AddCounter(“tbl_temp”, “Audit_ID”)
DoCmd.OpenQuery “qry_RunAudit”
‘************************************
SQL = “Select * from [tbl_Audit_Archive]”
‘ Find number of records in table.
iCount = DCount(“*”, “tbl_Audit_Archive”)
Randomize
i = Int(iCount * Rnd()) + 1
ReDim aSel(0)
aSel(0) = i
j = 1
Do While j < iSelectHowMany
IsRepeated = False
Do While Not IsRepeated
i = Int(iCount * Rnd()) + 1
For Each vItem In aSel
If vItem = i Then
IsRepeated = True
Exit For
End If
Next vItem
If Not IsRepeated Then
ReDim Preserve aSel(j)
aSel(j) = i
j = j + 1
Exit Do
End If
Loop
Loop
For Each vItem In aSel
sWhere = sWhere & ", " & vItem
Next
SQL = SQL & " where [Audit_ID] in (" & Mid(sWhere, 2) & ")"
Me.subform.Form.RecordSource = SQL
DoCmd.RunSQL "INSERT INTO myAudits(Member_id, Load_Date) SELECT Member_ID, Load_Date FROM SQL"
DoCmd.SetWarnings True
End Sub
[/indent]
I get the “can not find the input table or query SQL” message.
What am I doing wrong now? PLEASE HELP!!
