I am wondering how to i get the value of a SQL query that i have stored to display the outcome of its value -
Private Sub Command0_Click()
-
-
-
Dim I As Integer
-
Dim varNumber As Integer
-
Dim x As String
-
-
varNumber = Quantity 'box from form
-
-
Stop
-
-
For I = 1 To varNumber
-
-
x = "SQLToFindLowestRackNumber"
-
-
Debug.Print "order number = " & I & "; SQL value = " & x
-
-
Next I
-
-
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
First: - varNumber = Quantity 'box from form
should for clarity be written:
to illustrate that Quantity is something from the form (the Me being a reference to the form (or report))
or even better: - 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: - Dim strSQL as string
-
Dim strQueryName as string
-
strQueryName="SQLToFindLowestRackNumber"
-
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: - Dim strReturnValue as Variant 'Choose other data type as appropriate
-
strReturnValue=Dlookup("[Insert Field Name Here]",strQueryName)
-
3 4798
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 - Dim SQLToFindLowestRackNumber as String
-
Then Assign your SQL statment to your string variable - SQLToFindLowestRackNumber="Select ..."
Finally, to get the result of your query: - x=CurrentDb.OpenRecordset(SQLToFindLowestRackNumber ).Collect(0)
-
-
I hope that help
Thank you so much ahd2008 works a treat. On the final bit of code what does the
do? does this get the lowest value?Thanks again
First: - varNumber = Quantity 'box from form
should for clarity be written:
to illustrate that Quantity is something from the form (the Me being a reference to the form (or report))
or even better: - 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: - Dim strSQL as string
-
Dim strQueryName as string
-
strQueryName="SQLToFindLowestRackNumber"
-
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: - Dim strReturnValue as Variant 'Choose other data type as appropriate
-
strReturnValue=Dlookup("[Insert Field Name Here]",strQueryName)
-
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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 " & _...
|
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...
|
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...
|
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,...
|
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....
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |