Solved

MS Access 2010 - Event To Check Moving Off Record

Posted on 2016-09-28
19
38 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 35

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 - 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 35

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

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…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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.

813 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

11 Experts available now in Live!

Get 1:1 Help Now