Query won't open as Recordset (1 Viewer)

rich.barry

Registered User.
Local time
Today, 23:17
Joined
Aug 19, 2001
Messages
176
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

Registered User.
Local time
Tomorrow, 06:17
Joined
Feb 22, 2001
Messages
794
Do :
dbengine(0)(0).OpenRecordset ("YourQueryName", dbOpenSnapshot,,dbOptimistic)

Give the same error?
 

rich.barry

Registered User.
Local time
Today, 23:17
Joined
Aug 19, 2001
Messages
176
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
 

Attachments

  • db2.zip
    25.9 KB · Views: 115

Alexandre

Registered User.
Local time
Tomorrow, 06:17
Joined
Feb 22, 2001
Messages
794
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]));

Code:
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.

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.
 
Last edited:

rich.barry

Registered User.
Local time
Today, 23:17
Joined
Aug 19, 2001
Messages
176
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

Registered User.
Local time
Tomorrow, 00:17
Joined
Mar 27, 2003
Messages
125
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
 

Users who are viewing this thread

Top Bottom