Need to do an SQL "UPDATE [Table] INNER JOIN" operation in VBA

[adammorrisey]

Hey guys, I'm trying to add some SQL to a button click subroutine in VBA, but I'm not really sure how to use SQL in VBA, esspecially an UPDATE or an INNER JOIN operation. So I was wondering if someone here can help me or even write out what I need to put in my subroutine.

Here is my SQL statement:

UPDATE INVENTORY INNER JOIN SIGN_OUT ON
INVENTORY.Inventory = SIGN_OUT.InventoryID
SET INVENTORY.StatusID = 0;

Purpose of this sql statement is that when you sign a tool out using a form, its sets the corresponding tool in the inventorys status id value to 0 (not available).

If you need anything else let me know, thanks!

 

[Sketchin]

Dim StrSQL As String

If Me.Dirty Then Me.Dirty = False

StrSQL = "UPDATE tblReservations SET tblReservations.[CategoryID] = " & Me.[txtCategoryID] & "  " & _
" ,  tblReservations.[Portfolio] = " & Me.[ChkPortfolio] & " " & _
" WHERE tblReservations.[ReservationID] = " & Me.[TxtReservationID] & " "
DoCmd.RunSQL (StrSQL)

Let me know if you need more clarification, but all you have to do it change the StrSQL = "Your statement here" part

 

[adammorrisey]

What doe the "= False statement mean" ?

 

[Sketchin]

Pbaldy "I think at the point you select from the dropdown, the record hasn't been saved yet, thus there's no record to update. Try adding this before the SQL runs:

If Me.Dirty Then Me.Dirty = False "

This is the advice Pbaldy gave me a while back...