I think maybe there's a better way to go about this whole thing. If I
understand correctly, you want to make sure that, if chkWorkComp is true,
various other fields have information before the record is saved.
Generally this is much easier and more elegant through the form's
BeforeUpdate event, rather than trying to catch it in control events. The
form's BeforeUpdate event will always trigger if there has been a change
made... regardless of the record is saved (moving on to the next record,
closing the form, hitting a save button, closing access, everything except a
complete system crash). Furthermore, the form's BeforeUpdate event also
gives you the option to cancel.
So, if there's anything at all you want to make sure of before a record is
saved, the form's BeforeUpdate event is the idea place to check for it.
Assuming you are familiar with error handling and exit points in code, here's
the skeleton of how to effectively use this procedure:
'CODE START
Public Sub Before_Update(Cancel As Integer)
On Error Goto Err_Handler
Dim iCancel As Integer
iCancel = 0
'Put all of your validation code here
'Example:
If Me.chkWorkComp = False Then
MsgBox "Please check Work Comp before saving"
Goto Exit_Code
End If
'...
'...
'...
iCancel = 1
Exit_Code:
If iCancel = 0 Then
Cancel = True
End If
Exit Sub
Err_Handler:
'Typical Error handling code here...
Resume Exit_Code
Resume
End Sub
'CODE END
Here's how it works:
You'll have this iCancel variable, which you set to 0 before doing anything
else (It should default to that as soon as it is dimensioned, but we're not
going to count on it). When we get to the exit point of the sub, if it see's
that iCancel is still 0, its not going to let the save happen.
The first part of the trick here is to NOT set iCancel to 1 (or anything
besides 0) everything else in the sub is confirmed. This line should always
be the last line before before the exit point. The second part of the trick
is to use the Goto Exit_Code line any time a validation fails. That way, it
jumps right past the iCancel = 1 line, and the update is cancelled. (In case
you weren't aware, you should never have more than one exit point in a
procedure).
If you do these two things, the ONLY possible way that this record will
update is if every single validation expression you enter returns the way you
want it to. Even if your code runs into an unexpected error, the record will
not update.
The next problem you are going to run into deals with switching focus
between the main form and subform. I am *almost* positive that whenever you
change the focus from one to the other, whichever form you leave will save
(the BeforeUpdate for that form will run). I say this because it would stand
to reason that when you start a new record in the main form, that record
needs to be saved before you can start entering a related record in the
subform.
Based on this, you will need to make a decision on which form (main or sub)
that you would like to validate information on. *If* this is true, you will
not be able to validate controls on a subform when you are working with a new
record on the main form (the main form BeforeUpdate will fire when you switch
to the subform, and subform control validations will fail because the user
hasn't had a chance to get to the subform to edit those records yet). So,
you may want to use the subform's BeforeUpdate event rather than the
Mainforms (although, this will allow you to save a record in the mainform
without validation on subform data). As far as I know, there is no real way
to get around that.
If, by chance, you do want to validate this on a control-by-control basis,
consider using the control's OnExit event, rather than BeforeUpdate.
--
Jack Leach
www.tristatemachine.com
- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery
