Update SQL

  • Thread starter Thread starter TRM
  • Start date Start date
T

TRM

I am getting an error - "object needed" on the following
code. Specifically the line with the **. I have tried
using ' "Received" ' instead of the variable - which gave
me a different error. There is a value in the intOrder
and a matching record in the "transactions" table.

Suggestions??

Dim strR As String
strR = "Received"
Me!txtTotRecd = (Me!txtTotRecd + Me!txtTonRecd)
Me!txtTotRecd.Requery

If (Me!txtTotRecd) >= (Me!txtTotalOrder) Then
MsgBox ("This order is complete. The date and
transaction type will be updated.")
** db.Execute "UPDATE Transactions SET
Transactions.TransactionType= """ & strR & """ WHERE
(Transactions.OrderID = " & intOrder & ")"
db.Execute "UPDATE Transactions SET
Transactions.Completed = "" # dteToday # "" WHERE
((Transactions.OrderID = " & intOrder & "))"
 
db isn't assigned anywhere in the code sample you posted: is it assigned
somewhere else?

Assuming you've got a reference set to DAO, the following should work just
as well:

If (Me!txtTotRecd) >= (Me!txtTotalOrder) Then
MsgBox "This order is complete. " & _
"The date and transaction type will be updated."
CurrentDb().Execute "UPDATE Transactions " & _
"SET TransactionType= """ & strR & """, " & _
"Completed = " & Format$(dteToday, "\#mm\/dd\/yyyy\#") & _
"WHERE (Transactions.OrderID = " & intOrder & ")", dbFailOnError

Note a few things I've done differently:

I've combined the two updates into a single statement: there seems to be no
point in using 2 separate statements.

Your syntax was wrong for the date on the 2nd update statement. You would
have ended up trying to set Completed to the literal string # dteToday #,
due to how you put the double quotes. The funky format parameter I'm using
is guaranteed to work, regardless of what the user's short date format has
been set to through Regional Settings (assumign dteToday is declared as a
date-type field)

I've added a "dbFailOnError" parameter, so that you can use Error Handling
if something goes wrong with the update.
 
Thank you! I'll try it! I am still learning the correct
variable references.

TRM
-----Original Message-----
db isn't assigned anywhere in the code sample you posted: is it assigned
somewhere else?

Assuming you've got a reference set to DAO, the following should work just
as well:

If (Me!txtTotRecd) >= (Me!txtTotalOrder) Then
MsgBox "This order is complete. " & _
"The date and transaction type will be updated."
CurrentDb().Execute "UPDATE Transactions " & _
"SET TransactionType= """ & strR & """, " & _
"Completed = " &
Click to expand...
Format$(dteToday, "\#mm\/dd\/yyyy\#") & _
 
Back
Top