Using code to run an Update query that incorporates a Parameter

[ShovelinFishHeads]

I am working on some code that is designed to allow a user to switch the value of an ID number associated with a "Status" to the number 6 by simply clicking a cmd button. Button is named 'MakeStatusBillable'. This button is on a form named "MenuItemsLoads" that has a series of ID numbers "written" to a series of text boxes in the header of the form when the form is opened.
The relevant text box involved is named "txtBookingIDFilter"

Since the Update is designed to update the ID number in only one record at a time, I have written some SQL for an Update query that also incorporates "BookingID" as a parameter. See below:

Private Sub MakeStatusBillable_Click()
Dim UpdateSQL As String

UpdateSQL = "UPDATE WatchListQuery SET WatchListQuery.LoadStatusID = 6 WHERE (((WatchListQuery.LoadStatusID)=1 Or (WatchListQuery.LoadStatusID)=2 Or (WatchListQuery.LoadStatusID)=3 Or (WatchListQuery.LoadStatusID)=4 Or (WatchListQuery.LoadStatusID)=5) AND ((WatchListQuery.BookingID)=Eval([MenuItemsLoads]![txtBookingIDFilter])));"

DoCmd.SetWarnings False
DoCmd.RunSQL UpdateSQL
DoCmd.SetWarnings True

End Sub

My question is how do I make this work so that Access doesn't ask me for the BookingID number when the code is run? What am I missing here? Is some other method required?

Thanks everyone!

 

[pbaldy]

Presuming it works otherwise, you have to concatenate the form reference:

...AND ((WatchListQuery.BookingID)=" & Forms![MenuItemsLoads]![txtBookingIDFilter] & ")));"

Hopefully all those parentheses correctly apply the logic, given the mix of OR & AND.

 

[MarkK]

Also, in SQL you don't need to qualify field names if the source table is clear, and your SQL only has one table, so this SQL...

UpdateSQL = _
    "UPDATE WatchListQuery " & _
    "SET LoadStatusID = 6 " & _
    "WHERE LoadStatusID < 6 " & _
        "AND BookingID = Forms!MenuItemsLoads!txtBookingIDFilter"

...might be sufficient.

 

[ShovelinFishHeads]

I was all ready to get this problem solved with all of your help and move on to other programming, but something cropped up.

This is the SQL that the query builder shows me:

UPDATE WatchListQuery SET WatchListQuery.LoadStatusID = 6 WHERE (((WatchListQuery.LoadStatusID)<6) AND ((WatchListQuery.BookingID)=[MenuItemsLoads]![txtBookingIDFilter]));

And this is my code using the concatenation technique:

UpdateSQL = "UPDATE WatchListQuery SET WatchListQuery.LoadStatusID = 6 WHERE (((WatchListQuery.LoadStatusID)<6) AND ((WatchListQuery.BookingID)=" & Forms![MenuItemsLoads]![txtBookingIDFilter] & "));"

Access is telling me that there is an extra ")" in my code.

To me, it looks totally correct.

Is there some other syntax error not associated with a ")"?

Are my own eyeballs lying to me?

 

[MarkK]

The SQL that the query builder shows, IMO, is kludge.

 

[pbaldy]

Get rid of all the parentheses; none are necessary.

 

[ShovelinFishHeads]

Took out all of the () and it now works just like I wanted it to.

Thanks guys!

 

[ShovelinFishHeads]

For reference purposes, this is how the functioning code looks:

Private Sub MakeStatusBillable_Click()
Dim UpdateSQL As String

UpdateSQL = "UPDATE WatchListQuery SET WatchListQuery.LoadStatusID = 6 WHERE WatchListQuery.LoadStatusID<6 AND WatchListQuery.BookingID=" & Forms![MenuItemsLoads]![txtBookingIDFilter] & ";"

DoCmd.SetWarnings False
DoCmd.RunSQL UpdateSQL
DoCmd.SetWarnings True

End Sub

 

[pbaldy]

Glad you got it working.