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

How to get SQL stored query into VBA

P: 44
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.  

Share this Question
Share on Google+
3 Replies


P: 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

P: 44
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
Expert Mod 100+
P: 2,321
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

Post your reply

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