Solved

MS Access 2010 - Event To Check Moving Off Record

Posted on 2016-09-28
19
31 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
  • 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
 

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 34

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 - Access MVP) 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 34

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

747 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

10 Experts available now in Live!

Get 1:1 Help Now