Syntax Error in SQL statement

[BJS]

I'm getting a syntax error in the following SQL statement.
The whole statement is actually very long, so I am breaking it up.

I am testing just part of it, but I get a syntax error message:

strSQL = "UPDATE tblCustomer SET StartDate = #" & txtStartDate & "#," _
& "EndDate = #" & txtEndDate & "#," _
& "Name = " & cboName & "," _
& "WHERE CustomerId = " & txtCustomerId & ";"


Can anyone help me with this.

Thanks!
BJS

 

[Travis]

Try adding this line after you set the strSQL variable

Debug.Print strSQL

Then copy and paste it into the SQL view of a new query.

Then goto design view, if you don't get an error attempt to run it from the query. This should generate the error with more detail.

 

[dcx693]

You might want to consider not using "Name" as a fieldname in your tables/queries. It's a reserved word in VBA.

 

[MattS]

As dcx693 said, using Name isn't good practice. That aside, if Name is a text field (and therefore cboName is text) you need to put apostrophes around it:

& "Name = '" & cboName & "'," _

HTH,

Matt.

 

[Mile-O]

& "Name = " & cboName & "," _

It's the comma that's causing your problem.

You may also have problems with the SQL because there are no spaces between each clause:

What you had:

strSQL = "UPDATE tblCustomer SET StartDate = #" & txtStartDate & "#," _
& "EndDate = #" & txtEndDate & "#," _
& "Name = " & cboName & "," _
& "WHERE CustomerId = " & txtCustomerId & ";"

What I say:

strSQL = "UPDATE tblCustomer SET StartDate = #" & txtStartDate & "#, " _
& "EndDate = #" & txtEndDate & "#, " _
& "Name = """ & cboName & """" &  _
& "WHERE CustomerId = " & txtCustomerId & ";"

 

[BJS]

Thanks everyone!

I replaced my code with Mile-O-Phile's:

strSQL = "UPDATE tblCustomer SET StartDate = #" & txtStartDate & "#, " _
& "EndDate = #" & txtEndDate & "#, " _
& "Name = """ & cboName & """" & _
& "WHERE CustomerId = " & txtCustomerId & ";"

The above gave me error:
"Compile Code, Expected Expression"

I tried this instead, and it works:


strSQL = "UPDATE tblCustomer SET StartDate = [txtStartDate]," _
& "EndDate = [txtEndDate]," _
& "CustName = [cboName]" _
& "WHERE tblCustomer.CustomerID = [Forms]![frmCustomer]![CustomerID];"


When writing the sql statement the other way, I seem to run into alot of problems with putting the additional quotes, comma's etc. in the correct places. Is it bad practice to write the sql statement the way I'm showing I got it to work?

 

[dcx693]

Well, if your code is working, that's the important thing...but I can't imagine how it is.

Your code:

strSQL = "UPDATE tblCustomer SET StartDate = [txtStartDate]," _
& "EndDate = [txtEndDate]," _
& "CustName = [cboName]" _
& "WHERE tblCustomer.CustomerID = [Forms]![frmCustomer]![CustomerID];"

compares fields to literal field names. What I mean is, for example, that you are taking the tblCustomer.CustomerID field and seeing if it is equal to the string "[Forms]![frmCustomers]![CustomerID]", not the value that is stored in the [Forms]![frmCustomers]![CustomerID] control.

Edit: Mile's code should work with the removal of one ampersand like this:

strSQL = "UPDATE tblCustomer SET StartDate = #" & txtStartDate & "#, " _
& "EndDate = #" & txtEndDate & "#, " _
& "Name = """ & cboName & """" _
& "WHERE CustomerId = " & txtCustomerId & ";"

 

[Mile-O]

Yeah, my mistake.

The only thing that I'll say is that you'd be better off just building a stored QueryDef rather than building one in code as the SQL stated above is in no way dynamic.

Furthermore, as it is not stored it is created each time that particular part of code is run. The space taken up by "on the fly" queries is not lost until compacted.

So, either change to a stored QueryDef; or compact often.

 

[BJS]

Thanks again everyone!!

I will try Mile-O-Phile's code again, but without the additional "&".
I will also experiment with a QueryDef.

I should probably explain what I am doing:

1. I have a form that contains only unbound text boxes.
2. The user fills in the information and clicks on the "Save" button.
3. The SQL statement is behind the "Save" button. It is saving the values of unbound text boxes on the form to the table.

This must be why my other way is working, I want to write the values from the unbound text boxes on the form to the table, where the customer id of the form matches the customer id in the table.

Does this make more sense? Thanks again for your patience!

BJS

 

[WayneRyan]

BJS,

Code for the Add/Save Button:

Dim dbs As DAO.Database
Dim sql As String
Dim rst As DAO.RecordSet

Set dbs = CurrentDb
sql = "Select * from YourTable Where CustomerID = " & Me.CustomerID & ";"
Set rst = dbs.OpenRecordset(sql)
If rst.EOF and rst.BOF Then
   MsgBox("New record, do insert.")
   rst.AddNew
   rst!CustomerID = Me.CustomerID
   rst!OtherField = Me.OtherField
   rst.Update
Else
   MsgBox("Old record, do update.")
   rst.Edit
   rst!OtherField = Me.OtherField
   rst.SomeField = Me.SomeField
   rst.Update
End If

Wayne

 

[Rich]

What's the objective of using unbound textboxes and a lengthy procedure?

 

[WayneRyan]

Rich,

Unbound forms do make things more complicated!

Just following the specs.

Merry Christmas,
Wayne

 

[BJS]

Thanks for your code Wayne!

I did not design this database, I have inherited it.
Just fixing some bugs in the database.

I would like to ask the question though:

Is it not better to just base the form on the table and not worry about saving data from unbound text boxes to the table?

I have seen it done both ways. Which is the better way?
I personally have always based the form on a table...no save button.

I look forward to comments on this.

BJS

 

[BJS]

Thank you for the code, Wayne.

I did not design this database; I inherited it.
Just fixing some bugs for the users.

I would like to ask though:

What is the best method of saving data to the table?
1. Using unbound text boxes on the form with a save button?
2. Basing the form on the table, no save button.

I personally always use method no. 2.
But I have often seen method no. 1.

I would be interested in comments on which method is conceived to be the best practice and why.

Thanks,
BJS

 

[WayneRyan]

BJS,

There is always some discussion about bound versus unbound
forms.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=59582

If you use the Search Facility here and look for "unbound" you
should find some useful info.

Wayne

 

[Sue Regan]

You still need a space before the WHERE

Sue

--------------------------------------------------------------------------------strSQL = "UPDATE tblCustomer SET StartDate = #" & txtStartDate & "#, " _
& "EndDate = #" & txtEndDate & "#, " _
& "Name = """ & cboName & """" _
& "WHERE CustomerId = " & txtCustomerId & ";"--------------------------------------------------------------------------------