SQL Syntax help

[Mike Green]

Hi All
I am trying to insert information from a form into a table. I need to
insert just the information from the displayed record. I am using a command
button to run the SQL but I want to pass the current record number from the
open form to the SQL to send to the table. If I delete the "(RecordNo)" and
substitute just the actual record number all works fine so what is the
syntax I should use? I hav'nt used access for a while so obviously I am a
bit rusty, Please help!
Regards in advance

Mike

******************CODE*****************
Dim strSQLCreateInvoice As String
Dim RecordNo As String

RecordNo = Me.FdJobCardID


'Create the invoice
strSQLCreateInvoice = "INSERT INTO TblInvoice ( FdJobCardRef," & _
"FdInvoiceJCNo, FdInvoiceJobCardClosedDate, FdInvoiceCustomerOrderNo )" & _
"SELECT TblJobCard.FdJobCardID AS FdJobCardRef, " & _
"TblJobCard.FdJobCardNumber AS FdInvoiceJCNo, " & _
"TblJobCard.FdJobCardCloseDate AS FdInvoiceJobCardClosedDate, " & _
"TblJobCard.FdCustomerOrderNo AS FdInvoiceCustomerOrderNo " & _
"FROM TblJobCard " & _
"WHERE (((TblJobCard.FdJobCardID)= (RecordNo)));"

CurrentDb.Execute strSQLCreateInvoice

 

[RoyVidar]

Hi All
I am trying to insert information from a form into a table. I need
to insert just the information from the displayed record. I am using
a command button to run the SQL but I want to pass the current
record number from the open form to the SQL to send to the table.
If I delete the "(RecordNo)" and substitute just the actual record
number all works fine so what is the syntax I should use? I hav'nt
used access for a while so obviously I am a bit rusty, Please help!
Regards in advance

Mike

******************CODE*****************
Dim strSQLCreateInvoice As String
Dim RecordNo As String

RecordNo = Me.FdJobCardID


'Create the invoice
strSQLCreateInvoice = "INSERT INTO TblInvoice ( FdJobCardRef," & _
"FdInvoiceJCNo, FdInvoiceJobCardClosedDate, FdInvoiceCustomerOrderNo
)" & _ "SELECT TblJobCard.FdJobCardID AS FdJobCardRef, " & _
"TblJobCard.FdJobCardNumber AS FdInvoiceJCNo, " & _
"TblJobCard.FdJobCardCloseDate AS FdInvoiceJobCardClosedDate, " & _
"TblJobCard.FdCustomerOrderNo AS FdInvoiceCustomerOrderNo " & _
"FROM TblJobCard " & _
"WHERE (((TblJobCard.FdJobCardID)= (RecordNo)));"

CurrentDb.Execute strSQLCreateInvoice

Try concatenating the value of the control containing this value into
the string - now you're passing the name of it. Say if you have a
control on the form named RecordNo containing this number, then

....
"WHERE TblJobCard.FdJobCardID)= " & Me!RecordNo

Should the datatype of the field be text, you'd need single quotes

....
"WHERE TblJobCard.FdJobCardID)= '" & Me!RecordNo & "'"

 

[Allen Browne]

Concatenate the number into the string:
"WHERE TblJobCard.FdJobCardID = " & RecordNo & ";"

If tdJobCardId is actually a Text field, you need extra quotes:
"WHERE TblJobCard.FdJobCardID = """ & RecordNo & """;"

 

[Mike Green]

Thanks that worked a treat