Conditionally Required Fields

[HawkGuru]

The database I am designing in Access 2007 requires a form which shows if an issue is open or closed. This is accomplished by the use of a check box. I have set the required fields as required when opening the issue, but I would like to set the required fields to be required based on whether or not the closed check box is checked. I was able to get it to do this on new records, but the code does not function on existing records. Any help with this problem that anyone can provide will be greatly appreciated. The code I have on the BeforUpdate Event for the Closed check box is:

If Form!Closed = True Then
If Form!SupportProvided <> vbNull Then


Else
MsgBox "The Support Provided Field Is Blank. All Green Fields Are Required.", vbOKOnly
If Form!RepairDescriptionWorkPerformed <> vbNull Then
Else
MsgBox "The Repair Description / Work Performed Field Is Blank. All Green Fields Are Required.", vbOKOnly
If Form!Tech1ManHours <> vbNull Then
Else
MsgBox "The Tech #1 Man Hours Field Is Blank. All Green Fields Are Required.", vbOKOnly
If Form!DateWorkFinished <> vbNull Then
Else
MsgBox "The Date Work Finished Field Is Blank. All Green Fields Are Required.", vbOKOnly
If Form!SystemDownTime <> vbNull Then
Else
MsgBox "The System Down Time Field Is Blank. All Green Fields Are Required.", vbOKOnly
Cancel = True
End If
End If
End If
End If
End If
End If
End Sub

[June7]

Why doesn't it function - error message, wrong results, nothing happens? Have you step debugged? If you need to learn debugging techniques, check the link at bottom of my post.

I suspect you need to use a different event, such as the AfterUpdate.

Are there records that have the checkbox marked closed but the required fields were not completed?

[HawkGuru]

June7,
I do not receive any error message, it just does nothing. I tried it on the AfterUpdate event and received the same result. Yes, the required fields are not completed and the checkbox is still marked as closed.

[June7]

Yes, on second thought the AfterUpdate would not work. I think need to also place this code in Close event of the form. So that if user tries to exit without completing required fields, close can be aborted and user returned to finish entries. Sometimes is necessary to have code in more than one place in order to get the desired behavior under any conditions. Put the code in a sub behind the form that can be called by any event.

Another issue might be the use of vbNull. I have never used it. I use:

If IsNull(Form!SupportProvided) Then

If Not IsNull(Form!RepairDescriptionWorkPerformed) Then

Review this article http://allenbrowne.com/casu-11.html

[HawkGuru]

June7,
Thanks for your help with this. I removed the vbNull that I was using and wrote everything the way you suggested. Now when the checkbox is checked, the messages for each required field pop up regardless of whether the field is null or contains an entry. It also will not allow the checkbox to remain checked. The BeforeUpdate code now looks like:

If Form!Closed = True Then
If IsNull(Form!SupportProvided) Then
Else
MsgBox "The Support Provided Field Is Blank. All Green Fields Are Required.", vbOKOnly
If IsNull(Form!RepairDescriptionWorkPerformed) Then
Else
MsgBox "The Repair Description / Work Performed Field Is Blank. All Green Fields Are Required.", vbOKOnly
If IsNull(Form!Tech1ManHours) Then
Else
MsgBox "The Tech #1 Man Hours Field Is Blank. All Green Fields Are Required.", vbOKOnly
If IsNull(Form!DateWorkFinished) Then
Else
MsgBox "The Date Work Finished Field Is Blank. All Green Fields Are Required.", vbOKOnly
If IsNull(Form!SystemDownTime) Then
Else
MsgBox "The System Down Time Field Is Blank. All Green Fields Are Required.", vbOKOnly
Cancel = True
End If
End If
End If
End If
End If
End If
End Sub

[June7]

If Form!Closed = True Then
    If IsNull(Form!SupportProvided) Then
        MsgBox "The Support Provided Field Is Blank. All Green Fields Are Required.", vbOKOnly
    ElseIf IsNull(Form!RepairDescriptionWorkPerformed) Then
        MsgBox "The Repair Description / Work Performed Field Is Blank. All Green Fields Are Required.", vbOKOnly
    ElseIf IsNull(Form!Tech1ManHours) Then
        MsgBox "The Tech #1 Man Hours Field Is Blank. All Green Fields Are Required.", vbOKOnly
    ElseIf IsNull(Form!DateWorkFinished) Then
        MsgBox "The Date Work Finished Field Is Blank. All Green Fields Are Required.", vbOKOnly
    ElseIf IsNull(Form!SystemDownTime) Then
        MsgBox "The System Down Time Field Is Blank. All Green Fields Are Required.", vbOKOnly
End If

[HawkGuru]

Thanks June7! Your code worked like a charm and makes MUCH more sense then mine!!