Record Navigation and Everyone's Friend the Wheel Mouse

I want to handle a wheel mouse within my Access application but I don't want to disable it.  I've been using the Lebans soltution for a long time but I've found it works best when you turn off the wheel mouse right away, which disables it everywhere else, including spots where you'd like it to work.

I NEVER navigate through records in my forms.  When editing a record, I open the single record using an underlying query for that record only and open the form using acEdit, with AllowAdditions off.  The hell mouse isn't a problem there.  When adding a new record, I open it as acNew using the same form.  With the lebans mousehook in place, it does prevent the user from scrolling to another new record, but as I said before, I don't want to keept turning it on and off because it gets messed up.

Basically, I just want to open this form with acNew and allow only one new record to be added.  My solution was to set AllowAdditions to false on the fom's AfterInsert event.  This worked, but forced the update of the record.  This creates a problem because if I have a user who opens the form, doesn't end up wanting to save what they did and clicks Undo (Me.Undo), the record has already been saved with nothing in it because I set AllowAdditions to false (I don't know why that happens but it does) and I have a ton of bogus records.

So, considering I never want the form to move from record to record, how do I stop Access from doing it without having to jump through a bunch of hokey hoops.

Who is Participating?
Leigh PurvisDatabase DeveloperCommented:
Well, BeforeUpdate code needn't be related to a Save button as such.
But you would need to decide what constitues a record worthy of saving - or a saving event.
i.e. by what means is it acceptable to commit the form?
By actually having a Save button?
Or just by closing the form?

I'm just saying that - if you don't want to use Stephen's mouse hook (or use Access 2007 which has better mousewheel control ;-) then you'd need to disregard an attempt to update the record *other* than by your specified close button - or whatever.
For example - say you had a Close button.
Then a form level variable which when set to True prevented the form from updating (sets Cancel = True in the BeforeUpdate event of so).
However in your close button - you'd first set that flag to false - *then* issue the close command (which could allow the close and append to progress).

Other than that - then yes, you're implementing the hook still.
Leigh PurvisDatabase DeveloperCommented:

Some pertinent questions perhaps?
What do you consider to be jumping through a bunch of hoops?  Since you say you want to use the acNew parameter (of the OpenForm method I presume) then this means control like that which you get with an unbound form isn't a possibility?

How is the record being committed (so as to trigger your AfterInsert event)?
Through attempted navigation to another new record?  (As that placing along should indeed make sure you get only that one record)

Either way (i.e. by navigation of committing through a Save command (e.g. acCmdSaveRecord or Me.Dirty = False) then your main concern is that your Undo isn't working for you?
I'd suggest that you're not using a sufficiently powerful Undo.
The form's Undo method is fine for certain tasks - but not beyond updating the record.
While you'll initially get a prompt to confirm record deletion if you use an Uno method like
DoCmd.RunCommand acCmdUndo
then the record should be removed as you'd expect.

Obviously - if the user closes the form - then there's little you can do.  That's there choice - they're "done".
Also - there is no subform present is there?  Such navigation would leave you with a non-undoable commited record, regardless of the method employed.  But expecting to undo records where subrecords can be entered would be unusual anyway. :-)
tommyboy115Author Commented:
My comment about jumping through a bunch of hoops was having to implement a convoluted workaround for something that you'd think would have a straightforward fix.  Ultimately, I am looking for a bit of unbound functionality with a bound form.  The record is being committed through Me.Dirty = False.  To start a new record, the user exits from the form using a save & exit button and then opens the form again when they need too.  This isn't a hard core data-entry app.  The only way a user can close the form is by that or clicking undo.  This all works with validation and without a problem.

My main concern is the wheel mouse allowing movement to a new record or activating my validation on the BeforeUpdate event and the user wondering why that happened.  Is there a way, when a user opens a form with a new bound record to prevent the user from moving ahead to another new record.  I know I can prevent it by disabling the navigation buttons, but the wheel mouse still gives them the ability, which is my problem.  What I've tried so far, i.e. setting AllowEdits = False on the AfterInsert event forces the update of the record creating the bogus records.  I could programmatically check the validity of the record and then delete on the undo if the user aborted, but it just seems like unnecessary code.

In the end, if this something that doesn't have a straightforward answer, I'll just keep using the lebans solution to disable the wheel mouse.  It just seems silly to have to do so and eliminate its value elsewhere.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Leigh PurvisDatabase DeveloperCommented:
Your AfterInsert choice should equally prevent subsequent record entry.
If you're wanting to prevent advancing to another record that's a different issue (in that you're confirming the validity of the record in the BeforeUpdate event - and if it fails that then you Cancel that event - thus preventing the user from committing the record - yes?)

That way the Undo method shouldn't even be required - in that only valid records have been committed anyway.

For controlling updates and so forth on bound forms you might get an idea or two from the Cancel Undo example here
(Essentially using a variable flag to permit progress or not).
tommyboy115Author Commented:
I guess the problem is none of these remedies directly confront the issue of preventing record movement with the mouse wheel.  I understand all the things I can do before the record is updated, the problem is that I don't want clicking the mouse wheel to make these events fire at all.  My thought was that if I could "stop" the form from adding more records after adding just one record, then I could prevent the movement.  The ways to do that cause more problems.  I guess I'll stick with the lebans route.
Leigh PurvisDatabase DeveloperCommented:
My question to you is still just - what's wrong with your AfterInsert event change?
In what way is that failing to prevent users from navigating to other rows?

What have you tried - or not tried because you don't feel it will work for your situation.

You can prevent the record from being saved until you're satisfied it's worthy of being saved.
Once saved you can prevent  subsequent records from being added by using your AfterInsert method to disallow additions.

Where is the hole you're wanting to plug?
tommyboy115Author Commented:
Setting AllowAdditions to false on the AfterInsert event requires the record to be updated.  I don't want that to happen until the user clicks save, not when they accidently spin the mosue wheel.  My hole is that I don't want anything to happen when the mouse wheel is moved.  Moving the mouse wheel moves the form to another record.  I guess I don't know how else to explain it.  Lebans seems to be the only option.  Everything else we've talked about is how I would configure a Save button and not addressing the scroll wheel specifically, which is all I care about.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.