Validation And Calculated Fields

[DIY]

Hi Guys,
Can anyone help please?

A) I have 2 tick boxes to capture “AUTHORISED ABSENCE” and “UNAUTHORISED ABSENCE”. I also have an ABSENCE REASON text box.

If AUTHORISED ABSENCE or UNAUTHORISED ABSENCE is selected, then the ABSENCE REASON box must not be empty. The message “Select A Reason” must be displayed, else leave blank

I have tried,
=IIf([AUTHORISED ABSENCE]="-1" Or [UNAUTHORISED ABSENCE]="-1","Select A Reason",""). However this does not work.


B) I have a text box (JOB NUMBER) for storing Job Numbers and a calculated text box (BALANCE), which = 0 when a Job number is selected and the form is completed. This works.
I need to create a validation to say to ensure that BALANCE =0, if a Job Number is selected and the form is completed.

C) On Save, as a final check, I need the following validation.

IF Authorised Absence, Unauthorised Absence or JOB NUMBER is not selected,
Then the message “Check Job” must be displayed as one of the 3 must always be selected.

Any suggestion is welcome.
Many thanks

[PEB]

Hi man,
A)
So in your form you choose the before update property and there choose event procedure

Private Sub Form_BeforeUpdate(Cancel As Integer)
If ((Me![AUTHORISED ABSENCE]=True) or (me![UNAUTHORISED ABSENCE]=True)) AND (isnull(Me![ABSENCE REASON])) Then
msgbox “Select A Reason”
Cancel=True
end if
 
IF me![BALANCE]<>0 Then
Cancel=True
end if
 
IF (Me![Authorised Absence]=false) AND (Me![Unauthorised Absence]=False) AND (Isnull(ME![JOB NUMBER])) Then
Msgbox “Check Job” 
Cancel=true
End Sub
 

Best regards

:)

Hi Guys,
Can anyone help please?

A) I have 2 tick boxes to capture “AUTHORISED ABSENCE” and “UNAUTHORISED ABSENCE”. I also have an ABSENCE REASON text box.

If AUTHORISED ABSENCE or UNAUTHORISED ABSENCE is selected, then the ABSENCE REASON box must not be empty. The message “Select A Reason” must be displayed, else leave blank

I have tried,
=IIf([AUTHORISED ABSENCE]="-1" Or [UNAUTHORISED ABSENCE]="-1","Select A Reason",""). However this does not work.


B) I have a text box (JOB NUMBER) for storing Job Numbers and a calculated text box (BALANCE), which = 0 when a Job number is selected and the form is completed. This works.
I need to create a validation to say to ensure that BALANCE =0, if a Job Number is selected and the form is completed.

C) On Save, as a final check, I need the following validation.

IF Authorised Absence, Unauthorised Absence or JOB NUMBER is not selected,
Then the message “Check Job” must be displayed as one of the 3 must always be selected.

Any suggestion is welcome.
Many thanks

[DIY]

THANKS FOR THAT. SPOT ON.

Hi man,
A)
So in your form you choose the before update property and there choose event procedure

Private Sub Form_BeforeUpdate(Cancel As Integer)
If ((Me![AUTHORISED ABSENCE]=True) or (me![UNAUTHORISED ABSENCE]=True)) AND (isnull(Me![ABSENCE REASON])) Then
msgbox “Select A Reason”
Cancel=True
end if
 
IF me![BALANCE]<>0 Then
Cancel=True
end if
 
IF (Me![Authorised Absence]=false) AND (Me![Unauthorised Absence]=False) AND (Isnull(ME![JOB NUMBER])) Then
Msgbox “Check Job” 
Cancel=true
End Sub
 

Best regards

:)