Help with events

Topic

New Reply

I know I should have found a good book by now, but the selection available to me is rather limited (not to mention non existent in the “not really a programmer but don’t need screenshots of dialogue boxes thanks” department), so I hope some kind soul will help me out.

I want to attach a procedure to a form that prevents the user exiting that form and puts up a message box warning him or her that certain tasks have not been completed. I wish to use flags rather than required or validation properties as I need them to be set and reset from different parts of the form. I understand that I can use a BeforeUpdate procedure to do this, but I have 3 questions:

1) I assume that if all flags are down and the initial if condition of the procedure is not met, then after the procedure exits the other update events continue as normal, and the record is saved. Is this not the case? Do I have to force a save?
2) What happens if the user tries to close the form? I have had a similar problem with an error trapping mechanism on another field – if the form is closed, the message box appears, but when OK is clicked on, the form closes anyway, rather than staying open and directing the user to the field in question.
3) If a flag is up, and the user is directed back to the form, I assume all the recent changes are preserved, but not saved until the record is exited.

I’m afraid online help has rather deserted me with a lack of examples in this regard.

A related question – is there an update property that can be accessed, for instance if one wants to run some code on a control being exited, but one of many conditions to decide whether to execute a block is whether that control has been updated, is there a property that can be read, rather than having one block attached to some conditions on exit (ie to be assessed even if there has been no update), and the same block in a before or after update procedure. It would also be nice to confirm update=false. At present I do this by comparing control value with control.oldvalue, but it’s rather cumbersome.

Thanks

Mark

Reply | Quote

Replies

1) if you use the BeforeUpdate event of the form and set Cancel=True, everything stops right there. No further updates are performed and nothing is saved. The individual controls may have updated, but the record itself is not saved until the form’s AfterUpdate event occurs. If the BeforeUpdate event is cancelled, the AfterUpdate event doesn’t occur.

2) If you disable the Close button (the X in the upper right corner) then the user will have to close the form using the mechanisms you provide. If you code the command button’s Click event to only issue a DoCmd.Close if certain conditions are met, the close event of the form will only occur if those conditions are met. You can’t do this in the form’s Close event, only in the code behind your command button.

3) I’m not sure what you mean here. If you cancel the form’s BeforeUpdate event, nothing gets backed out unless you issue a me.undo.

I think you lost me on the additional question. The simplest way I’ve found to deal with this is to load the initial values of the controls into module level variables in the OnCurrent event of the form. Then it’s easy enough to compare those values to the current value of each control in the form’s BeforeUpdate or AfterUpdate event in order to determine whether any control has been updated.

Reply | Quote

Thanks for your help Charlotte (as always).

I have one more event related question.

I want an event to happen each time the user leaves a record to move to another one in a form (single form view). The OnCurrent event is not useful unless there is some way of finding out the previous record viewed, which I have yet to find. I could disable the navigation buttons and make my own, which is not only a hassle, but also does not catch moving between records using the wheel of a wheel mouse or CTRL+PageDown.

I have read the description of every event that I can find in Help, and also looked at MS online Access offerings, but to no avail. A lot of the promising entries about being able to identify the last record that I found in help, however, don’t seem to be available (usually marked DAO).

Am I missing something?

Thanks

Mark

Reply | Quote

You’re missing an explanation of what you’re trying to do. What do you mean by …[indent]

---

The OnCurrent event is not useful unless there is some way of finding out the previous record viewed

---

[/indent] What do you need to know about the previous record?

Reply | Quote

Sorry, I was trying not to write another essay.

Once a record has been entered, I want to make sure that any editing is performed through certain structures, so that various hidden identity fields are updated correctly. It is not possible just to disable the controls, however, as some editing may be necessary. I have structures in place that in the absence of any errors should ensure that all the data is edited in the correct way (the mechanism I have in place is procedures running on the GotFocus event of the controls in question, which force the user into a particular edit path (via subforms)), but there are ways round this, notably if any errors occur. The most difficult form of error to handle is where the primary key value already exists in one of the tables, which is possible due to the poor quality of the underlying data (and the poor quality of my first attempts to get this to work).

So what I’d like to do is set flags when data is edited in the controls. These flags are then cleared if the correct editing procedure is followed, but I want to check before a user leaves that record that none of these flags are up, and force a re-edit if they are. Another possibility would be to lock all the fields, giving an additional level of protection, but this can only happen when they have been updated (in the case of a new record or a record that has been edited), and the moment when that record is left to maove to another would be the perfect time to do that. It is more difficult to do that on form update, because I have to force a number of refreshes during the entry of a record to populate some comboboxes appropriately.

What I meant by finding out about the last record at the on current event is this: the on current event happens when the user moves to another record. In order to conduct the procedures noted above I would need to find out the identity (either primary key value or record number) of the record that was just left.

I should add that I want to be able to perform these checks any time the user leaves a record – be it new, old, edited or unedited, so that I can catch any previously missed events.

I hope this is making some sense.

Mark

Reply | Quote

Use the BeforeUpdate event of the form to call a routine that tests for any flags set during the edit. It doesn’t really make sense to run this on a record that hasn’t been edited if you always run it on an edited record since I assume you have to edit a record before any flags are set, right? If that is not the case, I would suspect that your form is not as well designed as it might be. Keys that are set conditionally should be set by the controls themselves. You can use the AfterUpdate event of each control to write anything else to additional fields, but this is not a foolproof method as you have found. Clicking a command button should trigger a test routine to check for flags before it processes the rest of the command button’s attached event procedure.

Basically, there is no single event that you can use to accomplish this. You have to cover all your bases, which means you have to outthink your user. Build a single test routine that looks at all the controls and applies the appropriate tests and call that routine from places like the command button’s click event, the beforeupdate event of the form, the afterupdate event of the control, etc. Allow the test routine to accept an optional control argument so that an individual control can pass itself to the test routine and only trigger tests specific to that control. Use the tab order of the form’s sections to control the automatic progression through the controls. Use the afterupdate event of each control to call a test of that control and to write associated data into other controls. If you need to force certain controls to be populated first, disable the other controls by default and only enable controls when the necessary conditions have been met.

Reply | Quote

Seems that I was trying to do something that noone in their right mind would, but as I’ve now cracked it might as well post it.

As I couldn’t use the BeforeUpdate event because of the number of refreshes forced by conditional comboboxes and subforms, the OnCurrent event was the key.

Having disabled close and navigation buttons and set up my own buttons to run some checks before doing this, I also got the buttons to set a flag. If this flag is set, the OnCurrent procedure loads the value of current record into a global variable. If this flag is not set, then the OnCurrent procedure returns to the record whose number is in this variable, and resets all flags. I have had to add a couple of extra flags and conditional clauses and have 2 passes at this event in order to prevent the oncurrent events cascading, and set the flag in the forms onopen event procedure to prevent problems entering the first form, but it now picks up all finds, ctrlF’s, CtrlPgDwn, menu navigation events etc, and slaps wrist for not having used the buttons I have so thoughtfully provided. I have a similar system in the forms onunload event procedure to catch any menu driven closes or CtrlF4’s.

I do have a number of minor problems left:
1) As these navigation tools are now buttons, the onexit event of any control that has the focus when one of these buttons are clicked still happens, which did not happen using the built in navigation buttons. Is there a way round this?

2) I cannot find any scroll controls in Access VBA. When the focus shifts back from the subform to the main form, I can find no way of controlling the position of the form in the window – it seems predetermined that the control (not even the label, but the control) which now has the focus is positioned to the far left and just below the top corner of the window. Short of creating a dummy control which would induce satisfactory positioning of the form in the window and directing the focus first to this and then immediately on to the control I want the focus to be with, I cannot seem to find a way round this. Excel scroll commands don’t work either, and as the only way to move the position of the form in the window is by the scroll bars, I can’t even use the sendkeys statement.

3) Although I have set the properties of the subforms to border size – resizable (in their form properties) and can grow (in their “control of parent form” properties), they persistently refuse to grow to accomodate all the records. Turning the scroll bars off doesn’t seem to help, either.

Just while I’m on a list of questions – I’ve noticed there are a lot of topics in online help which don’t seem to be available (click on them in search results and nothing happens). Many, but not all, of these are marked ADO or DAO. What gives?

I’d also like to find out if there’s anyway that a called routine can stop execution of all code (rather than allowing the calling procedure to continue as in exit sub) and return the focus to the form.

I hope one day to be able to answer at least 25% of the number of questions that I ask.

Reply | Quote

Hi Mark,
I hope Charlotte won’t shoot me for jumping in with this one quick point.

I’ve had problems with the scrolling issue in Access also. Two things to look into. First, check out the GoToPage method of the Form object. With it, you can make Access jump to a certain point on your form just by feeding it the coordinates (X and Y). You can find the specifics in the Access helpfiles. I’ve used it from time to time with great success. If I understood your question (#2)correctly, you could set the GoToPage to run on Exit of your Subform.

Second, I’ve had times when the GoToPage wouldn’t do the trick. It’s a long story, but I used dummy controls (like you mentioned) to set the focus to temporarily. That also worked well. It’s not ideal, but it got the job done.

If anyone has any info on how to control scrolling, maybe with API calls, please let both Marks know

HTH

Reply | Quote

Thanks Mark,

gotopage worked fine for me in this instance.

Reply | Quote

I’m not clear on what this means [indent]

---

As I couldn’t use the BeforeUpdate event because of the number of refreshes forced by conditional comboboxes and subforms, the OnCurrent event was the key.

---

[/indent] How does the beforeupdate event of a form or subform force refreshes and what are “conditional” comboboxes and subforms?

1) As you have discovered, the button’s click event happens before the exit event of the control. However, the afterupdate event of the control will still fire, so you could try using that.

2) This is a matter of how you design your subforms. The simplest method is to design subforms that don’t require scrolling. If they are long subforms but not continuous, you could try putting the controls on a tab control on the subform and hiding its buttons and controlling paging through code. Actually, the “dummy control” method is the standard method to use when you want to line up a subform that is bigger than its window on the parent form. They also work nicely on form detail sections that require scrolling. If you make it the first control in the tab order, it will get the focus automatically when you jump to the subform. Put code in its GotFocus event to set the focus to the first control that can contain data. MarkJ’s post mentions GoToPage, which I used in the days before tab controls, but that is tricky when working with subforms because you actually have to invoke the parent form’s gotopage to make it work. Tab controls are a much cleaner way to do this.

3) This is normal subform behavior. Their size is also constrained by the parent form. I’ve found it better to preset the size of the subform and not frustrate myself by trying to make it grow.

I’ve seen that help behavior in Access 2000, particularly with ADO topics. Sometimes, if you wait long enough the help article finally loads. For specific stuff, I usually go to the object browser, find the object, method or property there and hit F1, which loads the correct article without playing around.

If you ever [indent]

---

… find out if there’s anyway that a called routine can stop execution of all code (rather than allowing the calling procedure to continue as in exit sub) and return the focus to the form.

---

[/indent] don’t use it because it would be very, very bad programming. The rule of thumb is to design your code so that it exits gracefully instead of popping the stack like that. If you just mean that you want to stop that routine, use a conditional statement to test for a value or flag and put the rest of the code in the true portion. That way, if the condition tests false, the subroutine will fall through without executing the contents of the true condition.

Reply | Quote

Thanks Charlotte,

>1) As you have discovered, the button’s click event happens before the exit event of the

>control. However, the afterupdate event of the control will still fire, so you could try

>using that.

Actually, that’s not been my experience at all. All the events I referred to are exit

events, eg if Control1 has the focus, and it has an OnExit procedure of If Control1=1 Then

Me.Control2.SetFocus, when I click my FormClose button while Control1 has the focus and has

value 1, the focus passes to Control2 and the form does not close. I have also confirmed

that the OnClick procedure of the button does not even start to run. Sorry if I had not

made myself clear. I suppose I could try and do things with mouse move and finding out if

the last key was a tab, because most of these OnExit events are just helpfully tabbing over

a lot of secondary questions if the answer to the primary question is Negative, so only

really need to fire when tabbing rather than mousing out of the control. Ho hum.

>How does the beforeupdate event of a form or subform force refreshes and what are

>”conditional” comboboxes and subforms?

Just for information (as my nomenclature is not good), I refer to a conditional combo box on

one that varies the contents of its list dependent on the value of another control. The

obvious instance in my case is that once a village has been entered, the hamlet combo box is

populated with hamlets only in that village. I found I had to refresh the hamlet control on

entry in order for this to work. Similarly the subform seemed to need to be refreshed on

entry in order for it to display the correct data if the master field is changed in the same

record.

In fact, after further research, it is the very act of leaving the main form to enter the

subform that forces the main forms Update event – as this happens before the record is

completed, I could not therefore use the BeforeUpdate event to check that the correct

procedures have been followed.

>don’t use it because it would be very, very bad programming

– thanks, I’m learning. I’m also lazy, however, and I was thinking of the situations where

many procedures call the same subroutine, and I want to stop execution of all the parents

when certain conditions are met in the sub, which would mean testing for that flag in each

parent. Yawn (especially with the limitations of the replace function in VB editor –

suppose I could always cut and paste into a text editor). But point most definitely taken

and I won’t even think about it again.

I’m afraid that your method of accessing help topics resulted in a blank grey pop up screen for me. Boo ho. If this problem is so widespread, why is there nothing in the MSKB about it? The only issues dealt with there are terminal help errors.

Oops, am I being naive again?

Reply | Quote

[indent]

---

In fact, after further research, it is the very act of leaving the main form to enter the subform that forces the main forms Update event – as this happens before the record is completed, I could not therefore use the BeforeUpdate event to check that the correct procedures have been followed

---

[/indent]Entering or leaving a subform always forces a save on the form that is being left. That’s the nature of the beast. However, the main form’s record is completed by definition at this point. If you’re testing values on both the parent form and the subform to determine “completion”, you’re making it hard on yourself. A simpler approach would be to test for completeness in the OnExit event of the subform and not allow the user to move to another record unless everything was completed.

Reply | Quote

You have to set the focus to another control and then disable the command button. How you handle it depends on what happens when you click the command button.

The other method for “disabling” command buttons is to use a module level variable that is set when the button is clicked and not reset until something specific happens or some condition is met. The Current event of the form would check the variable to see whether the button should be enabled or not.

Reply | Quote