Access.2007.VBA.Bibl..

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 = “CompanyID”

EnterID:

strValue = InputBox(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.

FIGURE 5.8

A message box displaying an ID for a selected company.

Dynaset

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 Findmethods (FindFirst, FindLast,

Working with Access Data 5