Software

Ask something
Question Locked

Acess VBASQL UPDATE STATEMENT ERROR

0 helpful
2 replies

by acessvbasqlnewbie

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am trying to update a table in acess 2000 n
I keep getting a syntax error. Can someone please help. I have been looking at this for days(not kidding). The only thing that I notice is that the compiler keeps changing some of my field names although they are correct eg

strSQL = strSQL & ", Weekend= " & Me![weekend]

should be

strSQL = strSQL & ", Weekend= " & Me![Weekend]
based on the name of the columns in the table and the fields on the form

Here is my code below:
Dim db As DAO.Database, rsCust As DAO.Recordset
Set db = CurrentDb
Dim strSQL As String

strSQL = "UPDATE tblflight1 "
strSQL = strSQL & "SET AirlineCode= " & Me![AirlineCode]
strSQL = strSQL & ", AirlineName= " & Me![AirlineName]
strSQL = strSQL & ",Flightnuminout= " & Me![flightnuminout]
strSQL = strSQL & ", STA= " & Me![STA]
strSQL = strSQL & ", STD= " & Me![STD]
strSQL = strSQL & "', RouteTo= '" & Me![RouteTo]
strSQL = strSQL & "', RouteFrom= '" & Me![RouteFrom]
strSQL = strSQL & "', Equipment='" & Me![equipment]
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 & ", Weekend= " & Me![weekend]
strSQL = strSQL & ", Weekstart= " & Me![weekstart]
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] & ""

MsgBox strSQL

db.Execute strSQL

MsgBox "You have successfully changed" & UCase(Me!AirlineCode) & " " & UCase(flightnuminout) & " flight", vbOKOnly
Call ClearControls

2 replies

by tony hopkinson

WHERE AirlineCode = '" & [cboAdhoc] &
you've wrapped that string in quotes, but none of the other ones, you could have a similar problems with dates or times depending on locale you are using.

You might find this more intuitive

http://msdn2.microsoft.com/en-us/library/bb243789.aspx

HTHs

PS Until you get more proficient with SQL

Run up access New Query, switch to SQL View
and use that to work out what the query syntax should be. then build it in the code.

PPS
The method above is the classic way of leaving yourself open to a SQL injection attack.
You should not build sql commands off text based user input.

Google SQl Injection for info.

This discussion is closed to new replies.