Microsoft Access Tips for Serious Users

Provided by Allen Browne, February 2007

Scroll records with the mouse wheel in Access 2007

In previous versions, scrolling the mouse jumped records. This caused a range of problems: incomplete records were saved, and people were confused about why their record disappeared when they bumped the mouse. Some developers completely disabled the mouse wheel.

Microsoft gave Access 2007 a sensible compromise: disable the mouse wheel in Form view, and scroll records in Datasheet and Continuous view.

If you preferred the old approach, you can use the form's Mouse Wheel event to get the old behavior back. Before you do this, you might want to consider whether the new approach is more logical, particularly if anyone else will use your database.

Assuming you are using Access 2007, the steps are:

1. On the Create tab of the ribbon, in the Other group (rightmost), click the arrow below Macro, and choose Module. Access opens a new module.

2. Paste in the code below into the module. To verify Access understands it, choose Compile on the Debug menu (in the code window.)

3. Save the module, with a name such as abjMouseWheel. (The module name is not important, but it must be different to the function name.)

4. Open your form in design view. On the Event tab of the Properties sheet, set the On Mouse Wheel property to:
       [Event Procedure]

5. Click the Build button (...) beside the property. Access opens the code window. Between the Private Sub ... and End Sub lines, enter:
       Call DoMouseWheel(Me, Count)

6. Repeat steps 4 and 5 for your other forms.

The code

Public Function DoMouseWheel(frm As Form, lngCount As Long) As Integer
On Error GoTo Err_Handler
    'Purpose:   Make the MouseWheel scroll in Form View in Access 2007.
    '           This code lets Access 2007 behave like older versions.
    'Return:    1 if moved forward a record, -1 if moved back a record, 0 if not moved.
    'Author:    Allen Browne, February 2007.
    'Usage:     In the MouseWheel event procedure of the form:
    '               Call DoMouseWheel(Me, Count)
    Dim strMsg As String
    'Run this only in Access 2007 and later, and only in Form view.
    If (Val(SysCmd(acSysCmdAccessVer)) >= 12#) And (frm.CurrentView = 1) And (lngCount <> 0&) Then
        'Save any edits before moving record.
        RunCommand acCmdSaveRecord
        'Move back a record if Count is negative, otherwise forward.
        RunCommand IIf(lngCount < 0&, acCmdRecordsGoToPrevious, acCmdRecordsGoToNext)
        DoMouseWheel = Sgn(lngCount)
    End If

Exit_Handler:
    Exit Function

Err_Handler:
    Select Case Err.Number
    Case 2046&                 'Can't move before first, after last, etc.
        Beep
    Case 3314&, 2101&, 2115&   'Can't save the current record.
        strMsg = "Cannot scroll to another record, as this one can't be saved."
        MsgBox strMsg, vbInformation, "Cannot scroll"
    Case Else
        strMsg = "Error " & Err.Number & ": " & Err.Description
        MsgBox strMsg, vbInformation, "Cannot scroll"
    End Select
    Resume Exit_Handler
End Function

How it works

You can use the code without understanding how it works, but it boils down to just the highlighted line.

The function accepts two arguments:

• a reference to the form (which will be the active form if the mouse is scrolling it), and

• the value of Count (a positive number if scrolling forward, or negative if scrolling back.)

Firstly, the code tests the Access version is at least 12 (the internal number for Access 2007), and the form is in Form view. It does nothing in a previous version or in another view where the mouse scroll still works. It also does nothing if the count is zero, i.e. neither scrolling forward nor back.

Before you can move record, Access must save the current record. Explicitly saving is always a good idea, as this clears pending events. If the record cannot be saved (e.g. required field missing), the line generates an error and drops to the error hander which traps the common issues.

The highlighted RunCommand moves to the previous record if the Count is negative, or the next record if positive. This generates error 2046 if you try to scroll up above the first record, or down past the last one. Again the error handler traps this error.

Finally we set the return value to the sign of the Count argument, so the calling procedure can tell whether we moved record.