SQL append query help

[Neekos]

i have code that loops through a recordset and checks to see if a condition is true. If it's true, i want to send that record to a different table, but im having trouble doing so.

i have:

if x = true then
DoCmd.RunSQL "INSERT INTO [tblReady] SELECT [sheet1].* FROM [sheet1] WHERE (([sheet1].[Vendor Number])= recordset.[Vendor Number])"

but when the condition is true, its asking me to enter a value for "recordset.[Vendor Number].

How could i change it so that when the condition is true, it just takes the current record in the recordset and adds that to the new table?

[Neekos]

basically - i just need to know how to reference the current record in an ADO recordset.

[Neekos]

figured it out. forgot about the syntax for referencing a variable.

here's what works:

DoCmd.RunSQL "INSERT INTO [tblReady] SELECT [sheet1].* FROM [sheet1] WHERE (([sheet1].[Vendor Number])= " & recordset.Fields(4) & ")"

[nico5038]

figured it out. forgot about the syntax for referencing a variable.

here's what works:

DoCmd.RunSQL "INSERT INTO [tblReady] SELECT [sheet1].* FROM [sheet1] WHERE (([sheet1].[Vendor Number])= " & recordset.Fields(4) & ")"

Well done :-)
You could have used:

DoCmd.RunSQL "INSERT INTO [tblReady] SELECT [sheet1].* FROM [sheet1] WHERE (([sheet1].[Vendor Number])= " & recordset![Vendor Number] & ")"

when the field has the same name in the recordset. Thus it's easier to understand and when a field is added or removed in the select statement, you won't have to change the fieldnumber.
Also try to drop the usage of spaces in fieldnames. It will reduce the number of errors when coding as you can drop the [ and ] :-)

Nic;o)