IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, February 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:
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.
Paste in the code below into the module. To verify Access understands it, choose Compile on the Debug menu (in the code window.)
Save the module, with a name such as abjMouseWheel. (The module name is not important, but it must be different to the function name.)
Open your form in design view. On the Event tab of the Properties sheet, set the On Mouse Wheel property to:
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)
Repeat steps 4 and 5 for your other forms.
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
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.
|Home||Index of tips||Top|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'Scroll records with the mouse wheel in Access 2007'?
05 June 2008
I lost my scroll functions when they pushed 2007 w/o warning where I work. It was like suddenly losing the use of my thumb--I had no idea until I lost it just how much I use the mouse wheel. The code above got me back to work--thank you!
Have your say...