By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,858 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

SQL append query help

100+
P: 111
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:

Expand|Select|Wrap|Line Numbers
  1. if x = true then
  2. 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?
Oct 10 '07 #1
Share this Question
Share on Google+
3 Replies


100+
P: 111
basically - i just need to know how to reference the current record in an ADO recordset.
Oct 10 '07 #2

100+
P: 111
figured it out. forgot about the syntax for referencing a variable.

here's what works:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT INTO [tblReady] SELECT [sheet1].* FROM [sheet1] WHERE (([sheet1].[Vendor Number])= " & recordset.Fields(4) & ")"
Oct 10 '07 #3

nico5038
Expert 2.5K+
P: 3,072
figured it out. forgot about the syntax for referencing a variable.

here's what works:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT INTO [tblReady] SELECT [sheet1].* FROM [sheet1] WHERE (([sheet1].[Vendor Number])= " & recordset.Fields(4) & ")"
Well done :-)
You could have used:
Expand|Select|Wrap|Line Numbers
  1. 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)
Oct 11 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.