Form Controls unavailable to query field expression

[Jay]

Hi All -

This is a follow-up post to Subject: ‘Pass form parameters to query
expression’ dated 1/3/08.

Given:
1. Stand-alone Access 2003 mdb database.
2. Custom dialog form with two textboxes for user input (latitude &
longitude).
3. The btnOK on the form sets Me.Visible = False, but does not close the form.
4. A stored Select query named based on an underlying table.
5. An expression in the stored query that uses fully qualified references to
the latitude and longitude textbox controls on the form. The expression is:
Delta:
Abs(Abs([Latitude])-Abs(Forms!frmParameters!tbxLat))+Abs(Abs([Longitude])-Abs(Forms!frmParameters!tbxLon))


Behavior:
The query runs properly when the form is opened non-modally, latitude and
longitude are entered into the textboxes, and the query is opened manually.
However, when the query is called from VBA, the error "Run-time error:
'3061': Too few parameters" occurs. The VBA statement that produces the
error is:

strQueryName = "qCalculateDeltas"
Set rsq = CurrentDb.OpenRecordset(strQueryName) ‘error occurs on this
statement

Question:
Is it possible to open the query (into a recordset object) through the
OpenRecordset statement and have the query field expression access the fully
qualified form controls?

Thanks,
Jay

 

[Pieter Wijnen]

You can use the Eval Function to achieve your goal

Dim Db As DAO.Database
Dim Qdf As DAO.QueryDef
Dim Prm As DAO.Parameter
Dim rsq As DAO.Recordset
Dim strQueryName As String

Set Db = Access.Application.CurrentDb
strQueryName = "qCalculateDeltas"
Set Qdf = Db.QueryDefs(strQueryName)
For Each Prm in Qdf.Parameters
With Prm
.Value = Access.Eval(.Name)
End With
Next
Set rsq = Qdf.OpenRecordset(DAO.dbOpenSnapshot)

....

HtH

Pieter

 

[Klatuu]

If your query is referencing controls on your form, the form has to be open.
Running it stand alone, the query can't find the referenced objects.

 

[Jay]

Hi Pieter -

Wow. That approach is a thing of beauty and it works perfectly. Thanks to
you and Dave for exposing some of the real DP muscle of Access over my two
related posts. These techniques are very empowering. I must go now and lift
some heavy data!

Many, many thanks again,
Jay

You can use the Eval Function to achieve your goal

Dim Db As DAO.Database
Dim Qdf As DAO.QueryDef
Dim Prm As DAO.Parameter
Dim rsq As DAO.Recordset
Dim strQueryName As String

Set Db = Access.Application.CurrentDb
strQueryName = "qCalculateDeltas"
Set Qdf = Db.QueryDefs(strQueryName)
For Each Prm in Qdf.Parameters
With Prm
.Value = Access.Eval(.Name)
End With
Next
Set rsq = Qdf.OpenRecordset(DAO.dbOpenSnapshot)

....

HtH

Pieter

Hi All -

This is a follow-up post to Subject: 'Pass form parameters to query
expression' dated 1/3/08.

Given:
1. Stand-alone Access 2003 mdb database.
2. Custom dialog form with two textboxes for user input (latitude &
longitude).
3. The btnOK on the form sets Me.Visible = False, but does not close the
form.
4. A stored Select query named based on an underlying table.
5. An expression in the stored query that uses fully qualified references
to
the latitude and longitude textbox controls on the form. The expression
is:
Delta:
Abs(Abs([Latitude])-Abs(Forms!frmParameters!tbxLat))+Abs(Abs([Longitude])-Abs(Forms!frmParameters!tbxLon))


Behavior:
The query runs properly when the form is opened non-modally, latitude and
longitude are entered into the textboxes, and the query is opened
manually.
However, when the query is called from VBA, the error "Run-time error:
'3061': Too few parameters" occurs. The VBA statement that produces the
error is:

strQueryName = "qCalculateDeltas"
Set rsq = CurrentDb.OpenRecordset(strQueryName) 'error occurs on this
statement

Question:
Is it possible to open the query (into a recordset object) through the
OpenRecordset statement and have the query field expression access the
fully
qualified form controls?

Thanks,
Jay

 

[Jay]

Hi Dave -

Many thanks for sticking with my issue over two posts.

Jay

If your query is referencing controls on your form, the form has to be open.
Running it stand alone, the query can't find the referenced objects.
--
Dave Hargis, Microsoft Access MVP

Hi All -

This is a follow-up post to Subject: ‘Pass form parameters to query
expression’ dated 1/3/08.

Given:
1. Stand-alone Access 2003 mdb database.
2. Custom dialog form with two textboxes for user input (latitude &
longitude).
3. The btnOK on the form sets Me.Visible = False, but does not close the form.
4. A stored Select query named based on an underlying table.
5. An expression in the stored query that uses fully qualified references to
the latitude and longitude textbox controls on the form. The expression is:
Delta:
Abs(Abs([Latitude])-Abs(Forms!frmParameters!tbxLat))+Abs(Abs([Longitude])-Abs(Forms!frmParameters!tbxLon))


Behavior:
The query runs properly when the form is opened non-modally, latitude and
longitude are entered into the textboxes, and the query is opened manually.
However, when the query is called from VBA, the error "Run-time error:
'3061': Too few parameters" occurs. The VBA statement that produces the
error is:

strQueryName = "qCalculateDeltas"
Set rsq = CurrentDb.OpenRecordset(strQueryName) ‘error occurs on this
statement

Question:
Is it possible to open the query (into a recordset object) through the
OpenRecordset statement and have the query field expression access the fully
qualified form controls?

Thanks,
Jay