Query Parameters

[CK]

I Have the following general purpose parameterized query

PARAMETERS FIELD_NAME Text ( 255 );
SELECT CodeLists.CODE
FROM CodeLists
WHERE (((CodeLists.CODELIST)=[FIELD_NAME]));

that returns CODES associated with a CODELIST.

How can I pass at run-time (let's say in the Row Source of a field lookup)
the parameter name so that it does not request the parameter value?

I tried the syntax

qryCPL_GET_CODE_LIST("NOMINAL_WALL_THICKNESS_GCL")

and it did not like it.

Is what I need even possible or will I have to hard code the query code with
its corresponding filter value in each Row Source attribute?

Any help would be greatly appreciated.

 

[AC]

I Have the following general purpose parameterized query

PARAMETERS FIELD_NAME Text ( 255 );
SELECT CodeLists.CODE
FROM CodeLists
WHERE (((CodeLists.CODELIST)=[FIELD_NAME]));

that returns CODES associated with a CODELIST.

How can I pass at run-time (let's say in the Row Source of a field lookup)
the parameter name so that it does not request the parameter value?

I tried the syntax

qryCPL_GET_CODE_LIST("NOMINAL_WALL_THICKNESS_GCL")

and it did not like it.

Is what I need even possible or will I have to hard code the query code with
its corresponding filter value in each Row Source attribute?

Any help would be greatly appreciated.

Are you wanting to execute a parameter query by code? If so see
below, if not could you perhaps rephrase to make it clearer as to what
you are after.


To run a parameter query in code you access the parameters collection
of the query definition. You then insert the value you want into the
parameter, and open the query.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef

'Open your query definition
set db = currentDb
set qdf = db.QueryDefs("qryCPL_GET_CODE_LIST")

'Set a value for your parameter
qdf.Parameters("[FIELD_NAME]") = "NOMINAL_WALL_THICKNESS_GCL"

'Open a recordset on your query and do things
set rst = qdf.OpenRecordset()
' do things
'Or perhaps open your query in a window, or whatever

set rst = nothing
set qdf = nothing
set db = nothing





It is also possible to pass in the value of a control directly into
the query, rather than using a parameter query.
for example:

SELECT CodeLists.CODE
FROM CodeLists
WHERE (((CodeLists.CODELIST) = Forms![f_MyForm]![MyControlName] );


HTH, if way off base just post back with a few more details

AndyC

 

[CK]

I Have the following general purpose parameterized query

PARAMETERS FIELD_NAME Text ( 255 );
SELECT CodeLists.CODE
FROM CodeLists
WHERE (((CodeLists.CODELIST)=[FIELD_NAME]));

that returns CODES associated with a CODELIST.

How can I pass at run-time (let's say in the Row Source of a field lookup)
the parameter name so that it does not request the parameter value?

I tried the syntax

qryCPL_GET_CODE_LIST("NOMINAL_WALL_THICKNESS_GCL")

and it did not like it.

Is what I need even possible or will I have to hard code the query code with
its corresponding filter value in each Row Source attribute?

Any help would be greatly appreciated.

Are you wanting to execute a parameter query by code? If so see
below, if not could you perhaps rephrase to make it clearer as to what
you are after.


To run a parameter query in code you access the parameters collection
of the query definition. You then insert the value you want into the
parameter, and open the query.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef

'Open your query definition
set db = currentDb
set qdf = db.QueryDefs("qryCPL_GET_CODE_LIST")

'Set a value for your parameter
qdf.Parameters("[FIELD_NAME]") = "NOMINAL_WALL_THICKNESS_GCL"

'Open a recordset on your query and do things
set rst = qdf.OpenRecordset()
' do things
'Or perhaps open your query in a window, or whatever

set rst = nothing
set qdf = nothing
set db = nothing





It is also possible to pass in the value of a control directly into
the query, rather than using a parameter query.
for example:

SELECT CodeLists.CODE
FROM CodeLists
WHERE (((CodeLists.CODELIST) = Forms![f_MyForm]![MyControlName] );


HTH, if way off base just post back with a few more details

AndyC

Andy,
Thank you for the reply.
For starters, I do not want to write VBA code
In the table Dictionary (for several fields on several tables) I want to
provide a lookup by displaying a list box of pertinent values. All values are
grouped for all fields in a single lookup table.
The Row Source property of the Lookup tab provides me that capability.
The problem is how do I filter only the values pertinent to a field, by that
field's name.
That is where the parameterized query comes into place.
The only problem is that when I call the query I do not see any obvious way
to pass the parameter I want, so it does not pop up the dialog to ask for the
field name (parameter) every time I use it.
The same would apply if I were to use the query in the validation rule
attribute of the field.
Therefore, the question is
If I use a parameterized query in the field properties, is there any way to
pass a parameter value along with it?
Thanks