MS Access 2010 - Event To Check Moving Off Record

Hi Experts,

I have a Menu Form that displays a record.  I want to trigger a Form Event whenever I move to another record on the form BEFORE actually going to the new record.  I need to stop going to the new record if a Sub-Form is open that is linked to the record.

I can detect if the Sub-Form is open, I just don't know how to trigger the code when an attempt is made to move records.

Thanks,
Bob C.
Bob CollisonSystem ArchitectAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Still pretty much back to the same issue.  Seems you are going to have to test for this condition in your custom Nav buttons ... seems if would only be a couple lines of code ... If Then Else or whatnot.
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
Try Using the OnCurrent event
Place your code in the Form_Current( ) procedure in the form's module.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
How are you  moving to a new record?
A button?
If so ... put code behind the button to do whatever checking you need to do.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
The Current Event is after the fact ... occurs *when* you reach the new record :-(
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Experts,

The movement to the can be forward or backward and is triggered by Navigations Buttons or Mouse Wheel.  I could put the code in there but would have to repeat it 5 times (First Record, Last Record, Previous Record, Next Record, Mouse Wheel).

While waiting for an answer I Googled 'On Current' and MSDN says:
"This event occurs both when a form is opened and whenever the focus leaves one record and moves to another. Microsoft Access runs the Current macro or event procedure before the first or next record is displayed."

So this looks like it is the correct event to use.

Now the question is, how do I stop it actually moving to the next record.
Could I issue a 'Cancel'?

Thanks,
Bob C.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
The Current Event happens  at the Next Record position ... so it's after the fact regarding what you are trying to do :-(

This is no specific event that that triggers when 'leaving' a record.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Also, how are you testing for a sub form 'open' ?

"Now the question is, how do I stop it actually moving to the next record.
Could I issue a 'Cancel'?"


That is the problem.  Unless you have your own Nav buttons, you can't.  There is no Cancel argument for the Current Event.
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Experts,
 I just tried the On Current Event and contrary to what MSDN says Database MX is correct, it occurs after displaying the next record.

Also 'Cancel' doesn't work.

Thanks,
Bob C.
0
 
PatHartmanCommented:
Subforms are not independent of Main forms.  Is your problem caused because you have a popup form?  

Please explain the problem rather than guessing at what the solution might be.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
The Microsoft bit is a tad misleading.  The displaying of the record and the Current event are basically simultaneous. The intent of the Current event is to do things like enable/disable, make visible/invisible controls based on various conditions.  It is not intended to set default or other values, etc ... a common mistake.
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Experts,

First, I guess I was misleading.  The 'Sub-Form' maybe should be called a 'Child Form' since it not a true sub-form.  It is basically independent of the Main (Menu) Form except for the 'criteria' supplied by the Menu in opening it.  This criteria includes data from the record on the Menu which is why I don't want the Menu record to be changed if the Sub-Form is open..

Secondly, I do have custom Navigation Buttons including a Mouse Wheel Function.

Since there are field on the Menu, is there an Event that I could use to trigger the checking and cancel moving to the next record?

Thanks,
Bob C.
0
 
PatHartmanCommented:
Your best solution is to open the child form as a dialog.  That way, the user has to close the child form before doing anything else.

I rarely allow a user to open multiple forms because it just confuses them and I never allow them to open dependent forms unless one of them is a dialog.  If you allow dependent forms to be open, you probably need code in the on Activate event (I think that is the correct event to use) that checks to make sure the calling form is still on the same record as the dependent form.
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Joe,

Yes I have come to the same conclusion.

Its easy enough with the Navigation Buttons, however I think the Mouse Wheel is a trickier issue since it is in a Function.  For the couple of instances where I need this functionality I may have to do the Mouse Wheel as a separate event.  i.e. Not using the Function.

Thanks,
Bob C.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well ... or you could do the Dialog form as Pat suggested .... if that works in your case.
If you have the Form Cycle property set to Current Record, then not sure the Mouse Wheel would be an issue ?

BTW .. the missing event you are asking for would be quite useful. But, not likely it will be added :-(
0
 
Dale FyeCommented:
The Form_BeforeUpdate event is as close as you are going to come to an event that would fire before you leave the current record.  But that will only fire if the forms recordset is dirty.

I generally roll my own navigation buttons and hide the built-in navigation button, so that I can control users abilty to move from one record to another, and can run whatever code I like before actually making that jump.

And I always disable the mouse wheel when I can.  I believe the mouse wheel is disabled by default in 2010.

Dale
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Disable the mouse wheel?
Wow.  If someone did that to me, I would be mad :-)
I constantly use the mouse wheel.
0
 
Dale FyeCommented:
let me rephrase that.

in older versions of access, the mouse wheel would move you off the current record on a form,  there was a mousehook that would allow you to trap for that to prevent it from moving off the current record.  In the most recent versions of Access, that is the default, and you actually  have to enable the mousewheel event to enable the wheel to change records.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
OK I remember ... I used mousehook als. In fact, I had a **shortcut*  menu (:-)) to turn it off/on.
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Experts,

Firstly,  my Users would kill me (if I didn't do it first) if I disabled the Mouse Wheel.
That said I have what I think is a very good implementation of code for it.  It originated from a query on the web.  I left the reference in the code.  I'll share it in an attachment.

As it turns out by putting the checking in the Mouse Wheel Calling Code I have solved my problem that I thought was going to be difficult.

Secondly, I had thought about using the Before Update Event but it didn't work out.

Thanks for all your comments / suggestions.
Bob C.
0
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.