I worked through many of the examples in the book, Beginning Access 2002 VBA. The VBA code for intelligent navigation buttons (Private Sub Form_Current( ) – page 241 & 242) worked for the Ice Cream Database; however, it did not work when I copied the code over to my database to navigate through a form that keeps track of personal information for my employees. When I attempt to open my form, I get a “Runtime Error ‘13’: Type Mismatch.” I would appreciate help from anyone familiar with this section of the book, or anyone else who may be able to give me some suggestions.

FYI, the code follows.

Thanks,



David

Private Sub Form_Current()
Dim recClone As Recordset

'Make a clone of the recordset underlying the form so
'we can move around that without affecting the form's
'recordset
Set recClone = Me.RecordsetClone()

'If we are in a new record. disable the <Next> button
'and enable the rest of the buttons
If Me.NewRecord Then
    cmdFirst.Enabled = True
    cmdPrevious.Enabled = True
    cmdNext.Enabled = False
    cmdLast.Enabled = True
    cmdNew.Enabled = False
    Exit Sub
End If

'If we reach here, we know we are not in a new record
'so we can enable the <New> button if the form allows
'new records to be added
cmdNew.Enabled = Me.AllowAdditions

'But we need to check if there are no records. If so
'we disable all buttons except for the <New> button
If recClone.RecordCount = 0 Then
    cmdFirst.Enabled = False
    cmdNext.Enabled = False
    cmdPrevious.Enabled = False
    cmdLast.Enabled = False
Else

    'If there are records, we know that the <First> and
    '<Last> button will always be enabled, irrespective
    'of where we are in the recordset

    cmdFirst.Enabled = True
    cmdLast.Enabled = True

    'Syncronize the currect pointer in the two recordsets

    recClone.Bookmark = Me.Bookmark

    'Next we must see if we are on the first record
    'If so, we should disable the <Previous> button

    recClone.MovePrevious
    cmdPrevious.Enabled = Not (recClone.BOF)
    recClone.MoveNext

    'And then check whether we are on the last record
    'If so, we should disable the <Next> button

    recClone.MoveNext
    cmdNext.Enabled = Not (recClone.EOF)
    recClone.MovePrevious
End If

'And finally close the cloned recordset

recClone.Close

End Sub

Hi T,

Be sure you have a reference set to the Microsoft DAO 3.x library, then change Dim recClone As Recordset to Dim recClone As DAO.Recordset (always fully qualify data access objects like this, with library.object syntax).

Your default library is ADO, the recordsets of bound forms in .mdb files are always DAO recordsets. So Set rst = Me.RecordsetClone always returns a DAO recordset in an .mdb file. (though recordsetClone returns an ADO recodset in an .adp file).

In fact, since Access (.mdb) knows that the return value of RecordsetClone is always a DAO recordset, code like:

Private Sub cboSearch_AfterUpdate()
    Dim rst As DAO.Recordset

    Set rst = Me.RecordsetClone

    rst.FindFirst "CustomerID=" & cboSearch
    Me.Bookmark = rst.Bookmark
     cboSearch = Null
End Sub

the above can be simplified to:

Sub cboSearch_AfterUpdate()
    Me.RecordsetClone.FindFirst "CustomerID=" & cboSearch
    Me.Bookmark = Me.RecordsetClone.Bookmark
    cboSearch = Null
End Sub

HTH,

Bob