Update table with date and number

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

I'm trying to update a table with the current date and the current user ID
but I keep getting a syntax error. Could somebody check my code and tell me
what I'm doing wrong?

Dim strUserID As Integer

strUserID = DLookup("UserID", "q_GetUser")

CurrentDb.Execute "UPDATE (pt_PurchaseOrders) SET
pt_PurchaseOrders.CancelledDate = Date(), pt_PurchaseOrders.CancelledBy =
strUserID WHERE pt_PurchaseOrders.PONumber = " & Me.PONumber
 
JB -

Two things - you want the RunSQL statement (not the Execute), and also the
strUserID needs to have single quotes put around the text as a delimeter.
Try this:

DoCmd.RunSQL "UPDATE (pt_PurchaseOrders) SET pt_PurchaseOrders.CancelledDate
= Date(), pt_PurchaseOrders.CancelledBy = '" & strUserID & "' WHERE
pt_PurchaseOrders.PONumber = " & Me.PONumber

If the PONumber is a text field, it would need the delimeters also.
 
Thanks Daryl.

It works perfectly now.


Daryl S said:
JB -

Two things - you want the RunSQL statement (not the Execute), and also the
strUserID needs to have single quotes put around the text as a delimeter.
Try this:

DoCmd.RunSQL "UPDATE (pt_PurchaseOrders) SET pt_PurchaseOrders.CancelledDate
= Date(), pt_PurchaseOrders.CancelledBy = '" & strUserID & "' WHERE
pt_PurchaseOrders.PONumber = " & Me.PONumber

If the PONumber is a text field, it would need the delimeters also.
Click to expand...
 
Back
Top