Solved

MS Access 2010 - Event To Check Moving Off Record

Posted on 2016-09-28
19
41 Views
Last Modified: 2016-09-28
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.
0
Comment
Question by:Bob_Collison
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 2
  • +2
19 Comments
 
LVL 22

Assisted Solution

by:Ferruccio Accalai
Ferruccio Accalai earned 50 total points
ID: 41820359
Try Using the OnCurrent event
Place your code in the Form_Current( ) procedure in the form's module.
0
 
LVL 75
ID: 41820361
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
 
LVL 75
ID: 41820362
The Current Event is after the fact ... occurs *when* you reach the new record :-(
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Bob_Collison
ID: 41820520
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
 
LVL 75
ID: 41820535
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
 
LVL 75
ID: 41820538
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
 

Author Comment

by:Bob_Collison
ID: 41820542
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
 
LVL 36

Expert Comment

by:PatHartman
ID: 41820561
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
 
LVL 75
ID: 41820562
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
 

Author Comment

by:Bob_Collison
ID: 41820590
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 400 total points
ID: 41820618
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
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 50 total points
ID: 41820636
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
 

Author Comment

by:Bob_Collison
ID: 41820638
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
 
LVL 75
ID: 41820651
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41820864
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
 
LVL 75
ID: 41820969
Disable the mouse wheel?
Wow.  If someone did that to me, I would be mad :-)
I constantly use the mouse wheel.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41820974
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
 
LVL 75
ID: 41821011
OK I remember ... I used mousehook als. In fact, I had a **shortcut*  menu (:-)) to turn it off/on.
0
 

Author Comment

by:Bob_Collison
ID: 41821019
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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

735 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