So after years of getting great value from the site I've been stumped and need to post my first question! Thanks for all the advice in advance and sorry of the questions seems overly simple.
I'm trying to grab a name in the ProductsXref table and use it in a query that appends records to a table. I tried to use this code below but it doesn't work and I get an error. I'm struggling to know if I'm stepping through the rows AND if the SQL code will work. Thanks!
Dim rs As DAO.Recordset
Dim sSQL As String
Dim aSQL As String
Dim sValue As String
sSQL = "SELECT * FROM ProductsXref"
Set rs = CurrentDb.OpenRecordset(sSQL)
DoCmd.SetWarnings False
rs.MoveFirst
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
While (Not rs.EOF)
sValue = rs("Myfield")
'sSQL = "SELECT V2.customer_id, V2.customer_name, V2." & sValue & ", svalue AS Expr1 FROM V2 WHERE (((V2." & sValue & ")>0));"
DoCmd.RunSQL "INSERT INTO V2_Amend ( customer_id, customer_name, svalue, Expr1 )SELECT V2.customer_id, V2.customer_name, V2." & sValue & ", svalue AS Expr1 FROM V2 WHERE (((V2." & sValue & ")>0));"
rs.MoveNext
Wend
End If
rs.Close
Set rs = Nothing
End Sub
I'm trying to grab a name in the ProductsXref table and use it in a query that appends records to a table. I tried to use this code below but it doesn't work and I get an error. I'm struggling to know if I'm stepping through the rows AND if the SQL code will work. Thanks!
Dim rs As DAO.Recordset
Dim sSQL As String
Dim aSQL As String
Dim sValue As String
sSQL = "SELECT * FROM ProductsXref"
Set rs = CurrentDb.OpenRecordset(sSQL)
DoCmd.SetWarnings False
rs.MoveFirst
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
While (Not rs.EOF)
sValue = rs("Myfield")
'sSQL = "SELECT V2.customer_id, V2.customer_name, V2." & sValue & ", svalue AS Expr1 FROM V2 WHERE (((V2." & sValue & ")>0));"
DoCmd.RunSQL "INSERT INTO V2_Amend ( customer_id, customer_name, svalue, Expr1 )SELECT V2.customer_id, V2.customer_name, V2." & sValue & ", svalue AS Expr1 FROM V2 WHERE (((V2." & sValue & ")>0));"
rs.MoveNext
Wend
End If
rs.Close
Set rs = Nothing
End Sub