Access.2007.VBA.Bibl..

Private Sub TestKeysetOptimistic()

On Error GoTo ErrorHandler

Dim cnn As ADODB.Connection

Dim rst As ADODB.Recordset

Dim strCategory As String

Dim strPrompt As String

Dim strTitle As String

Dim strSearch As String

Create a connection to the current database.

Set cnn = CurrentProject.Connection

Set rst = New ADODB.Recordset

Create a recordset based on a table.

rst.Open Source:=”tlkpCategories”, _

ActiveConnection:=cnn.ConnectionString, _

CursorType:=adOpenKeyset, _

LockType:=adLockOptimistic

CategoryName:

Add a new record, getting a field value from the user.

strPrompt = “Please enter new category name”

strTitle = “New category”

strCategory = Nz(InputBox(prompt:=strPrompt, _

Title:=strTitle))

If strCategory = “” Then

GoTo ErrorHandlerExit

Else

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

& strCategory & Chr$(39)

Debug.Print “Search string: “; strSearch

With rst

.MoveLast

.MoveFirst

Debug.Print .RecordCount _

& “ records initially in recordset”

Check whether this category name has already been used — if the search fails, the cursor will be at
the end of the recordset.

rst.Find strSearch

If rst.EOF = False Then

strPrompt = Chr$(39) & strCategory _

Working with Access Data 5