recordset syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to insert the value of ClientID into a table via the following
code:
Set rs = db.OpenRecordset("SELECT c.ClientID FROM Client c WHERE c.groupID= 1)

InsertStr = "INSERT INTO m(ClientID, MYear) SELECT " & rs.ClientID & “, " &
plngMYr & " FROM MatchList m;â€

DoCmd.RunSQL Insertstr

WHen I try to run this, it does not recognize rs.ClientID. How do I refer to
the ClientID so that I can use that and insert into the second table? I am
running this in a loop.
 
What does InsertStr look like before you run it? Is it valid SQL?

Is ClientID text? If so, you'll need to include quotes around its value.
 
smk23 said:
I would like to insert the value of ClientID into a table via the following
code:
Set rs = db.OpenRecordset("SELECT c.ClientID FROM Client c WHERE c.groupID= 1)

InsertStr = "INSERT INTO m(ClientID, MYear) SELECT " & rs.ClientID & “, " &
plngMYr & " FROM MatchList m;”

DoCmd.RunSQL Insertstr

WHen I try to run this, it does not recognize rs.ClientID. How do I refer to
the ClientID so that I can use that and insert into the second table? I am
running this in a loop.
Click to expand...


A field is a member of the recordset's Fields collection and
you need to use ! to refer to a member of any collection.
Since Fields is the default collection of a recordset
object, you do not have to specify that's what you're using.


Dot is used to refer to properties and methods.
 
Doug: how do I check to see if it's valid SQL? It looks fine to me. This is
relatively new coding to me. I haven't done this particular task before.

THe ClientID is integer.
 
I tried rs!ClientID and it still prompts for a value, so it's still not
recognizing the values from the recordset.
Thanks for the help.
 
Doug: Thanks. THe SQL Insert statement is posted on my original post below. I
found the error: I deleted the "FROM" clause and it's working now.
I greatly appreciate you guys who help us out!!
Sam
 
Back
Top