Access.2007.VBA.Bibl..

strPrompt = “The last Company ID for “ & strValue _

& “ is “ & rst![CompanyID]

strTitle = “Search succeeded”

MsgBox prompt:=strPrompt, Buttons:=vbOKOnly _

+ vbInformation, Title:=strTitle

End If

End Sub

Snapshot

A snapshot-type recordset is a read-only copy of a set of records, useful only for viewing data or
generating reports. The following procedure moves through a recordset based on a table, writing
the values in several fields to the Immediate window, using the VB constants vbCrLfand vbTab
to create line breaks and indents for better readability:

Private Sub ListValues()

Dim dbs As DAO.Database

Dim rst As DAO.Recordset

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(Name:=”tblCompanyIDs”, _

Type:=dbOpenSnapshot)

Do While Not rst.EOF

Debug.Print “Company ID: “ & rst![CompanyID] _

& vbCrLf & vbTab & “ID Label: “ & rst![IDLabel] _

& vbCrLf & vbTab & “ID/Account No.: “ _

& rst![ID/AccountNumber] & vbCrLf

rst.MoveNext

Loop

End Sub

The results of running this procedure for two records are listed as follows:

Company ID: MS Office & VBA

ID Label: CIS ID

ID/Account No.: 70304,3633

Company ID: Fisher Consulting

ID Label: E-Mail Address

ID/Account No.: [email protected]

Unlike table-type and dynaset-type recordsets, you can work with a snapshot-type recordset even if
the underlying table is open, which can occasionally be useful.

Working with Access Data 5