parameter query opening as a recordset

[EliteDesk@aol.com]

I need to open a recordset with a query, but when I used the code below I received "Too Few Parameters. Expected 1" Error Message.

rsReports.Open "Select DISTINCT InspectorID from qryCreatePDFs", conn,
adOpenStatic, adLockReadOnly

due to the following site:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

I tried the following...

Dim rsReports As Recordset '<- DAO recordset
Dim qdfMyQuery As QueryDef

Set qdfMyQuery = CurrentDb.QueryDefs("qryCreatePdfs")
qdfMyQuery![Forms!frmCreatePDFs!InspectionDate] = Forms![frmCreatePDFs]![InspectionDate]

Set rsReports = CurrentDb.OpenRecordset("Select DISTINCT InspectorID from qdfMyQuery", dbOpenSnapshot)

BUT getting the following error "Cannot find the input table or query" at Set rsReports =


Any advise would be greatly appreciated. Thanks in advance for your time.


Cathi

 

[SforSoftware]

Do you have a reference (VBA->Tools->References) to DAO? And is it above the reference for ADO ?

Else declare your recordset as DAO.Recordset

 

[EliteDesk@aol.com]

Bert
I have Microsoft DAO 3.6 Object Library checked via References. Do I still need to declare DAO?

Thanks,
Cathi

 

[SforSoftware]

Hi Cathi,

In my opinion it's always better to declare in that way, so you should use:
..As DAO.Recordset
..As DAO.QueryDef

But now I had a better look at your code and found the error by opening the recordset. Try this code:

Dim rsReports As DAO.Recordset '<- DAO recordset
Dim qdfMyQuery As DAO.QueryDef

Set qdfMyQuery = CurrentDb.QueryDefs("qryCreatePdfs")
qdfMyQuery![Forms!frmCreatePDFs!InspectionDate] = Forms![frmCreatePDFs]![InspectionDate]

Set rsReports = qdfMyQuery.OpenRecordset()

Have also a look at step 4 on the Microsoft-page that you referred to, there's a complete example

 

[Mile-O]

If you don't explicitly declare DAO and have ADO still checked as a reference then it is down to the order (which one's above the other) that determines whether it'll fall apart or not.

The RecordSet object, for example, is both a member of DAO and of ADO although they are different (to a slight extent) objects.

Have a look at this.

 

[Jon K]

"Select DISTINCT InspectorID from qryCreatePDFs"

Since qryCreatePDFs is a parameter query, to obtain a DISTINCT InspectorID list in a recordset from qryCreatePDFs, you need to first create a QueryDef with the SQL string, and then pass the parameter value to it from the form (The form must be open when the code is run):-

   Dim qdfMyQuery As DAO.QueryDef
   Dim rsReports As DAO.Recordset
   Dim SQL As String
   
   SQL = "Select DISTINCT InspectorID from qryCreatePDFs"
   
   Set qdfMyQuery = CurrentDb.[b]CreateQueryDef[/b]("", SQL)
  
   qdfMyQuery![Forms!frmCreatePDFs!InspectionDate] = _
               Forms![frmCreatePDFs]![InspectionDate]
   
   Set rsReports = qdfMyQuery.OpenRecordset(dbOpenSnapshot)
   
   
   ' loop through the recordset.
   Do While Not rsReports.EOF
     MsgBox rsReports![InspectorID]
     rsReports.MoveNext
   Loop

 

[EliteDesk@aol.com]

Thank You

JON K it works like a charm, thanks for taking the time to help. Sorry it took a while to get back, but I went on a much needed vacation. Thank You, to all of you brilliant people who reply the endless questions. Access World Forums is priceless. Cathi