473,327 Members | 1,976 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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

Brilstern
208 100+
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.

7 7610
Rabbit
12,516 Expert Mod 8TB
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
32,556 Expert Mod 16PB
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
5,501 Expert Mod 4TB
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
208 100+
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
208 100+
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
32,556 Expert Mod 16PB
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
208 100+
One lesson learned and another to run into!
Jan 29 '13 #8

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

Similar topics

2
by: Santosh | last post by:
Hi, I have a script running which fails when it tries to execute an Update query, but when i try to run the query from the SQLQueryAnalyzer it runs fine. Can somebody try and explain this...
6
by: Nicolae Fieraru | last post by:
Hi All, I was trying to update a field in a table, based on the results from a query. The table to be updated is tblCustomers and the query is qrySelect. This query has two parameters, provided...
2
by: NigelMThomas | last post by:
I have an especially challenging problem. I know there are a few geniuses in this group so perhaps; you can advise me whether or not this can be done as an update query in Access. Thanks. I am...
9
by: DP | last post by:
hi., i've got 3 tables, customer, film and filmrental. i've got a customer form, with a sub form at the bottom, which is a film rental subform. i've created an update query, which when a...
0
by: Dave | last post by:
I have table to update a QUANTITY field using c# Express. What statement should I put in the query builder wizard? A select statement or an Update statement? Do I must have a select statement with...
3
by: hharriel | last post by:
Hi All, I have created an update query related to high school course information (name of course; credit hour; course description, etc.) I am updating a master course information table. I am...
5
by: colleen1980 | last post by:
Hi: In my table there is a field of type checkbox. I create a button on my form and wants to deselect all the checkboxes in that field (PrintQueue). Table: Research_New PrintQueue Format Yes/No...
3
by: turtle | last post by:
I have Two tables (Table1 and Table2). Both tables have a common field called part number. Table 1 contains an extra field that i would like to update table 2 to match if the part number matches....
13
by: Neil | last post by:
I'm running an update query in SQL 7 from QA, and it runs forever. Has been running for 20 minutes so far! The query is quite simple: update a single field in a table, based on a join with another...
1
by: giovannino | last post by:
Dear all, I did a query which update a sequence number (column NR_SEQUENZA) in a table using a nice code (from Trevor !). 1) Given that I'm not a programmer I can't understand why...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.