what is the name of access recordset?

[munger]

I have a table called "members" and a form based on it called "members". I wanted to write code with dot notation something like this "members.recordset.count". Access doesn't recognize this so I conclude that access is using a different name for the database and recordset. If I knew the answer to this question I could find all all sorts of great things about the recordset I am using.

Thank you.

 

[RickSpr]

In Access 97, you can't directly access the recordset that corresponds to the form's RecordSource property. I think it has something to do with Access having knowledge of every change that happens via the recordset (either record navigation or changing data in the records). If you were able to get to the recordset, you might do a .MoveNext that Access didn't know about, so the form fields wouldn't be synchronized with the current record.

However, there is a way for you to accomplish most of what you might want to do with the recordset. The form has a RecordsetClone property that returns an exact copy of the form recordset, even positioned to the same record. You can assign this property to a Recordset variable, then use the variable to get the number of records, or inspect properties of its fields, for example.

You can also navigate through the records, though that doesn't change the "real" recordset's position nor the fields on the form. But here's a nice trick: You can search your recordset clone for the record you want (using FindFirst, for example), then assign the clone's Bookmark property to the form's Bookmark property. Example:
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "[LastName] = " & Me.txtSearchField
Me.Bookmark = rst.Bookmark
rst.Close
Set rst = Nothing

The form's Bookmark property isn't actually the same as the underlying recordset's Bookmark property. The form's property invokes a method that first saves any changes to the current record, then sets the recordset's Bookmark, and finally reloads the form controls from the new record. By providing a layer of control between you and the form recordset, Access can track what you're doing and keep the form and recordset synchronized.

Note: As with any recordset, you can't just use the .RecordCount property to find out how many records there are. The reason is that Access displays the form and allows user interaction as soon as the first record has been read. Reading in the rest of the recordset may continue in the background, or may even be postponed until later records are needed. So Access/Jet doesn't even know how many records there are until the whole recordset has been read. To ensure you get an accurate count, you have to do a .MoveLast method call, after which the RecordCount property will be correct. This is another good way to use the RecordsetClone property. Example:
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.MoveLast
cnt = rst.RecordCount
rst.Close
Set rst = Nothing Rick Sprague