- without needing a DLL and without VBA subclassing
Download Demonstration MDB (Access 2000+) (Zipped)
Download Demonstration MDB (Access 97) (Zipped)
UPDATE v1.3: 29/08/2009 -- Improved VBA loader code - now supports VBA5 and VBA6.
UPDATE v1.4: 01/09/2009 -- Scroll wheel now disabled by default, for convenience.
UPDATE v1.5: 04/12/2009 -- now the Init and Scroll object methods are case insensitive (fixes potential "Object does not support..." errors)
This solution is being provided for free and you can use this solution in your applications freely provided you leave the copyright notices in place in the source code.
- The problem
- The old solutions
- The new solution
- Instructions for use
- How this works -- Warning, quite technical!
The problem
In Access versions prior to v2007, when you use the mouse scroll wheel on a form, Access will always cycle through the current records in your forms dataset. This is quite an annoyance when you might not want this behaviour, and Microsoft never offered a simple solution to turn this feature off (until v2007).
The old solutions
There have been several solutions to this over the years, including;
- A DLL based solution (such as MouseWheelOnOff by Stephen Lebans)
- A VBA subclassing solution (as described here)
There are other solutions such as forcing the currently displayed record to always be in edit mode, but we won't discuss them here - they have their own drawbacks too.
Each of these current solutions have drawbacks to them
For #1, you need to distribute a DLL alongside your product.
For #2, as discussed in the Microsoft KB article, the VBA subclassing solution is not advisable due to problems that can occur if the VBE window is open. Also, your forms will take a performance hit since every window message goes through the relatively inefficient VBA subsystem.
The new solution
My solution is to use the subclassing method as described in the VBA article above, but rather than use a VBA subroutine as the callback procedure, I use a native machine code compiled procedure (read the technical stuff below if you want to understand how this is achieved). The result is extremely efficient and best of all: there is no DLL to re-distribute - the whole functionality is implemented in a single VBA module.
This solution is being provided for free and you can use this solution in your applications freely provided you leave the copyright notices in place in the source code.
Instructions for use
Import the code module basMouseHook into your database application from the demonstration database: Access 2000 version - Access 97 version.
From within your forms OnOpen event, add this simple code:
Private Sub Form_Open(Cancel As Integer)
Static MouseHook As Object
Set MouseHook = NewMouseHook(Me)
End Sub
And that's all you need to know to make use of this new technique. Hopefully some of you will find this new method useful - if you do, feel free to leave your comments at the end of the article.
If you like this solution and appreciate the effort that went into creating it, please consider donating:
If you want further details on how the basMouseHook VBA module works... read on.
How this works -- Warning, quite technical!
VBA allows us, with the help of the RtlMoveMemory API call, to cast any numeric value into a VBA variable. So for instance, we can cast a Long integer into an Object variable, if we so wish. Ordinarily by doing this you will cause an instant crash since VBA expects the Object variable to contain a pointer to a valid in-memory COM object, and so will attempt to call the QueryInterface / AddRef / Release methods of the COM IUnknown interface when attempting to do anything at all with the variable.
However, if we setup an area of memory that replicates the layout, interfaces and machine code that VBA expects to find in an Object variable and then cast a pointer to that in-memory object into an Object variable, then VBA will treat our Object as any ordinary COM object.
This approach has several issues that have to be addressed:
- We need a way of setting up that area of memory correctly, including all the normal things that COM objects need - i.e. the actual native machine code, VTables and their pointers for all of the interfaces we support, and associated data sections.
- Adjusting relative memory offsets at runtime.
- Native machine code written in-memory like this has no way of interacting with the Operating System by default since it has no "handles" to any DLL libraries - it is just pure x86 machine code. So at first we have no method to call any APIs ... so we're quite limited in what we can offer. Fortunately this can be solved.
To solve #1 and #2
The approach I use is to have a "loader" or "shell code" which is written in pure assembly using only x86 opcodes that fall into the ASCII character set (so that we can reliably contain this machine code within a String variable in VBA). The loader code will be instantiated by VBA on it's first call to IUnknown::QueryInterface on our object.
The loader code is responsible for setting up the area of memory for our COM-compatible object and for adjusting the relative memory/pointer offsets before finally calling IUnknown::QueryInterface. The machine code that our object uses is encoded with a Base64-style encoding, which is also decoded by our loader on the first call to IUnknown:QueryInterface - this allows us full freedom to use all x86 opcodes once the loader has done its job.
To solve #3
The solution I use is to pass a handle to the kernel32 library and a pointer to the GetProcAddress API function from VBA to our COM object via a Init() method call. Once our COM object has the kernel32 handle and can call the GetProcAddress function, it can then call any other API functions at its discretion.
So there you go - a little insider info into how this technique all works. No doubt some people will call this approach a "hack", but to me this is just a "new technique" - the end result is a perfectly valid COM-compatible object created in memory dynamically from VBA code.
Regards,
Wayne Phillips
http://www.everythingaccess.com
