Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access 2010 - Event To Check Moving Off Record

Posted on 2016-09-28
19
Medium Priority
?
58 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 23

Assisted Solution

by:Ferruccio Accalai
Ferruccio Accalai earned 200 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 39

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 1600 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 39

Assisted Solution

by:PatHartman
PatHartman earned 200 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 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

by:Dale Fye
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

704 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