Table-type recordsets represent base tables (that is, tables located within the database from which the code is running, as opposed to linked tables). You can add, edit, or delete records from a table using a table-type recordset. These recordsets don't support the Find methods (FindFirst, FindLast, FindNext, FindPrevious); instead they support the Seek method.

To run one of the procedures in the DAO and ADO Sample Code database, place your cursor inside the procedure and press F5, or select Run Sub/UserForm from the Run menu in the Visual Basic window.

If you don't get a response when pressing a function key, function keys may be disabled; some newer keyboards disable function keys by default. To enable function keys, press the F Lock key.

The following code segment searches for the record with a specific value (retrieved from an InputBox) in the CompanyID field and, if it is found, displays the value in the ID/AccountNumber field for that record in a message box:

Private Sub ListID()

Dim dbs As DAO.Database Dim rst As DAO.Recordset

Dim strValue As String Dim strPrompt As String Dim strTitle As String

Set dbs = CurrentDb

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

Type:=dbOpenTable) rst.Index = "CompanylD"


strValue = lnputBox(prompt:="Please enter a company ID"

Title:="Company ID", Default:="TEAC") rst.Seek Comparison:="=", key1:=strValue 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 first ID for " & strValue _

& " is " & rst![ID/AccountNumber] strTitle = "Search succeeded"

MsgBox prompt:=strPrompt, Buttons:=vbOKOnly _ + vbInformation, Title:=strTitle

End If End Sub

Figure 5.8 shows the message box with the ID for the selected company.


A message box displaying an ID for a selected company.


0 0

Post a comment