Pass parameter to query from code

[Kevin199]

I have a query that I have been executing via the line "DoCmd.OpenQuery
myQuery" in the code for a form. I've have modified the query so that it
takes a parameter but I can't figure out to how to pass the parameter to the
query. The parameter is a string calculated within the form's code.

 

[Jinjer]

Hi, Kevin.

The way I do it is to create a global variable ('giValue' in the sample
below) and a function ('fiValue' in the sample) in a Code module, set the
variable to your parameter where appropriate in your code, then put the
function, fiValue(), in the field's criteria in the query.

Function Sample:

Global giValue as Integer

Function fiValue() as integer
fiValue = giValue 'the function equals the variable
End Function

 

[Kevin199]

Jinjer,
Thank you for your responce. I am not familiar with functions or global
variables, this code in in a private sub. Would I need to define these in a
public, some how? Anyway, I have tried the following but no luck. I still
get a prompt from my query.


Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryCheckString")
qdf.Parameters("str1") = str
DoCmd.OpenQuery "qryCheckString", acNormal, acEdit

 

[Jinjer]

Kevin,

Go to the "Modules" tab in Access; create a new module.

Create a global variable - Instead of Dim, type Global - (the value of a
global variable can be set anywhere in the application). Create a function
in the same module (the function can be called from anywhere in the
application).

In the new module:

Option Compare Database

Global gStr As String 'global variable

Function fstr() As String 'function name
fstr = gstr 'function will equal the variable
End Function

Save the module. I usually just name my "Code" or "modCode".

In your form code, set the global variable to your parameter in whatever
action in the form gets the data, such as an On Click event.

gStr = Me.yourformfielddata

Open your query, and in field that needs the parameter, type in the function
name in the "Criteria" property

fstr()

When the query runs, it will automatically call the function. As long as
the value of the variable has been set in the form first, the function will
equal that variable and it should work.

Functions return a value. In this case, the function is set to the value of
the variable.

I hope this helps.

 

[Kevin199]

Jinjer,
Thank you. It works great and was very simple.

 

[John W. Vinson]

I have a query that I have been executing via the line "DoCmd.OpenQuery
myQuery" in the code for a form. I've have modified the query so that it
takes a parameter but I can't figure out to how to pass the parameter to the
query. The parameter is a string calculated within the form's code.

It's very rarely necessary or appropriate to open a query Datasheet and
present it to a user.

Instead, use the query as the Recordsource for a Form (for onscreen display)
or Report (for printing), and use code to either open or requery the Form, or
to open (in preview or Normal view) the Report.

Normally the parameter would itself be a reference to a form control, e.g.

=[Forms]![NameOfForm]![NameOfControl]