Forcing entry on if info in 1 out of the 2

[superrob5]

I have several fields on my form. there are 2 fields that need special attention. if one of them has info in it the other one has to have info in it. I know how to check for null then setting focus to the one I want to add info in it. I put this code in the before update on the form. But if the user clicks on the arrow again to change records it by passes the code. It doesnt recheck. Any help?


Rob

 

[AlanS]

Try putting your error checking code in the form's BeforeUpdate event procedure - if the business rule (both fields must be Null or both fields must NOT be Null) is not met, display an appropriate message box, set the focus to one of the controls, and set the procedure's Cancel argument to True.

 

[dcx693]

The problem, I think, is that the Before Update code for the form only fires under certain circumstances. One of these is when a record has been updated but before the changes have been saved. Perhaps the record is being saved somewhere along the line so that Access doesn't fire the event again.

If no one can present a more elegant solution, you may want to work it differently by putting code into the first field so it moves the focus to the second field after someone's made an entry into the first field. Then put code into the second field that does not allow the user to exit the field until a valid entry is made.

 

[AlanS]

Setting up the second control so that the user can't leave it without making an entry could cause other problems. For instance, if the user is simply tabbing through these two fields they'll have to put an entry into the second one even if there is no entry in the first one. Or, they may put an entry in the first one, then change their mind, but still have to go ahead with an entry in the second one. Etc, etc...

Doing the error checking in the BeforeUpdate event avoids all these problems, and insures that a record cannot be created or modified without the business rule being enforced.

If the record is being saved in such a way that the form's BeforeUpdate event does not fire (unusual but not unheard of), then the same code should be executed before that action. An easy way to do this is to set up a Boolean function to test for compliance with the rule - if everything is OK the function returns True; if not, it displays the message box, sets the focus, and returns False. If you're saving a record in code, you can then do it conditioned on this function (If MyFunction Then ...). In the form's BeforeUpdate event, you can just specifiy If Not MyFunction Then Cancel = True.

 

[dcx693]

If superrob can figure out how to get the BeforeUpdate code of the form to work, then that would be, well, super for rob.

As to your point that:

Setting up the second control so that the user can't leave it without making an entry could cause other problems. For instance, if the user is simply tabbing through these two fields they'll have to put an entry into the second one even if there is no entry in the first one. Or, they may put an entry in the first one, then change their mind, but still have to go ahead with an entry in the second one.

My originaly suggestion was:

If no one can present a more elegant solution, you may want to work it differently by putting code into the first field so it moves the focus to the second field after someone's made an entry into the first field . Then put code into the second field that does not allow the user to exit the field until a valid entry is made.

I only wanted to suggest a workaround since superrob seemed to be having a problem getting the BeforeUpdate event to fire properly. We'd all love to program "properly" but sometimes (either because of our own inability or a fault in Access, or even time constraints) we just can't get the programs to run the way we want them to. In those cases, you need to work around the problem even if the solution is not perfect or elegant.

 

[Pat Hartman]

If the record is being saved in such a way that the form's BeforeUpdate event does not fire

--- NOT POSSIBLE ---- The BeforeUpdate event of the form ALWAYS fires if the record is dirty no matter how the form is closed or whenever the current record pointer is moved by the navigation buttons or if the code forces a save as in DoCmd.RunCommand acCmdSaveRecord.

The BeforeUpdate event is the correct event to use for ALL multi-field edits, ie edits that depend on the value of some other field. You can of course use the event for any edit. If there is some error detected, you must cancel the update event with the following statement -
Cancel = True

 

[superrob5]

hey man thanks that works by adding the cancel event. Thanks so much just one more question. Now if the user descided to go back to one of those fields and blank it out. I want it to allow it to save it. Which means I cant test for null. Can I test no empty or blank.. I dont remeber the function. is it isempty()???


Thanks
Rob

 

[Pat Hartman]

The easiest way to handle the problem is to combine the tests:

If Len(Nz(SomeField,"")) = 0 Then
....

 

[superrob5]

the only problem is this doesnt work for dates ex 10/08/1981
if I put a date in then take it out the error message that I have created still comes up.

Rob