Update Query error (1 Viewer)

[ethan.geerdes]

So I'm working on making an update query for a trouble ticket log (more or less) and I'm trying to set the status based on the ID number of the ticket.

My table is set up like this

Table name = tlbTSLog
first column = ID (pk)
second column= Submitted (date/time format)
third column= Date Updated (date/time format)
fourth Column= Completed (date/time format)
fifth column= Equipment (text)
sixth column= Problem (text)
seventh column= Actions taken (text)
eighth column= Resolution (text)
ninth column= Status (text)


My form has a combo box named combo10 label:ID

my update query is as follows.

Dim strSQL As String
strSQL = "Update tblTSLog" & _
" Set Status = 'in progress' , " & _
" [Date Updated] = #" & Now & "# ," & _
" Where [ID] = '" & Me.Combo10 & "';"

Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError


End Sub


I get a syntax error and it highlights the line

CurrentDb.Execute strSQL,dbFailOnError

 

[pbaldy]

It would help to see the result of the print, but the comma before "Where" will cause an error.

 

[pbaldy]

Also, if ID is numeric, you don't want the single quotes around the value.

 

[ethan.geerdes]

Thank you. I appreciate it.

 

[ethan.geerdes]

I took the comma out and now I get a "data type mismatch" error.

 

[Minty]

Did you remove the single quotes around your

" Where [ID] = '" & Me.Combo10 & "';"

As Paul suggested?

 

[ethan.geerdes]

Well, I did what I thought he said, and played with the syntax a little bit doesn't fail now but it also doesn't do what I'm trying to make it do.

This is what I currently have. Where do I go to find out more about the syntax about SQL in access? Thanks guys for your help by the way.


Private Sub Command8_Click()

Dim strSQL As String
strSQL = "Update tblTSLog" & _
" Set Status = 'in progress' , " & _
" [Date Updated] = #" & Now & "# " & _
" Where ID = " & Me.Combo10 & " ;"

Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError


End Sub

 

[Minty]

In the Debug / VBA window open the immediate window (Press ctrl + G) you should see the actual text that strSql is using.

If the problem isn't immediately obvious, copy and paste it into the SQL view of a new blank query. Try and run it , what error comes back if any or look at it in datasheet view and see how many records it pulls in.

 

[ethan.geerdes]

I am starting to suspect that the query works and it is something with my form properties. I took the where line out and it updates status of everything to in progress. I then re-inserted the line and it's not erroring out however, it also won't update the record that is selected in the combo box.

 

[Minty]

What is the bound column of your combo box?

add Debug.Print Me.Combo10 before your code.

 

[ethan.geerdes]

I figured it out. I didn't have the column specified in the combo box in the query.


Private Sub Command8_Click()

Dim strSQL As String
strSQL = "Update tblTSLog" & _
" Set Status = 'in progress' , " & _
" Where ID = " & Me.Combo10.Column(0) & " ;"

Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError


End Sub

and that worked! Thank you guys!