473,320 Members | 2,145 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,320 software developers and data experts.

How to get SQL stored query into VBA

I am wondering how to i get the value of a SQL query that i have stored to display the outcome of its value
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.  
  3.  
  4. Dim I As Integer
  5. Dim varNumber As Integer
  6. Dim x As String
  7.  
  8. varNumber = Quantity 'box from form
  9.  
  10. Stop
  11.  
  12. For I = 1 To varNumber
  13.  
  14.     x = "SQLToFindLowestRackNumber"
  15.  
  16.     Debug.Print "order number = " & I & ";  SQL value = " & x
  17.  
  18. Next I
  19.  
  20. End Sub
My stored query is called "SQLToFindLowestRackNumber" if i could get this to display in the debug window along with the order number it will hopefully set me on my way.

I have been looking around and the majority of tutorials describe how to call SQL which is manually typed into the VBA and not through a stored query.
Any help will be greatly appricetated.
Thansk
Jul 2 '12 #1

✓ answered by TheSmileyCoder

First:
Expand|Select|Wrap|Line Numbers
  1. varNumber = Quantity 'box from form
should for clarity be written:
Expand|Select|Wrap|Line Numbers
  1. varNumber = Me.Quantity
to illustrate that Quantity is something from the form (the Me being a reference to the form (or report))
or even better:
Expand|Select|Wrap|Line Numbers
  1. varNumber = Me.txtQuantity
Where you have renamed the textbox to include the txt, clearly illustrating that its a textbox, when you use it in your code.
Your code will work fine without these changes, but its not best practice.




Now to get the SQL stored in a query, you can do like so:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as string
  2. Dim strQueryName as string
  3. strQueryName="SQLToFindLowestRackNumber"
  4. strSQL=currentdb.QueryDefs(strQueryName).SQL
The [code]strSQL[/icode] now has a copy of the SQL syntax in your query, but it doesn't execute it, nor return any values, and im guessing that is what you actually want.

If your query only returns 1 record, you can use the Dlookup to get that like so:
Expand|Select|Wrap|Line Numbers
  1. Dim strReturnValue as Variant 'Choose other data type as appropriate
  2. strReturnValue=Dlookup("[Insert Field Name Here]",strQueryName)
  3.  

3 4798
ahd2008
68
Hi,

I think you need to use Recordset to run a SQL defined within the VBA. In your case, you need to define your query SQLToFindLowestRackNumber as String variable

Expand|Select|Wrap|Line Numbers
  1. Dim SQLToFindLowestRackNumber as String
  2.  
Then Assign your SQL statment to your string variable

Expand|Select|Wrap|Line Numbers
  1. SQLToFindLowestRackNumber="Select ..."
Finally, to get the result of your query:

Expand|Select|Wrap|Line Numbers
  1. x=CurrentDb.OpenRecordset(SQLToFindLowestRackNumber ).Collect(0)
  2.  
  3.  
I hope that help
Jul 3 '12 #2
Thank you so much ahd2008 works a treat. On the final bit of code what does the
Expand|Select|Wrap|Line Numbers
  1. .collect(0)
do? does this get the lowest value?Thanks again
Jul 3 '12 #3
TheSmileyCoder
2,322 Expert Mod 2GB
First:
Expand|Select|Wrap|Line Numbers
  1. varNumber = Quantity 'box from form
should for clarity be written:
Expand|Select|Wrap|Line Numbers
  1. varNumber = Me.Quantity
to illustrate that Quantity is something from the form (the Me being a reference to the form (or report))
or even better:
Expand|Select|Wrap|Line Numbers
  1. varNumber = Me.txtQuantity
Where you have renamed the textbox to include the txt, clearly illustrating that its a textbox, when you use it in your code.
Your code will work fine without these changes, but its not best practice.




Now to get the SQL stored in a query, you can do like so:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as string
  2. Dim strQueryName as string
  3. strQueryName="SQLToFindLowestRackNumber"
  4. strSQL=currentdb.QueryDefs(strQueryName).SQL
The [code]strSQL[/icode] now has a copy of the SQL syntax in your query, but it doesn't execute it, nor return any values, and im guessing that is what you actually want.

If your query only returns 1 record, you can use the Dlookup to get that like so:
Expand|Select|Wrap|Line Numbers
  1. Dim strReturnValue as Variant 'Choose other data type as appropriate
  2. strReturnValue=Dlookup("[Insert Field Name Here]",strQueryName)
  3.  
Jul 4 '12 #4

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

Similar topics

1
by: David | last post by:
Hi everyone. I have read every page that Google returns on this topic, but can't find anything that resolves my problem. Basically, I have an Access Database that does a number of different...
1
by: Jack | last post by:
Hi, I am working on a asp page where I am opening a recordset object using an Access stored query named "GMISExpenseCombo". I have to use this resultset with various fields in the page. Howeve,r...
0
by: Mike Knight | last post by:
I have the following code in an Excel 2003 module that creates a query in MS Access 2003. The created query can then be opened from Access. In Access, right-mouse clicking in design mode, in...
0
by: sean | last post by:
Hi I am trying to execute an ms access stored query, I keep getting an error when I try to execute the code.I know that I am missing some declarations in my script, I am just not sure on the...
1
by: AJ | last post by:
Folllowing on from a previous post, i have created a stored query as follows. SELECT c.ID, c.Company_Name, p., 1 As QueryNbr FROM (Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID) LEFT...
2
by: splever | last post by:
I have a update query which updates correctly in the query window, but not when executed in vba. The VBA is ====================================================== strSQL = "UPDATE " & _...
1
by: Mayhem05 | last post by:
I have an Access 2003 database that I need to write some VBA code for to populate a table. The table is based on a query I have built in Access queries. Right now I have 2 parameters that are...
1
by: jaegertw2 | last post by:
I've been working on this for a while and I can't get the syntax right. I'm trying to use code to change the criteria of a stored query so I can toggle whether or not Null values are included in the...
0
ADezii
by: ADezii | last post by:
One frequently asked question at TheScripts is "Should I use a Stored Query or an SQL Statement in those situations that require a Query (RecordSets, RecordSources, Append, Delete, Update Operations,...
1
by: Thelma Roslyn Lubkin | last post by:
I have a form with several comboboxes whose contents I'm trying to initialize from a table. The table, ComboSelections, has 3 fields, an autonumber ID and two text fields, ListCode and ListEntry....
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...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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)...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.