Record Navigation and Everyone's Friend the Wheel Mouse

Posted on 2007-07-30
Last Modified: 2008-02-01
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.

Question by:tommyboy115
    LVL 44

    Expert Comment

    by:Leigh Purvis

    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. :-)

    Author Comment

    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.

    LVL 44

    Expert Comment

    by:Leigh Purvis
    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).

    Author Comment

    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.
    LVL 44

    Expert Comment

    by:Leigh Purvis
    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?

    Author Comment

    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.
    LVL 44

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now