Dynaset-type recordsets represent the results of updatable queries, possibly based on more than one table. You can use these recordsets to add, edit, or delete records from one or more base tables. Dynaset-type recordsets support the more flexible Find methods (FindFirst, FindLast,

Argument Styles

When writing VBA code, you have two style choices: using argument names (as I do in most of the procedures in this book), which is more verbose, but allows you to skip arguments without causing syntax errors; or omitting argument names, in which case you have to make sure you have the right number of commas between arguments, with the spaces between commas representing the arguments you aren't using. I prefer using argument names for clarity, even though it makes my code a little longer.

If you use one argument name, you must use argument names for all the arguments of a function or method you use — mix and match is not permitted.

FindNext, FindPrevious); unlike the Seek method for table-type dynasets, you don't need to set an index, and you can search for a value in any field in the recordset. Here is an example in which the code searches for the last record with a matching value in the IDLabel field and displays the Company ID for that record in a message box:

Private Sub ListCompany()

Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim strValue As String Dim strPrompt As String Dim strTitle As String Dim strSearch As String


strValue = InputBox(prompt:="Please enter an ID label", _

Title:="ID Label", Default:="E-Mail Address") strSearch = "[IDLabel] = " & Chr$(39) & strValue _ & Chr$(3 9)

Debug.Print "Search string: " & strSearch Set dbs = CurrentDb

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

Type:=dbOpenDynaset) rst.FindLast strSearch If rst.NoMatch = True Then strPrompt = "Couldn't find " & strValue & _

"; please try again" strTitle = "Search failed"

MsgBox prompt:=strPrompt, Buttons:=vbCritical _

+ vbOKOnly, Title:=strTitle GoTo EnterID Else strPrompt = "The last Company ID for " & strValue _

& " is " & rst![CompanyID] strTitle = "Search succeeded"

MsgBox prompt:=strPrompt, Buttons:=vbOKOnly _ + vblnformation, 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 vbCrLf and 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.

0 0

Post a comment