Pass form parameters to query expression

[Jay]

Hi All -

Given:
1. Stand-alone Access 2003 mdb database.
2. Custom dialog form with two textboxes for user input (latitude &
longitude).
3. btnOK on the form sets Me.Visible = False, but does not close the form.
4. A stored Select query based on an underlying table.
5. A controlling VBA procedure that opens the form, waits until btnOK is
pressed, then continues executing.

Question: Can the form variables ‘latitude’ and ‘longitude’ be passed from
the open form (or from the controlling VBA procedure) to an expression in a
query field of the stored query without using an SQL statement? The query
field expression in the query design grid is:

Delta: Abs(Abs([StartLat])-Abs(latitude))+Abs(Abs([StartLon])-Abs(longitude))
(‘StartLat’ and ‘StartLon’ come from the query’s underlying table)

Avoiding the use of an SQL statement is desirable in this case because
application evolution will require building numerous queries and query
combinations; it would be easier to manage the evolution if variables could
be fed into expressions in stored queries if possible.

TIA,
Jay

 

[Klatuu]

What do you mean by form variables?
variables can be defined in the form's module, but can't be passed to stored
queries.

If you mean controls on your form, then yes, they can be passed to a stored
query.

To use a form control in a query, you have to fully qualify its reference as
in this example:

WHERE [SomeField] = Forms!MyFormName!MyControlName

 

[Jay]

Hi Dave -

You're correct, I meant controls.

Your suggestion to fully qualify the control's reference does make the query
run properly when it is run manually. Thanks for the solution.

I have a related question, but I'll repost.

Thanks again,
Jay

What do you mean by form variables?
variables can be defined in the form's module, but can't be passed to stored
queries.

If you mean controls on your form, then yes, they can be passed to a stored
query.

To use a form control in a query, you have to fully qualify its reference as
in this example:

WHERE [SomeField] = Forms!MyFormName!MyControlName
--
Dave Hargis, Microsoft Access MVP

Hi All -

Given:
1. Stand-alone Access 2003 mdb database.
2. Custom dialog form with two textboxes for user input (latitude &
longitude).
3. btnOK on the form sets Me.Visible = False, but does not close the form.
4. A stored Select query based on an underlying table.
5. A controlling VBA procedure that opens the form, waits until btnOK is
pressed, then continues executing.

Question: Can the form variables ‘latitude’ and ‘longitude’ be passed from
the open form (or from the controlling VBA procedure) to an expression in a
query field of the stored query without using an SQL statement? The query
field expression in the query design grid is:

Delta: Abs(Abs([StartLat])-Abs(latitude))+Abs(Abs([StartLon])-Abs(longitude))
(‘StartLat’ and ‘StartLon’ come from the query’s underlying table)

Avoiding the use of an SQL statement is desirable in this case because
application evolution will require building numerous queries and query
combinations; it would be easier to manage the evolution if variables could
be fed into expressions in stored queries if possible.

TIA,
Jay