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.Room)=[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.