Trying to update record in table from form using VBA

irunergoiam

Registered User.
Local time
Yesterday, 18:57
Joined
May 30, 2009
Messages
76
It's been quite some time since I ventured onto the Forum. I would be most appreciative if someone far wiser than me might enlighten me on the following:

I'm flummoxed over how to do an UPDATE using VBA on a form to update a specific record on the table using an unbound field on the form to filter the update.

Every time the code runs, it tells me: Run-time error '3144': Syntax error in UPDATE statement and takes me to the "CurrentDb.Execute strSQL, dbFailOnError" line at the end of my sample below.

Here's my code:


Dim strSQL As String
Dim strCriteria As String

strSQL = ""
strSQL = strSQL & " UPDATE [tblTicket] SET"
strSQL = strSQL & " ([UpdatedBy], [AssignedTo], [Requestor], [Dept])"
strSQL = strSQL & " Values"
strSQL = strSQL & " ('" & unbEnteredBy & "','" & cmbAssignedTo & "','" & cmbRequestor & "','" & cmbDepartment & "')"
strSQL = strSQL & "Where [tblTicket]![DateTimeOpened] = #" & FORMS!frmTicketTracker.unbDateTimeOpened & "#;"
CurrentDb.Execute strSQL, dbFailOnError
 
Hi
The way I fault find this is to use debug.print SQL
From the intermediate window I can cut and paste the SQL into a query.
Trying to run this query or even to look at the design view helps :)
You may find something out of place

Hope this helps

T
 
Try this:

This page will show you the correct syntax for the UPDATE statement

Code:
Dim strSQL As String
Dim strCriteria As String

strSQL = "UPDATE tblTicket SET [UpdatedBy]='" & unbEnteredBy & "', " _
    "[AssignedTo]='" & cmbAssignedTo & "', [Requestor]='" & cmbRequestor & "', " _
    "[Dept]='" & cmbDepartment & "' " _
    "WHERE tblTicket.DateTimeOpened = #" & FORMS!frmTicketTracker.unbDateTimeOpened & "#;"

CurrentDb.Execute strSQL, dbFailOnError
 
I guess I'm not too adept at using the DeBug.Print and Intermediate Window. I entered the DeBug.Print strlSQL and reran the code, but it now tells me I have duplicate alias.
 
Thanks TJPoorman. I copied and pasted the code you provided, but everything displays in red (as if there were ticks at the beginning of the line). Do I need to modify what you provided for it to work?
 
Add an & before the underscores at the end of the lines
 
Try this..
Code:
Dim strSQL As String
Dim strCriteria As String

strSQL = "UPDATE tblTicket SET [UpdatedBy]='" & unbEnteredBy & "', " [COLOR=Red][B]& _[/B][/COLOR]
         "[AssignedTo]='" & cmbAssignedTo & "', [Requestor]='" & cmbRequestor & "', " [COLOR=Red][B]& _[/B][/COLOR]
         "[Dept]='" & cmbDepartment & "' " [COLOR=Red][B]& _[/B][/COLOR]
         "WHERE tblTicket.DateTimeOpened = " & [COLOR=Red][B]Format([/B][/COLOR]Forms!frmTicketTracker!unbDateTimeOpened[COLOR=Red][B], "\#mm\/dd\/yyyy\#")[/B][/COLOR]

CurrentDb.Execute strSQL, dbFailOnError
 
pr2-eugin, it worked!! Thank you, thank you and to the others that replied thanks for leaving me a little wiser than when I started this thread.

Cheers!
 

Users who are viewing this thread

Back
Top Bottom