Update table with date and number

[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

 

[Daryl S]

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.

 

[JB]

Thanks Daryl.

It works perfectly now.

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.

 

[Mr. B]

JB,

Just for the record, the "Currentdb.Execute" is perfectly fine to use when
trying to run any action type query.

To validate this, try the new updated sql statement provided byt Daryl S
with the "Currentdb.Execute" statement.

Not that it really matters that much but I felt that you and others should
know.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm