By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,581 Members | 2,023 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,581 IT Pros & Developers. It's quick & easy.

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

Brilstern
100+
P: 207
I have a database that consist of two primary tables:

Expand|Select|Wrap|Line Numbers
  1. [tblPatrons]
  2. [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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOk_Click()
  2.  
  3. 'DoCmd.SetWarnings False
  4.  
  5.     'run delete query to delete patron from tables
  6.     DoCmd.RunSQL "DELETE tblPatrons.*, tblPatrons.Room FROM tblPatrons " & _
  7.     "WHERE (((tblPatrons.Room)=[Forms]![frmVerify]![Room2]));"
  8.  
  9.     'run update query to update room availability
  10.     DoCmd.RunSQL "UPDATE tblRooms SET tblRooms.Availability = 'Available'" & _
  11.     "WHERE (((tblRooms.Room)=[Forms]![frmVerify]![Room2]));"
  12.  
  13.     DoCmd.Close
  14.  
  15. 'DoCmd.SetWarnings True
  16.  
  17.     DoCmd.SelectObject acForm, "frmHome"
  18.     DoCmd.Requery
  19.     DoCmd.RepaintObject
  20.  
  21. End Sub
Table meta data avaiable if needed.
Jan 28 '13 #1

✓ answered by 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.

Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,374
When in VBA, the SQL engine does not have access to the Forms collections. Move that out of the quotes and concatenate it instead.
Jan 28 '13 #2

NeoPa
Expert Mod 15k+
P: 31,561
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.
Jan 28 '13 #3

zmbd
Expert Mod 5K+
P: 5,397
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.

Expand|Select|Wrap|Line Numbers
  1. '<>Do you have the following lines at the top of your code?
  2. '<> if not, then you should. 
  3. Option Compare Database
  4. Option Explicit
  5. '</>
  6. '
  7. Private Sub cmdOk_Click() 
  8. '<>
  9.    DIM zStrSQL as string
  10. '</>
  11. '
  12.    'DoCmd.SetWarnings False 
  13.    'run delete query to delete patron from tables 
  14.    '
  15. '<>
  16.    zStrSQL = "DELETE tblPatrons.*, tblPatrons.Room " & _
  17.       "FROM tblPatrons " & _
  18.       "WHERE (((tblPatrons.Room)=[Forms]![frmVerify]![Room2]));"
  19. '
  20.    Debug.print " SQL for del patron::> " & zStrSQL
  21.    '
  22.    DoCmd.RunSQL  zStrSQL
  23. '</>
  24.    '
  25.    'run update query to update room availability 
  26. '<>
  27.    zStrSQL = "UPDATE tblRooms " & _
  28.       "SET tblRooms.Availability = 'Available'" & _
  29.       "WHERE (((tblRooms.Room)=[Forms]![frmVerify]![Room2]));"
  30. '
  31.    Debug.print " SQL for query to update room::> " & zStrSQL
  32.    '
  33.    DoCmd.RunSQL  zStrSQL
  34. '</>
  35.    '
  36.    DoCmd.Close 
  37.    '  
  38.    'DoCmd.SetWarnings True 
  39.    ' 
  40.    DoCmd.SelectObject acForm, "frmHome" 
  41.    DoCmd.Requery 
  42.    DoCmd.RepaintObject 
  43.    ' 
  44. 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.
Jan 29 '13 #4

Brilstern
100+
P: 207
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:
Expand|Select|Wrap|Line Numbers
  1. 1.User clicks on cmdCheckOut on switchboard
  2. 2.[frmDeleteMarine] is ran which request the user input room #
  3. 3.User clicks on cmdOk
  4. 4.frmVerify is ran with a filter to auto-populate the form for
  5.   the user to verify the name and room number of the selection
  6. 5.frmDeleteMarine closes
  7. 6.User clicks cmdOk
  8. 7.The first query [qryDeleteMarine] (or SQL statement
  9. imbedded in VBA) runs correctly and deletes the
  10. record from [frmPatrons] where the Room is equal to
  11. the where clause
  12. 8.The second query [qryUpdateAvailablity] (or SQL
  13. statement imbedded in VBA) does not run correctly
  14. 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.
Jan 29 '13 #5

Brilstern
100+
P: 207
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!
Jan 29 '13 #6

NeoPa
Expert Mod 15k+
P: 31,561
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.
Jan 29 '13 #7

Brilstern
100+
P: 207
One lesson learned and another to run into!
Jan 29 '13 #8

Post your reply

Sign in to post your reply or Sign up for a free account.