Running Query in Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I have a question regarding queries. How can I run a select query in code?

I have a form that gets data from user to determine what information to
query for (ie. all parts, 1 part, time frame, etc). Once the user has made
all the selections and pressed the button to continue, the code recreates an
existing query (used for multiple forms) and then runs another query for a
new form to build a chart. Basically it goes Form > Query > Query > Chart
(in Form)

Now I need to find a way to run both queries within the code and without
having to explicitly run the form.

I found a way to do it using DoCmd.OpenQuery("qryName") but it also opens
the query. Not surprising since it's called OpenQuery. But I can't find a
way to run the queries without opening them. And if I do nothing, and just
base the chart off the queries, it's meaningless. I need to run the queries
if the chart is to be displayed properly.

Any help is appreciated.

Thanks,
Jay
 
try

DoCmd.SetWarnings False ' turn off system warnings
DoCmd.OpenQuery "queryname", acNormal, acEdit
DoCmd.SetWarnings True ' turn on system warnings
 
Jay;

You can do this by building your query in the query builder switch to SQL
view. Cut and past the code in VBA and assign the variables where you need
them.

be advised that you need to very detailed with the SQL because I feel that
it can be very touchy with what it likes and does not like!
 
Unfortunately, it still opens the queries. But thanks for the idea. I'll
play with that to see if anything works.

Jay
 
Richard,

I might be misunderstanding you, but if I do run the SQL code using
DoCmd.RunSQL, I get an error because I can't run a SELECT statement.

Would have to create a table everytime the code is run? That seems the only
way around it, if I want to run the SQL in VBA. Maybe I'm missing something.

One query is based on another query. I don't think moving the SQL code to
VBA would affect anything other than needing some minor changes. I simply
thought it'd be easier to run the query as a query (as opposed to hardcoded
SQL) in case the query changes later. Especially if it is so picky.

Thanks,
Jay
 
Hi Jay,

You can build your SQL in the first form and pass that to the next form using
the OpenArgs property. Then, in the new form's Open event, you can pull that
SQL back out. This code may give you the idea.

If Not IsNull(Me.OpenArgs) Then
sSql = Me.OpenArgs
End If

' Then you can assign the forms record source to the SQL statement:
Me.RecordSource = sSql
Me.Requery

Hope this helps,
RD
 
Running a SELECT query in code doesn't do anything since the point of
the a SELECT query is to display records which can't be done in code.
Its like displaying the Desert Tray in the kitchen of a restaurant.
There's no point to it since the customer's can't see it.

Now, in reading the question I didn't follow what exactly is going on.
Is this correct?

1. Get data from a user for a query

2. Code recreates a query
-What do you mean by 'RECREATES'??? Describe what this query does

3. Code runs another query for a new form to build a chart
-Action query (INSERT, DELETE, UPDATE) or SELECT???
Describe this one as well.
 
Jay,
I am not 100% sure what your need is but if I have understood you right then
Your need seem to be as stated below :

1) Open the Users Form for him to supply you with the Parameters for some
Report in Chart Form
2) Depending on the Users Responses you need to Create a Query.
3) Based on the newly generated query you need to open another either Report
or a Form.
Now if the above is correct and that is what you need do it this way.
1) Do the Step 1 as normal
2) As said by some other user in the Code keep the SQL statement of your
original Query pasted in the code for reference.
3) Dynamically create just the Exact need ful Query Statement ( I mean just
a Statement as a String ) with the different parameter Values as given by
users. and put that in a String Variable say ...... sQry = "Select * from
xTable where Field1 = " & [Form Field1 Provided by user] & " and ........ "
etc
4) Pass this string to the Report/Form (If you cannot pass the String as
Parameter to the form use sQry string variable as a global variable)
5) And in the OpenForm Event of the New Form set the above query string as
Control Source....

This should play the trick.
 
Hi David,
I hope I can clear up any questions here:

1) Get data from user to create a query. User selects from a variety of
options to basically mine the table for specific data. Some examples are:
Part Number (all or one), Serial Number (all or one), Store Number (all, one
or many), date range (from month/year, to month/year).

2) Using that data above and a bunch of if statements, I create a SQL
Select statement. But the SQL statement can be different every time, so what
I do is I create a query each time. But if the query already exists (under
the predetermined name) then I delete the query and recreate it. Here's the
code:
dbs.QueryDefs.Refresh
For Each qdf In dbs.QueryDefs
If qdf.Name = "qryReportRequested" Then
dbs.QueryDefs.Delete qdf.Name
End If
Next qdf
Set qdf = dbs.CreateQueryDef("qryReportRequested", strSQL)

I did it this way because it's possible multiple forms would need to use the
query for displaying data. It seemed easier to do it this way than to hard
code the SQL into the form. Maybe I'm misguided in this assumption, so I've
very open to ideas.

3) To create the chart, I needed to get certain data from the query the
user has defined. Stuff like Max and Min of certain fields. But this is a
constant query. It's for a specific report, which will never change.
Because of that I created the query, instead of hardcoding.

My issue is, since the data from query in 2) can change, the query in 3)
does not always have the most up-to-date data. But if I open (run) the
query, then the data is properly inserted and the chart in 3) is using the
most relevant data.

Using DoCmd.OpenQuery() does what I'm looking for, but it opens the query as
well. I don't want that. I just what the data in each query updated without
the user seeing it being done.

Hope that clears it up. I'm no pro, so I'm open to any other ideas. I also
have to look into other solutions people have posted.

Thanks,
Jay
 
So in short you're providing the users with a means to create their own
query with the ability to select the fields to include and the where
statements?
 
I'm inclinded to suggest that you use a table to capture the SQL
statement generated by the user. This would allow you to provide the
user with the ability to save queries for later retrival if the
structure is

txtQueryDescription memQuerySQL

If the SQL statement, will be enormous you could do something whereBy
you do create a new Query object saved in a format that clearly
identifies it as a search query and then save the name to the table as in

txtQueryDescription txtQueryName
Open Invoices qrySRCH_user1
Closed Invoices qrySRCH_user2

From there its just a matter of setting the form/report recordSource as
needed by doing a DLookup()

If you've got a FE/BE configuration (as you should), the table would go
into the FE so that each user can customize their own.

David H
 
Back
Top