Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old October 7th, 2003, 05:41 AM
Authorized User
Join Date: Sep 2003
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default Intelligent Navigation Buttons

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.



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

    '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

    cmdPrevious.Enabled = Not (recClone.BOF)

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

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

'And finally close the cloned recordset


End Sub

Old October 7th, 2003, 09:07 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

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



Similar Threads
Thread Thread Starter Forum Replies Last Post
Intelligent Complaint-need help please rsearing Forum and Wrox.com Feedback 1 October 12th, 2006 08:45 AM
intelligent navigation buttons NeoCat Access 13 September 17th, 2005 07:43 PM
Wizard generated navigation buttons mgirard BOOK: Access 2003 VBA Programmer's Reference 3 January 11th, 2005 02:33 AM
Navigation buttons terry s Dreamweaver (all versions) 3 August 3rd, 2004 04:03 PM
Animated Buttons Inside Navigation Bars? Ben Horne Dreamweaver (all versions) 0 October 5th, 2003 09:59 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.