Open recordset on parameterized query

[Guest]

Here's the code:

Dim rsHeader As DAO.Recordset
Set rsHeader = CurrentDb.OpenRecordset("HeaderSelect", dbOpenSnapshot)

Here is a simplified version of the parameter in the HeaderSelect query:

[DateFrom]

I can run the query directly just fine, and it correctly prompts me for
DateFrom. However, when I run VBA to open the recordset, it fails with error
0. When I replace DateFrom with >#5/1/2006# in the query, the recordset opens
fine from VBA.

Ultimately, the parameter will be this:

Between [Forms]![Main]![DateFrom] and [Forms]![Main]![DateTo]

so that the user will select the date range on the form. Again, the query
runs fine alone, but the attempt to open the recordset fails.

 

[Brendan Reynolds]

Here's an example I used for a similar question in another newsgroup ...

Public Sub TestQuery()

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

Set db = CurrentDb
Set qdf = db.QueryDefs("qryTest")
qdf.Parameters("[How Many?]") = 5 '<= assign value to parameter here
Set rst = qdf.OpenRecordset()
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value
Next fld
rst.MoveNext
Loop
rst.Close

End Sub


--
Brendan Reynolds
Access MVP

Here's the code:

Dim rsHeader As DAO.Recordset
Set rsHeader = CurrentDb.OpenRecordset("HeaderSelect", dbOpenSnapshot)

Here is a simplified version of the parameter in the HeaderSelect query:

[DateFrom]

I can run the query directly just fine, and it correctly prompts me for
DateFrom. However, when I run VBA to open the recordset, it fails with
error
0. When I replace DateFrom with >#5/1/2006# in the query, the recordset
opens
fine from VBA.

Ultimately, the parameter will be this:

Between [Forms]![Main]![DateFrom] and [Forms]![Main]![DateTo]

so that the user will select the date range on the form. Again, the query
runs fine alone, but the attempt to open the recordset fails.

 

[Guest]

....however, it works if I use this parameter:

Between GetDateFrom() And GetDateTo()

in conjunction with these two public functions:

Public Function GetDateFrom() As Date
GetDateFrom = [Forms]![MenuMain]![DateFrom]
End Function

Public Function GetDateTo() As Date
GetDateTo = [Forms]![MenuMain]![DateTo]
End Function

Why will the direct reference to the control on the form not work with the
Open Recordset method as it does when running the query directly?

Here's the code:

Dim rsHeader As DAO.Recordset
Set rsHeader = CurrentDb.OpenRecordset("HeaderSelect", dbOpenSnapshot)

Here is a simplified version of the parameter in the HeaderSelect query:

[DateFrom]

I can run the query directly just fine, and it correctly prompts me for
DateFrom. However, when I run VBA to open the recordset, it fails with error
0. When I replace DateFrom with >#5/1/2006# in the query, the recordset opens
fine from VBA.

Ultimately, the parameter will be this:

Between [Forms]![Main]![DateFrom] and [Forms]![Main]![DateTo]

so that the user will select the date range on the form. Again, the query
runs fine alone, but the attempt to open the recordset fails.

 

[Guest]

Thank you very much.

Here's an example I used for a similar question in another newsgroup ...

Public Sub TestQuery()

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

Set db = CurrentDb
Set qdf = db.QueryDefs("qryTest")
qdf.Parameters("[How Many?]") = 5 '<= assign value to parameter here
Set rst = qdf.OpenRecordset()
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value
Next fld
rst.MoveNext
Loop
rst.Close

End Sub


--
Brendan Reynolds
Access MVP

Here's the code:

Dim rsHeader As DAO.Recordset
Set rsHeader = CurrentDb.OpenRecordset("HeaderSelect", dbOpenSnapshot)

Here is a simplified version of the parameter in the HeaderSelect query:

[DateFrom]

I can run the query directly just fine, and it correctly prompts me for
DateFrom. However, when I run VBA to open the recordset, it fails with
error
0. When I replace DateFrom with >#5/1/2006# in the query, the recordset
opens
fine from VBA.

Ultimately, the parameter will be this:

Between [Forms]![Main]![DateFrom] and [Forms]![Main]![DateTo]

so that the user will select the date range on the form. Again, the query
runs fine alone, but the attempt to open the recordset fails.

 

[Guest]

Use the form and control names where you will enter the dates in the Criteria
row in the query builder:
For the From Date field:

= [Forms]![Main]![DateFrom]

For the To Date field:
<= [Forms]![Main]![DateTo]

Here's the code:

Dim rsHeader As DAO.Recordset
Set rsHeader = CurrentDb.OpenRecordset("HeaderSelect", dbOpenSnapshot)

Here is a simplified version of the parameter in the HeaderSelect query:

[DateFrom]

I can run the query directly just fine, and it correctly prompts me for
DateFrom. However, when I run VBA to open the recordset, it fails with error
0. When I replace DateFrom with >#5/1/2006# in the query, the recordset opens
fine from VBA.

Ultimately, the parameter will be this:

Between [Forms]![Main]![DateFrom] and [Forms]![Main]![DateTo]

so that the user will select the date range on the form. Again, the query
runs fine alone, but the attempt to open the recordset fails.

 

[Guest]

That's what I originally had. As a simple test, I had just this:

= [Forms]![Main]![DateFrom]

It worked when running the query, but failed when using the query as the
source for the Open Recorset method. I fixed it by assigning the parameters
in VBA (qdf.parameter etc) per Brendan's suggestion.

I'm still curious about why the reference to the form or user-input
parameter is invalid when opening the recordset but not when running the
query alone, but I guess I will just have to live with it working right!

Use the form and control names where you will enter the dates in the Criteria
row in the query builder:
For the From Date field:

= [Forms]![Main]![DateFrom]

For the To Date field:
<= [Forms]![Main]![DateTo]

Here's the code:

Dim rsHeader As DAO.Recordset
Set rsHeader = CurrentDb.OpenRecordset("HeaderSelect", dbOpenSnapshot)

Here is a simplified version of the parameter in the HeaderSelect query:

[DateFrom]

I can run the query directly just fine, and it correctly prompts me for
DateFrom. However, when I run VBA to open the recordset, it fails with error
0. When I replace DateFrom with >#5/1/2006# in the query, the recordset opens
fine from VBA.

Ultimately, the parameter will be this:

Between [Forms]![Main]![DateFrom] and [Forms]![Main]![DateTo]

so that the user will select the date range on the form. Again, the query
runs fine alone, but the attempt to open the recordset fails.

 

[Guest]

Surprises me. I use that technique regularly. Brendan's technique is
probably better. I have used that before.

That's what I originally had. As a simple test, I had just this:

= [Forms]![Main]![DateFrom]

It worked when running the query, but failed when using the query as the
source for the Open Recorset method. I fixed it by assigning the parameters
in VBA (qdf.parameter etc) per Brendan's suggestion.

I'm still curious about why the reference to the form or user-input
parameter is invalid when opening the recordset but not when running the
query alone, but I guess I will just have to live with it working right!

Use the form and control names where you will enter the dates in the Criteria
row in the query builder:
For the From Date field:

= [Forms]![Main]![DateFrom]

For the To Date field:
<= [Forms]![Main]![DateTo]

Here's the code:

Dim rsHeader As DAO.Recordset
Set rsHeader = CurrentDb.OpenRecordset("HeaderSelect", dbOpenSnapshot)

Here is a simplified version of the parameter in the HeaderSelect query:

[DateFrom]

I can run the query directly just fine, and it correctly prompts me for
DateFrom. However, when I run VBA to open the recordset, it fails with error
0. When I replace DateFrom with >#5/1/2006# in the query, the recordset opens
fine from VBA.

Ultimately, the parameter will be this:

Between [Forms]![Main]![DateFrom] and [Forms]![Main]![DateTo]

so that the user will select the date range on the form. Again, the query
runs fine alone, but the attempt to open the recordset fails.

 

[Tim Ferguson]

I'm still curious about why the reference to the form or user-input
parameter is invalid when opening the recordset but not when running
the query alone, but I guess I will just have to live with it working
right!

It depends on how you get your SQL executed. Using the Access user
interface will involve the built-in expression evaluator which knows
about forms and controls and stuff. This usually involves the DoCmd
object, like

DoCmd.OpenQueryDef "MyQueryDef", etc
DoCmd.RunSQL jetMyCommand

On the other hand, passing commands straight to the jet engine is
usually faster and more flexible, but you lose all contact with the UI
so you have to inject all literal values by hand, either injecting them
into the command text or as parameters. Vis

set qdf = QueryDefs("MyQueryDef")
qdf.Parameters("DestinationStation")="LGW"
qdf.Execute dbFailOnError

jetSQL = "SELECT * FROM MyTable " & vbNewLine & _
"WHERE MyLimit > " & txtLimit.Value & ";"
Set rs = db.OpenRecordSet(jetSQL, dbOpenSnapshot, etc)

Hope that helps


Tim F