When you close a Recordset object, any bookmarks you saved become invalid. You can’t use a bookmark from one Recordset object in another Recordset object, even if both Recordset objects are based on the same underlying table or query. However, you can use a bookmark on the duplicate (clone) of a Recordset object, as shown in the following example. In this example, strDbPath is the path to the NorthwindTables database:
Dim dbs As Database, rstOrig As Recordset
Dim rstClone As Recordset, varPlace As Variant
Set dbs = OpenDatabase(strDbPath)
Set rstOrig = dbs.OpenRecordset("Customers", dbOpenDynaset)
rstOrig.MoveLast
' Save current record location.
varPlace = rstOrig.Bookmark
' Create duplicate recordset.
Set rstClone = rstOrig.Clone
MsgBox "Before setting bookmark: Current Record CompanyName = " _
& rstClone!CompanyName
' Go to saved record.
rstClone.Bookmark = varPlace
MsgBox "After setting bookmark: Current Record CompanyName = " _
& rstClone!CompanyName
Microsoft Access Users You can also use the Bookmark property on the Recordset object underlying a form. With this property, your code can mark which record is currently displayed on the form, and then change the record being displayed. For example, on a form containing employee information, you may want a button that a user can click to show the record for an employee’s supervisor. The following example illustrates the event procedure you would use for the button’s Click event:
Private Sub cmdShowSupervisor_Click()
Dim rst As Recordset
Dim varOrigin As Variant, strEmployee As String
Dim strSuper As String
' Open Recordset object.
Set rst = Me.RecordsetClone
' Store bookmark for current record.
varOrigin = Me.Bookmark
' Store first and last name of employee.
strEmployee = Me!FirstName & " " & Me!LastName
' Check whether employee reports to anyone.
If Not IsNull(Me!ReportsTo) Then
' Search for first record matching value of ReportsTo field.
rst.FindFirst "EmployeeID = " & Me!ReportsTo
' Check whether record for supervisor exists in table.
If rst.NoMatch Then
MsgBox "Couldn't find " & strEmployee & "'s supervisor."
Else
' If supervisor record exists, set form's bookmark.
' Current record is now supervisor's record.
Me.Bookmark = rst.Bookmark
' Store supervisor's first and last name.
strSuper = Me!FirstName & " " & Me!LastName
' Display supervisor's name.
MsgBox strEmployee & "'s supervisor is " & _
strSuper & "."
' Move back to employee's record.
Me.Bookmark = varOrigin
End If
Else
MsgBox strEmployee & " has no supervisor."
End If
rst.Close
End Sub
If you have used another database or programming environment, you may be accustomed to referring to record numbers. For example, you may have written code that opens a text file and thereafter refers to specific records by their relative position in the file. The first record in the file would be record 1, the second would be record 2, and so on.
In Microsoft Jet databases, your view of records (a Recordset object) is usually a subset of the records in one or more tables. Because the actual number of records in a Recordset object can change at any time, especially in a multiuser environment, there’s no absolute record number you can always use to refer to a particular record. The AbsolutePosition property is not the same as a record number, because this property changes if a lower-numbered record is deleted.
Furthermore, records returned in a Recordset object appear in no particular order, unless the Recordset object was created with a query that includes an ORDER BY clause, or is a table-type Recordset object with an index. For this reason, record numbers are meaningless in a Recordset object.
Instead of record numbers, Microsoft Jet provides bookmarks so that you can uniquely identify a particular record. A given record retains its unique bookmark for the life of the Recordset object.
Visual C++ Users Bookmarks are especially useful in conjunction with the GetRowsEx method in the DAO Software Development Kit (SDK). If you choose dbBindBookmark as one of the fields to be bound, it will retrieve a bookmark as a pseudo-field. This enables you to combine the speed and convenience of the GetRowsEx method with easy updating.
For example, you could have retrieved code to fill a list box that looks something like:
// Structure for DoGetRowsEx
typedef struct
{
LONG lEmpId;
LPVOID lpbm;
TCHAR *lpstrLastName;
TCHAR strFirstName[20];
} EMP, *LPEMP ;
// Employee table binding
DAORSETBINDING Bindings[] =
{
//Index Type Column Type Offset Size
{dbBindIndexINT, EMP_ID, dbBindI4, offsetof(EMP,lEmpId), sizeof(LONG)},
{dbBindIndexINT, NULL, dbBindBookmark, offsetof(EMP,lpbm), sizeof(LPVOID)},
{dbBindIndexINT, EMP_LNAME, dbBindLPSTRING, offsetof(EMP,lpstrLastName),sizeof(TCHAR *)},
{dbBindIndexINT, EMP_FNAME, dbBindSTRING, offsetof(EMP,strFirstName), sizeof(TCHAR) * 20}
};
// Perform C++ GetRowsEx against the Employee table.
void CDlg::DoGetRowsEx()
{
LPEMP pEmpRows = new EMP[MAX_EMP_REC];
CString strLBRow;
TCHAR szId[16];
LONG lNumRecords;
LONG lCount;
LONG cbBuf = ( MAX_EMP_REC * sizeof(TCHAR) * 15 ); // Allow average of 15 chars/name.
LPVOID pvBuf = new LPVOID[cbBuf];
//Perform GetRows on Employee table.
//This GetRows uses a specific C++ structure.
lNumRecords = m_rs.GetRowsEx(pEmpRows, sizeof(EMP),
&Bindings[0], sizeof(Bindings) / sizeof(DAORSETBINDING),
pvBuf, cbBuf,
MAX_EMP_REC); //Arbitrarily get MAX_EMP_REC rows.
//Step through the returned rows.
for (lCount = 0; lCount < lNumRecords; lCount++)
{
strLBRow.Empty();
wsprintf(szId, _T("%d, "), pEmpRows[lCount].lEmpId);
strLBRow += szId;
strLBRow += pEmpRows[lCount].lpstrLastName;
strLBRow += _T(", ");
strLBRow += (LPCTSTR) pEmpRows[lCount].strFirstName;
//Put the bookmark in the global array.
m_rgbm[lCount] = CdbBookmark((LPSAFEARRAY)pEmpRows[lCount].lpbm );
// Put a pointer to it in the ItemData for the list box entry.
m_lstEmps.SetItemData( m_lstEmps.AddString(strLBRow),
(DWORD)&m_rgbm[lCount] );
}
delete [] pEmpRows;
delete [] pvBuf;
}
See Also For more information about the GetRowsEx method, see Chapter 11, “Programming with DAO in C++ and Java.” For a complete sample program, see the Visual C++ project file in the \Program Files\DevStudio\DAOSDK\SAMPLES\GETROWS folder of the DAO SDK, which is installed with Microsoft Visual C++.
The prior code example could be combined with selection code that moves the current record as follows:
void CDlg::OnSelchangeListEmployees()
{
// Cast the dword held in ItemData back into a pointer to a
// bookmark. The actual bookmarks are being held in m_rgbm
// and will automatically deallocate the system safe arrays and
// destruct on CDlg destruction.
CdbBookmark *pbm = (CdbBookmark *)m_lstEmps.GetItemData(m_lstEmps.GetCurSel());
m_rs.SetBookmark( *pbm );
m_strMoreInfo.Format(
_T("Notes: %s\n"),
(LPCTSTR)( m_rs[15].GetValue().bstrVal ) );
UpdateData( FALSE );
}