Passing criteria to query

[Erin]

Hi all,

I would like to run a series of stored queries from a
macro. These queries require parameters to be entered by
the user. I need to request these values from the user
for other purposes as well, so I would like to set them up
as variables. How do I go about passing the variables to
the query to satisfy the parameter requirements? Any help
would be appreciated. Thanks.

Erin

 

[Ken Snell]

Use a form and have the user enter the values on the form; then have the
query read the values from the form instead of asking the user to input
them. There's no place in a macro to store a variable.

 

[Erin]

I guess I'm not really talking about a macro. I should
have used the term VBA procedure. (I'm primarily an Excel
programmer and haven't quite figured out the role
of "macros" vs. VBA coding in Access). Is it possible
from a VBA procedure?

Erin

 

[Ken Snell]

Unfortunately, the term "macro" does not mean the same thing in ACCESS as it
does in EXCEL, WORD, etc. A macro in ACCESS is not VBA. But ACCESS can run
VBA similarly to EXCEL, etc. You put the code in modules, just as you do in
EXCEL.

To do what you seek via VBA in ACCESS, you need to use the QueryDef
collection of DAO and then resolve the parameters. Here is some sample code
for how to do this:


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim r As DAO.QueryDef
VariableName = InputBox("Enter the value:", "Value Title")
Set dbs = CurrentDb
Set r = dbs.QueryDefs("NameOfQuery")
r.Parameters(0) = VariableName
' repeat above step (changing index value) for each parameter
Set rst = r.OpenRecordset(dbOpenDynaset)
' Or you can use the r.Execute action instead
' of the OpenRecordset step
' .... more code
' .
' .
rst.Close
r.Close
Set r = Nothing
Set rst = Nothing
dbs.Close
Set dbs = Nothing

 

[Erin]

Ken,

Is this possible with an Append Query? I'm
getting "Invalid Operation" at 'Set rst = r.OpenRecordset
(dbOpenDynaset)'. (See my code below.) I tried r.Execute
but apparently I didn't know the proper syntax. I also
just tried running a Select Query but the RecordCount
indicated 1 record rather than the 31 I expected. I don't
know if it's not working or if I'm just using RecordCount
incorrectly.

I thought about using a form as you suggested earlier, but
I'm having trouble bringing up the form so that it has the
focus, much less getting the remainder of the code to
run. I'd appreciate any help. Clearly I'm in over my
head, but this is the last piece of the puzzle. I would
like to see it through.

Do you have any suggestions for a good book on this type
of thing. I'm currently using 'Running Microsoft Access
2000' from Microsoft Press, but clearly it isn't working
out for me. Thanks.

DoCmd.SetWarnings False
Dim RefYear As Single
Dim NewYear As Single
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim r As DAO.QueryDef
Set dbs = CurrentDb
Set r = dbs.QueryDefs("NEW YEAR APPEND")
RefYear = InputBox("Please enter the reference
year:", "REFERENCE YEAR")
NewYear = InputBox("Please enter the new
year:", "REFERENCE YEAR")
r.Parameters(0) = NewYear
r.Parameters(1) = RefYear
Set rst = r.OpenRecordset(dbOpenDynaset)
rst.Close
r.Close
Set r = Nothing
Set rst = Nothing
dbs.Close
Set dbs = Nothing
DoCmd.SetWarnings True

 

[Ken Snell]

Comments inline...

--
Ken Snell
<MS ACCESS MVP>

Ken,

Is this possible with an Append Query? I'm
getting "Invalid Operation" at 'Set rst = r.OpenRecordset
(dbOpenDynaset)'. (See my code below.)

For an action query, you must use the .Execute method. The .OpenRecordset
method won't work for an append query. The syntax is this:

r.Execute

I tried r.Execute
but apparently I didn't know the proper syntax. I also
just tried running a Select Query but the RecordCount
indicated 1 record rather than the 31 I expected. I don't
know if it's not working or if I'm just using RecordCount
incorrectly.

The RecordCount value of a recordset will either be 0 or 1 (depending upon
whether there are zero records or more than zero records, respectively) in
the recordset. In order to get an accurate count, you must move the
recordset to the last record and then get the RecordCount. Be sure that you
then move back to the first record if you wish to loop through the
recordset.

Set rst = r.OpenRecordset(dbOpenDynaset)
If rst.BOF = False And rst.EOF = False Then
rst.MoveLast
varRecordCount = rst.RecordCount
rst.MoveFirst
' ... more code here
' ...
End If

I thought about using a form as you suggested earlier, but
I'm having trouble bringing up the form so that it has the
focus, much less getting the remainder of the code to
run. I'd appreciate any help.

If you want to show the form as part of your code, the best way is to open
the form in dialog mode (this causes your code to pause). The form needs to
have a command button on it for the user to click when the values have been
entered. There must be code in the OnClick event of that button that makes
the form invisible so that your initial code continues to run and you then
can get the values from the form and then close it:

Add these code steps to your code where you want the form to be displayed:
DoCmd.OpenForm "PopupFormName", , , , , acDialog
r.Parameters(0) = Forms("PopupFormName").NewYearControl.Value
r.Parameters(1) = Forms("PopupFormName").RefYearControl.Value
DoCmd.Close acForm, "PopupFormName"

In the "PopupFormName" form, put this code on the OnClick event of the
button:

Private Sub cmdButtonName_Click()
Me.Visible = False
End Sub

Clearly I'm in over my
head, but this is the last piece of the puzzle. I would
like to see it through.

Do you have any suggestions for a good book on this type
of thing. I'm currently using 'Running Microsoft Access
2000' from Microsoft Press, but clearly it isn't working
out for me. Thanks.

There are many good books....but different books work better for different
people. I've use ACCESS (version number) Bible, Beginning ACCESS (version
number) VBA, ACCESS 202 VBA Handbook, and others. The definitive book is the
ACCESS (version number) Developer's Handbook (big and expensive, but well
worth the price).