RecordsetClone equivalent for reports???

[Bill]

Oh-Oh!!! First time I've ever had an occasion to
want the use of the RecordsetClone in a report
and discover that method isn't valid for reports.

Yikes! In the OnOpen code, I need to search
for a specific ID in the report's underlying
RecordSource to extract some information
that is constant on each page of the report.

So, how do I get around this one?

Thanks,
Bill

 

[Duane Hookom]

Can you create a recordset based on the Record Source property of the
report? You might need to add the Filter property if the filter is on.

 

[Bill]

You lost me on that one Duane. Can you give me a quick
code example and what the syntax would be to access
a field?

What I had tried when I encountered the RecordsetClone
method error is: (Just to be clear about what I'm trying to
accomplish)

Me.RecordsetClone.FindFirst "[RegistryID] = " & lngSendID

With Me.RecordsetClone
strFname = ![FIRSTNAME]
strLname = ![LASTNAME]
strAdr = ![ADDRESS]
strCS = ![CITYSTATE]
strZip = ![ZIP]
strHomeNo = ![HOMEPHONE]
strWorkNo = ![WORKPHONE]
strCellNo = ![CELLPHONE]
End With

 

[Allen Browne]

Duane suggested that you could OpenRecordset() on whatever is the
RecordSource of the report, e.g.:
Dim rs As DAO.Recordset
Set rs = Currentdb.OpenRecordset(Me.RecordSource)
You now have a recordset that has the same records as the report, so you can
now FindFirst the one you want, and get at that information.

Your reply indicates that you want one specific record rather than the whole
set. It would be better to just get that one record:
Dim strSql As String
strSql = "SELECT * FROM MyTable WHERE RegistryID = " & lngSendID & ";"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
With rs
If .RecordCount > 0 Then
strFname = Nz(!FirstName, vbNullString)
strLname = Nz(!LastName, vbNullString)
'etc
End If
End With
rs.Close
Set rs = Nothing
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

You lost me on that one Duane. Can you give me a quick
code example and what the syntax would be to access
a field?

What I had tried when I encountered the RecordsetClone
method error is: (Just to be clear about what I'm trying to
accomplish)

Me.RecordsetClone.FindFirst "[RegistryID] = " & lngSendID

With Me.RecordsetClone
strFname = ![FIRSTNAME]
strLname = ![LASTNAME]
strAdr = ![ADDRESS]
strCS = ![CITYSTATE]
strZip = ![ZIP]
strHomeNo = ![HOMEPHONE]
strWorkNo = ![WORKPHONE]
strCellNo = ![CELLPHONE]
End With

Can you create a recordset based on the Record Source property of the
report? You might need to add the Filter property if the filter is on.

 

[Bill]

Hi Allen,
I stumbled a bit because I had the wrong query
field in the WHERE clause. Once Doug Steele
commented about data type considerations I
discovered the error and all went smoothly from
there. (See my post "Error 3061" from earlier
today, Wednesday.)

I do have a question about dbEngine. What is
the "array type" notation (0)(0) all about? I only
have limited knowledge about dbEngine, but it
was my understanding that it wasn't a part of
any collection and would therefore not be
referenced as such?

Bill

Duane suggested that you could OpenRecordset() on whatever is the
RecordSource of the report, e.g.:
Dim rs As DAO.Recordset
Set rs = Currentdb.OpenRecordset(Me.RecordSource)
You now have a recordset that has the same records as the report, so you
can now FindFirst the one you want, and get at that information.

Your reply indicates that you want one specific record rather than the
whole set. It would be better to just get that one record:
Dim strSql As String
strSql = "SELECT * FROM MyTable WHERE RegistryID = " & lngSendID & ";"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
With rs
If .RecordCount > 0 Then
strFname = Nz(!FirstName, vbNullString)
strLname = Nz(!LastName, vbNullString)
'etc
End If
End With
rs.Close
Set rs = Nothing
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

You lost me on that one Duane. Can you give me a quick
code example and what the syntax would be to access
a field?

What I had tried when I encountered the RecordsetClone
method error is: (Just to be clear about what I'm trying to
accomplish)

Me.RecordsetClone.FindFirst "[RegistryID] = " & lngSendID

With Me.RecordsetClone
strFname = ![FIRSTNAME]
strLname = ![LASTNAME]
strAdr = ![ADDRESS]
strCS = ![CITYSTATE]
strZip = ![ZIP]
strHomeNo = ![HOMEPHONE]
strWorkNo = ![WORKPHONE]
strCellNo = ![CELLPHONE]
End With

Can you create a recordset based on the Record Source property of the
report? You might need to add the Filter property if the filter is on.

--
Duane Hookom
MS Access MVP

Oh-Oh!!! First time I've ever had an occasion to
want the use of the RecordsetClone in a report
and discover that method isn't valid for reports.

Yikes! In the OnOpen code, I need to search
for a specific ID in the report's underlying
RecordSource to extract some information
that is constant on each page of the report.

 

[Allen Browne]

dbEngine is the top object in the DAO object model.
Refer to this diagram:
http://allenbrowne.com/ser-04.html

Workspaces is the default collection under dbEngine, so you refer to the
first workspace as:
dbEngine.Workspaces(0)
But since Workspaces is the default collection, you can get away with
dbEngine(0) as a shortcut.

Databases is the default collection for a Workspace object, so the full
reference to the first database is:
dbEngine.Workspaces(0).Databases(0)
Again, you can shortcut that to:
dbEngine.Workspaces(0)(0)
or just
dbEngine(0)(0)

Most objects in Access have default collections or properties.
For forms, the Controls collection is the default.
So you can refer to the City text box on Form1 as:
Forms!Form1.Controls("City")
or shortcut it as:
Forms!Form1("City")