Query won't open as Recordset (1 Viewer)

[rich.barry]

I have two queries and a form

Query 1 uses a To and From date as criteria that it gets from text boxes on the form, and sums the information between the 2 dates.

Query 2 turns the Query 1 sums into percentages.

Everything works fine when opening Query 1 & 2 from the database window, but when I try to open the query as a recordset, I get and error that the parameters are missing.

RecordAll.Open "SELECT * FROM qryUPT_All_Percent", db, adOpenKeyset, adLockOptimistic

The form is still open, so why does the query spit the dumy at this point?

Thanks

Richard

 

[Alexandre]

Do :
dbengine(0)(0).OpenRecordset ("YourQueryName", dbOpenSnapshot,,dbOptimistic)

Give the same error?

 

[rich.barry]

Alex

Thanks for your suggestion, but I'm not making much headway.

Dim db As Database
Set db = CurrentDb

Dim RecordAll As Recordset
Set RecordAll = DBEngine(0)(0).OpenRecordset("query1", dbOpenSnapshot, , dbOptimistic)

The code you suggested gives me a "Runtime 3001 Invalid Argument" Error

QueryDefs may be the way to go here, but despite looking at other problems/examples, it doesn't seem to work either.

If you're prepared to have a quick look, a simplified database is attached.

Thanks

Richard

 

[Alexandre]

Hi, Rich

You have at least two options.
The first uses parameters with a saved querydef. In this case you have to explicitely declare the parameters in your code.

Query SQL:
PARAMETERS dtDateBegin DateTime;
SELECT [Table1].[TestDate], [Table1].[TestNumber]
FROM Table1
WHERE ((([Table1].[TestDate])<[dtDateBegin]));

Dim db As Database
Dim qrd As QueryDef
Dim rst As Recordset

Set db = DBEngine(0)(0)
Set qrd = db.QueryDefs("Query1")
qrd.Parameters("dtDateBegin") = Me.Controls("YourControlName") 
Set rst = qrd.OpenRecordset(dbOpenDynaset, _
                            dbSeeChanges, _
                            dbOptimistic)

'What you want to do here

Set rst = Nothing
Set qrd = Nothing
Set db = Nothing

The second doesn't use parameters but you have to create a querydef from an SQL string in your code.

Dim strSQL As String
Dim rst As Recordset

srtSQL = "SELECT [Table1].[TestDate], " & _
                "[Table1].[TestNumber] " & _
         "FROM Table1 " & _
         "WHERE ((([Table1].[TestDate])" & _
                "< #" & Me.Controls("YourControlName") & "#));"

Set rst = DBEngine(0)(0).OpenRecordset(strSQL, _
                                       dbOpenDynaset, _
                                       dbSeeChanges, _
                                       dbOptimistic)

'What you want to do here

Set rst = Nothing

To me, the first option is the best under various aspects.
Hope this helps.

 

[rich.barry]

I think I've got there at last, having discovered that:

You have to use =EVal("controlname") as your criteria.

The priority of your references is critical to stop getting type mismatch errors when opening the recordset.

It's been a big drama for a little task!

Thanks for the assistance.

 

[hair]

hi Rich
can you explain me please a little more precisely what you did cause I have the same problem and I didn't get the idea

Thank you