Full Version: DoCmd properties
UtterAccess Forums > Microsoft® Access > Access Forms
sike11
I would like to run a query from VBA. DoCmd.OpenQuery DISPLAYS the query, which I don't want. I would like to run the query in the background, capture the results, and loop through to test for whatever.
Can anyone help?
argeedblu
In order to do this you will have to create a query string in your vba code, then use this string to open a record set (either DAO or ADO). When you have the recordset open, you can then loop through it. Incidentally, you can get a start on writing SQL by creating your query in the query designer and then switching the view to SQL. For non-parameter queries you can then copy the SQL and use it to form you VBA assignment statement.
Glenn
MrSiezen
>
Why does he have to create a query string? Can't he just open the stored query he already has in a recordset? Not commenting, just wondering...
sike11
I have already written a select query. I am just wondering if it is possible to do this.
That I have are 2 text boxes with a command button. I would like the user to input part or all of the required text and the results returned to the text boxes for use. At the moment it is not doing that.
This is the command in the Find button:
Private Sub cmdFindStudent_Click()
On Error GoTo Err_cmdFindStudent_Click
Dim stDocName As String
stDocName = "qryQOE-ManualUpdate"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_cmdFindStudent_Click:
Exit Sub
Err_cmdFindStudent_Click:
MsgBox Err.Description
Resume Exit_cmdFindStudent_Click

End Sub
Is there any way to do this?
argeedblu
You are right. He could use the stored query to open the recordset. I prefer to use the equivalent SQL string because my code will not be susceptible to design changes in the query and I can see the query directly in the source code. That means I don't have to open the stored query just to see what it is doing.
dmittedly, creating sql strings can be somewhat daunting at the beginning and definitely more work than simple using a stored query. However the effort in learning to do so definitely pays of in increased flexibilty. There are also some queries that must be written in SQL (UNIONs for example). I have found that learning how to construct simpler sql strings and using them in favour of stored queries when I am writing VBA to be definitely worthwhile.
Glenn
dashiellx2000
Although I have poked stored queries to get thier data, etc..., I have to agree with Glenn. Once you have learned SQL (Which is much easier then it looks) your database are much more powerful.
Is far as the OP's original question, have the normalization issues in your db been fixed? What you are attempting works best with a normalized data structure.
sike11
Hi,
Can you give me an example of how to do this?
argeedblu
That sounds somewhat differerent from you original question. If you are merely trying to see if a value enterd by the user exists in the data, you can use a parameter query that refers to the relevant form and text box for its criteriea. However if you run that query from you vba code, the query results will still be displayed.

If, on the other hand, you can use the query as the record source for a form or report. Opening the form or report with DoCmd will only display the results.

Glenn
sike11
Thank you for your prompt response.
How would you use the query as the record source for the form?
dashiellx2000 - In response to your question re: normalisation, yes.
argeedblu
Open the form in design view. Use view/properties to view the form's property sheet. Go to the data tab. Use the Record Source property to select the relevant query. If you are modifying a table based form, you will have to make sure that your query includes all the fields you refer to on the form.
Glenn
sike11
Thank you.
Trying it now.
MrSiezen
I completely agree with you Argee (wow, this is just asking for a typo!). But since it seems like he's somewhat a beginner in Access, it would be wise to mention that this is a more advanced way of doing it, otherwise it might confuse people. Just my two cents of course.
started by using stored queries, but I'm now using string queries more and more, mostly because of the possibilites added when working like that.
argeedblu
Thanks for your comment. I generally try to gear my responses to the comfort level that the poster has indicated by the context of his or her post. In this case, I assumed that the poster's level of experience was reasonably advanced given the question about writing queries in VBA. I'm not offering that as an excuse bur merely my rationale for offering the initial response that I did.
Glenn
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.