Access.2007.VBA.Bibl..

FindNext, FindPrevious); unlike the Seekmethod 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

EnterID:

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

Title:=”ID Label”, Default:=”E-Mail Address”)

strSearch = “[IDLabel] = “ & Chr$(39) & strValue _

& Chr$(39)

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

Part II Writing VBA Code to Exchange Data between Office Components

Argument Styles

W

hen 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 with-

out 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 represent-

ing 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.