Syntax Error please help!

[Mike Green]

Hi All
I keep getting Syntax error on the code shown below. What am I doing wrong?
It's really bugging me now please help!

Dim strSQLAppend As String
Dim strSQLDelete As String
Dim strCaravan As String
Dim strCustomer As String
strCaravan = Me.CaravanID
strCustomer = Me.CustomerID
'Archive the Customer
strSQLAppend = "INSERT INTO Tbl_Customer_Archive (CustomerID,
CustomerTitle, CustomerGName," & _
"CustomerSName, CustomerAddres1, CustomerAddres2, CustomerAddres3,
CustomerTown," & _
"CustomerCounty, CustomerPostal, CustomerEmail, CustomerHPhone,
CustomerWPhone, CusteomerMPhone, CustomerRemarks ) " & _
"SELECT Tbl_Customer.CustomerID" & _
"Tbl_Customer.CustomerTitle," & _
"Tbl_Customer.CustomerGName, " & _
"Tbl_Customer.CustomerSName," & _
"Tbl_Customer.CustomerAddres1," & _
"Tbl_Customer.CustomerAddres2," & _
"Tbl_Customer.CustomerAddres3," & _
"Tbl_Customer.CustomerTown," & _
"Tbl_Customer.CustomerCounty," & _
"Tbl_Customer.CustomerPostal," & _
"Tbl_Customer.CustomerEmail," & _
"Tbl_Customer.CustomerHPhone," & _
"Tbl_Customer.CustomerWPhone," & _
"Tbl_Customer.CusteomerMPhone," & _
"Tbl_Customer.CustomerRemarks," & _
"FROM Tbl_Customer WHERE (Tbl_Customer.CustomerID = strCustomer);"

CurrentDb.Execute strSQLAppend

I have cut the rest of the code as the error is in this part.
Thanks in Advance.

 

[Nikos Yannacopoulos]

Mike,

I see two things towards the end:

"Tbl_Customer.CustomerRemarks," & _
"FROM Tbl_Customer WHERE (Tbl_Customer.CustomerID = strCustomer);"

1. there should be a space sepatrating the last dield in the SELECT
clause from the FROM keyword, whereas you have a comma
2. strCustomer is a variable, so it should be outside the quotes to be
treated as such.

So, change the last couple of lines to:

"Tbl_Customer.CustomerRemarks " & _
"FROM Tbl_Customer WHERE (Tbl_Customer.CustomerID = '" & _
strCustomer & "');"

Hope I have not missed anything else...

Nikos

 

[Mike Green]

Hi Nikos
Thanks for the feed back. Unfortunatly it still does not work. I now get
the error "Run-time error 3601 Too few perameters. Expected 1"
Any help that you can give will be appreciated.

Regards

Mike

 

[Nikos Yannacopoulos]

Yep... one more syntax error; in the first line of the SELECT clause,
you have omitted the comma to separate the first from the second field.

Where your code reads:
"SELECT Tbl_Customer.CustomerID" & _

Change to:
"SELECT Tbl_Customer.CustomerID," & _

Tip: when constructing SQL statements (or any text string) in code, use
Debug.Print to print it in the immediate window (made visible with
Ctrl+G); this helps identify syntax errors. In this case, you would add
this line:

Debug.Print strSQLAppend

right after you have constructed the string, before you execute it. When
you get your code running, you can comment out or delete the Debug.Print
(though it won't actually hurt if forgotten in there).

HTH,
Nikos