VBA UPDATE Help

[cstickman]

Hello everyone,

I am stuck on an update query within VBA. I debug.print window and it shows all the data correctly, but not updating to the table. The table name is travelcalendar and the fields I would like to update are:
updated (short text)
newclosingcall (date/time)
newfinalreport (date/time)
newdocfiling (date/time)
newchangedby (short text)
newentered (date/time)

WHERE is auditid (short name)

Here is what I have:

strSQL = ""
strSQL = strSQL & "UPDATE travelcalendar " & vbCrLf
strSQL = strSQL & "SET [travelcalendar].[updated] = '" & Me.txtyes & "', [travelcalendar].[newclosingcall] = #" & Me.txtnewclosing & "#, [travelcalendar].[newfinalreport] = #" & Me.txtnewfinal & "#, [travelcalendar].[newdocfiling] = #" & Me.txtnewdoc & "#, " & vbCrLf
strSQL = strSQL & " [travelcalendar].[newchangedby] = '" & Me.txtuser & "', [travelcalendar].[newentered] = #" & Me.txtcreateddate & "#, " & vbCrLf
strSQL = strSQL & "WHERE [travelcalendar].[auditid] = '" & Me.txtauditidf & "' "

On Error Resume Next

CurrentDb.Execute strSQL

If Err.Number <> 0 Then
Debug.Print strSQL
MsgBox strSQL
Exit Sub
End If

Any help or suggestions would be greatly appreciated. Thanks!!

 

[pbaldy]

What is the result of the debug.print? This may help:

CurrentDb.Execute strSQL, dbFailOnError

 

[Cronk]

As well, leave out the vbCrLf's and format your date variables
eg format(Me.txtcreateddate,"mm/dd/yyyy")

Also, is [travelcalendar].[auditid] a numeric field; if so, leave out the single quotes which are only required for text.

 

[cstickman]

pbaldy:
Here is what shows on the debug.print screen. I also made the code smaller easier to read and test.

UPDATE [travelcalendar]
SET [travelcalendar].[updated]='Yes',[travelcalendar].[newclosingcall]=
#03/01/2016#,
WHERE [travelcalendar].[ID]=1

**NEW CODE**

 Dim strSQL As String
 strSQL = ""
strSQL = strSQL & "UPDATE [travelcalendar] " & vbCrLf
strSQL = strSQL & "SET [travelcalendar].[updated] = '" & Me.txtyes & "', [travelcalendar].[newclosingcall] = #" & Format(Me.txtnewclosing, "mm/dd/yyyy") & "#, " & vbCrLf
strSQL = strSQL & "WHERE [travelcalendar].[ID] = " & Me.txtauditidf & " "
 On Error Resume Next
 CurrentDb.Execute strSQL, dbFailOnError
 If Err.Number <> 0 Then
Debug.Print strSQL
MsgBox strSQL
Exit Sub
End If

Cronk:
I made your suggestions on the format thank you, but when I remove the vbCrLf as suggested it gives me an Access Window with False as the error. No details or anything just False. When I add it back in then I get the error window above.

Do I need to set a reference within VBA? I am using Access 2013 if that makes a difference. Thanks guys!!

 

[pbaldy]

Try pasting that SQL into a new query and trying to run it. If updated is a Yes/No field, you don't want single quotes around the value.

 

[cstickman]

pbaldy, the yes/no is just a text field. I will copy the SQL into a query and try it. I will report back with my results.

 

[MarkK]

Looks like there's a comma after the last field before the WHERE clause. That should be removed.

 

[cstickman]

I got!! Below is the code that worked!! Thank you so much pbaldy, MarkK, and Cronk!! I was missing a ; at the end of the WHERE statement and removing the comma as MarkK suggested did the trick!!

 Dim strSQL As String
  
 strSQL = ""
    strSQL = strSQL & "UPDATE [travelcalendar] " & vbCrLf
    strSQL = strSQL & "SET [travelcalendar].[updated] = '" & Me.txtyes & "', [travelcalendar].[newclosingcall] = #" & Format(Me.txtnewclosing, "mm/dd/yyyy") & "# " & vbCrLf
    strSQL = strSQL & "WHERE [travelcalendar].[auditid] = '" & Me.txtauditidf & "'; "
    
On Error Resume Next
    
    CurrentDb.Execute strSQL
    
    If Err.Number <> 0 Then
        Debug.Print strSQL
        MsgBox strSQL
        Exit Sub
    End If

Thanks again everyone!!

 

[cstickman]

That code works great, but here is the problem. On the update statement it is requiring that all fields be filled out in order to update it. In my case the user may only update one field. The other two may not need to be updated. How do I change it so that it only updates the field that the user wants to update?

Thanks

 

[cstickman]

Hey, I have it figured out. I just made three Variants and added them to my SQL statement and now it works like a charm!! Thanks everyone!!