Need someone else's eyes Update Statement Syntax error

[tasmontique]

Hello everyone,
I keep getting a syntax error in my update statement. The only thing that I notice is
that although the columns in my table have the same name as the fields on the form the compiler keeps changing the name eg

strSQL = strSQL & "', Equipment='" & Me![equipment] (compiler format)

should read
strSQL = strSQL & "', Equipment='" & Me![Equipment].

Also I am trying to format the where clause criteria with a combo box that has two columns. I am wondering if this is giving the error but the compiler does not highlight it.
In the combo box's after update event this works fine

Criteria = "[AirlineCode] &' '& [flightnuminout]" = " & [cboAdhoc]"

rsCust.FindFirst Criteria.

However I am not sure how to concatenate two columns from the same table in the where clause.

I think maybe

1. 'strSQL = strSQL & " WHERE "[AirlineCode] &"' '"& [Flightnuminout]" = " & [cboAdhoc]"
2.strSQL = strSQL & "' WHERE AirlineCode = '" & [cboAdhoc] & ""

3. strSQL = strSQL & "' WHERE AirlineCode And Flightnuminout= '" & [cboAdhoc] & ""

[pks00]

Ok, first off, your first sql, did u end it with single quotes or forget to post that?
i.e.
strSQL = strSQL & "', Equipment='" & Me![Equipment].

should be

strSQL = strSQL & "', Equipment='" & Me![Equipment]. & "'"


Secondly, what are you trying to achieve here

strSQL = strSQL & "' WHERE AirlineCode And Flightnuminout= '" & [cboAdhoc] & ""

Is cboAdhoc a combination of both ArlineCode and Flightnuminout concatenated? If so then try this

strSQL = strSQL & "' WHERE AirlineCode & Flightnuminout= '" & [cboAdhoc] & ""

how many columns in cboAdhoc, u couldbe looking at the wrong column
to verify the value, type this in

msgbox cboAdhoc

[tasmontique]

Thanks for the sql syntax corrections.
My combox is still giving me trouble

The combobox consists of two columns. I search the records to edit with this combobox code

Private Sub cboAdhoc_AfterUpdate()
Dim d As DAO.Database
Dim rsCust As DAO.Recordset
Dim Criteria As String
rsCus
Set d = CurrentDb
Set rsAdhoc = d.OpenRecordset("tblflight1", dbOpenDynaset)
'selects concatenated fields in access table
Criteria = "[AirlineCode] &' '& [flightnuminout]" = " & [cboAdhoc]"

rsAdhoc.FindFirst Criteria


Me!Equipment = rsAdhoc("Equipment")
Me!AirlineCode = rsAdhoc("AirlineCode")
Me!AirlineCode = UCase(Me!AirlineCode)
Me!Flightnuminout = rsAdhoc("Flightnuminout")

This works fine. If this syntax is wrong please correct me.

However in the update table statement I keep getting an error as if it is only recognizing the airlinecode and not the flightnuminout.

When I click on the combobox it displays both columns airlinecode and flightnuminout.

However after it is selected it displays only airlinecode.

I wonder if the combobox can be read as an index eg in sql statement

Where AirlineCode & Flightnuminout = & [cboAdhoc.Value()]" would this allow it to read the two coulumns?
When I try I get an error

[pks00]

When I click on the combobox it displays both columns airlinecode and flightnuminout.

However after it is selected it displays only airlinecode.

this means u have to reference them using column numbers

try this

strSQL = strSQL & "' WHERE AirlineCode = '" & cboAdhoc.Column(0) & ' AND Flightnuminout = '" & cboAdhoc.Column(1) & ""


cboAdhoc.Column(0) returns value in 1st column
cboAdhoc.Column(1) returns value in 2nd column
etc

If any fields are numeric then u drop the wrapping in single quotes

[tasmontique]

Thank you so much this both values are being read in the update statement.

However, the syntax error has come back. After I click the command button a message box from access comes up appearing as if it has passed all the values from the form to the table columns but then the update syntax error comes up again. Here is all my code.

f IsNull(Me!AirlineCode) Then
MsgBox "AirlineCode is a Required Entry.", 48
Me!AirlineCode.SetFocus
Exit Sub
End If

If IsNull(Me!flightnuminout) Then
MsgBox "Flightnuminout is a Required Entry.", 48
Me!flightnuminout.SetFocus
Exit Sub
End If

If IsNull(Me!cboAdhoc) Then
MsgBox "Is this a new addition? If so, use the Add button below to save this record."
Me!cmdAddRec0.SetFocus
Exit Sub
End If


'**** Save Changes ****
Dim db As Database
Set db = CurrentDb
Dim strSQL As String

strSQL = "UPDATE tblflight1 "

strSQL = strSQL & "',SET AirlineCode='" & Me![AirlineCode] & "'"
strSQL = strSQL & "', Equipment='" & Me![Equipment] & "'"
strSQL = strSQL & "', Flightnuminout='" & Me![flightnuminout] & "'"
strSQL = strSQL & "', AirlineName='" & Me![AirlineName] & "'"
strSQL = strSQL & "', STA='" & Me![STA] & "'"
strSQL = strSQL & "', STD='" & Me![STD] & "'"
strSQL = strSQL & "',RouteTo='" & Me![RouteTo] & "'"
strSQL = strSQL & "',RouteFrom='" & Me![RouteFrom] & "'"
strSQL = strSQL & "',Configuration='" & Me![Configuration] & "'"
strSQL = strSQL & "',ProgramIN='" & Me![ProgramIN] & "'"
strSQL = strSQL & "',ProgramOUT='" & Me![ProgramOUT] & "'"
strSQL = strSQL & "',Remarks='" & Me![Remarks] & "'"
strSQL = strSQL & "',Montharr='" & Me![Montharr] & "'"
strSQL = strSQL & "',Monthactivity='" & Me![Monthactivity] & "'"
strSQL = strSQL & "',Arrtime='" & Me![arrtime] & "'"
strSQL = strSQL & "',Deptime='" & Me![deptime] & "'"
strSQL = strSQL & "',Arrivaldate='" & Me![Arrivaldate] & "'"
strSQL = strSQL & "',Departuredate='" & Me![Departuredate] & "'"
strSQL = strSQL & "',WK1='" & Me![WK1] & "'"
strSQL = strSQL & "',WK2='" & Me![WK2] & "'"
strSQL = strSQL & "',WK3='" & Me![WK3] & "'"
strSQL = strSQL & "',WK4='" & Me![WK4] & "'"



strSQL = strSQL & " 'WHERE AirlineCode = '" & cboAdhoc.Column(0) & "' AND Flightnuminout = '" & cboAdhoc.Column(1) & ""
MsgBox strSQL

db.Execute strSQL

MsgBox "Changes to " & Me!AirlineCode & " have been saved."

'**** clear the controls to add more flights ****
Call ClearControls