SQL/Update Query not running when ran from VBA but is running manually

[Brilstern]

I have a database that consist of two primary tables:

[tblPatrons]
[tblRooms]

I have created a button that deletes a patron from the tables [tblPatrons] INNERJOIN [tblRooms] by opening a form requesting the user input the room to update. The second part of this action updates the table [tblRooms].[Availability] to Available where [tblRooms].[Room] is the typed room. When inserted and ok'd the following code is ran and the delete query works correctly. The update does not. It gives no error code but just shows 0 rows updated. When ran mannualy as a query the Update Query works correctly.

Private Sub cmdOk_Click()

'DoCmd.SetWarnings False

    'run delete query to delete patron from tables
    DoCmd.RunSQL "DELETE tblPatrons.*, tblPatrons.Room FROM tblPatrons " & _
    "WHERE (((tblPatrons.Room)=[Forms]![frmVerify]![Room2]));"

    'run update query to update room availability
    DoCmd.RunSQL "UPDATE tblRooms SET tblRooms.Availability = 'Available'" & _
    "WHERE (((tblRooms.Room)=[Forms]![frmVerify]![Room2]));"
    
    DoCmd.Close

'DoCmd.SetWarnings True

    DoCmd.SelectObject acForm, "frmHome"
    DoCmd.Requery
    DoCmd.RepaintObject

End Sub

Table meta data avaiable if needed.

[Rabbit]

When in VBA, the SQL engine does not have access to the Forms collections. Move that out of the quotes and concatenate it instead.

[NeoPa]

Stevan, Try How to Debug SQL String first. You will often find your problems on your own that way. If not, at least you are much further down the line than posting VBA code for a SQL problem.

[zmbd]

Pretty much on the same page as the rest of the guys;

What I've done below is taken your code EXACTLY as you've written it in the code block in the OP.

- I've then tweeked it a tad by adding the two Option statments... which you should already have at the top of you form's code; however, if not then you should.

- I've added a string variable within your click event code.

- I then pulled the strings from your RunSQL command lines...exactly as you have them... no changes except for readability.

- I add a debug print following these so that you can see EXACTLY how your string is being sent to the SQL engine. To see this, open your form, run your event, then press <ctrl><g>, the VBA window will open and you can see what is happening.

'<>Do you have the following lines at the top of your code?
'<> if not, then you should. 
Option Compare Database
Option Explicit
'</>
'
Private Sub cmdOk_Click() 
'<>
   DIM zStrSQL as string
'</>
'
   'DoCmd.SetWarnings False 
   'run delete query to delete patron from tables 
   '
'<>
   zStrSQL = "DELETE tblPatrons.*, tblPatrons.Room " & _
      "FROM tblPatrons " & _
      "WHERE (((tblPatrons.Room)=[Forms]![frmVerify]![Room2]));"
'
   Debug.print " SQL for del patron::> " & zStrSQL
   '
   DoCmd.RunSQL  zStrSQL
'</>
   '
   'run update query to update room availability 
'<>
   zStrSQL = "UPDATE tblRooms " & _
      "SET tblRooms.Availability = 'Available'" & _
      "WHERE (((tblRooms.Room)=[Forms]![frmVerify]![Room2]));"
'
   Debug.print " SQL for query to update room::> " & zStrSQL
   '
   DoCmd.RunSQL  zStrSQL
'</>
   '
   DoCmd.Close 
   '  
   'DoCmd.SetWarnings True 
   ' 
   DoCmd.SelectObject acForm, "frmHome" 
   DoCmd.Requery 
   DoCmd.RepaintObject 
   ' 
End Sub

Refering to the above revised code: Lines 18 and 29, the "where clauses..."

What Rabbit is trying to tell you is the the SQL engine does not see the forms; thus, it is unable to pull the information from the form for use within Lines 18 and 29.

OK, now you ask, "I took this from the Query Designer" why does it work when I run the query Directly within the Access UI? Why this works when you use a form and refer to it from within the query designer is that Access knows that it needs the form information; thus, opens your form, pulls the information from it and then sends the resolved SQL string with that information to the engine, not a reference to the form.

VBA runsql doesn't know this in that it almost too smart for its own good; therefor, you have to do the same thing from within VBA that the UI does for you within the Access window. This is fairly easy.

Take Line 18:
Yours: "WHERE (((tblPatrons.R oom)=[Forms]![frmVerify]![Room2]));"

What would be expected:"WHERE ((('" & tblPatrons.Room )=[Forms]![frmVerify]![Room2] & "'));"
NOTE: I've added a " ' " single quote in there as I don't know if the value in the control is a string or not... IF this is a numeric then remove the single quotes otherwise you will receive a type mismatch error.

Make this change, leave the debug stuff, run this code with the change.
Now again... see the difference in the Debug window.

I leave the remaining change for you to complete.

[Brilstern]

Update: Order of actions

To all. Thank you for your input. To be more clear on my issue. The SQL itself works fine. I can run each of these as an update query and it works both manually and if the VBA initiates the update query for the first SQL statement: "delete marine". My problem lies in the second SQL statement. When ran within the code as a query or SQL statement it does not read the exact same value that the previous one just did.

Order of actions:

1.User clicks on cmdCheckOut on switchboard
2.[frmDeleteMarine] is ran which request the user input room #
3.User clicks on cmdOk
4.frmVerify is ran with a filter to auto-populate the form for
  the user to verify the name and room number of the selection
5.frmDeleteMarine closes
6.User clicks cmdOk
7.The first query [qryDeleteMarine] (or SQL statement
imbedded in VBA) runs correctly and deletes the
record from [frmPatrons] where the Room is equal to
the where clause
8.The second query [qryUpdateAvailablity] (or SQL
statement imbedded in VBA) does not run correctly
9.[frmVerify] closes

This is what throws me off about this because it works in the first instance but not the second. I will try to do the debug method and see if I can identify the issue.

[Brilstern]

Ok, so I figured it out!

It was the order of operations that caused this issue. I was removing the record that I had filtered changing the "filtered value of [Room2] to a null value for the second statement. Simply putting the room update above the deletion of the record solved my problem. I found this out thanks to the reading of NeoPa's thread on SQL debugging. Using MsgBox() I displayed the [Room2] value prior the running of each SQL statement and realized my error. Thank you very much for all of your input!

[NeoPa]

Good for you for sorting that out Stevan. It's true you need to do everything that relies on the record before deleting it. I'm sure you won't fall over that one again.

[Brilstern]

One lesson learned and another to run into!