Making a field required based on the value of another field

[thexxvi]

Good morning,

I have a form with the following:

Combobox (1b Answer) - User can only either select 'Yes' or 'No'
Textbox (1b Comments) - User can enter freeform notes
Combobox (Save_or_Submit) - User can only either select 'Save' or 'Submit'
Command Button (CloseButton) - Information is saved to the table


What I want to happen is the following:

Save_or_Submit is a required field. When the user selects 'Save', the information is saved into the table (no required fields). When the user selects 'Submit', 1b Comments is required only when 1b Answer is answered 'Yes'.


This is what I have so far:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Save_or_Submit = "Submit" Then
If Me.[1b Answer] = "Yes" Then
If IsNull(Me.[1b Comments]) Or Me.[1b Comments] = "" Then
MsgBox "You must enter a comment for question 1b."
Cancel = True
Exit Sub
End If
End If
End If
End Sub


Private Sub CloseButton_Click()
If MsgBox("Changes have been made to this record." _
& vbCrLf & vbCrLf & "Do you want to save these changes?" _


, vbYesNo, "Changes Made") = vbYes Then
DoCmd.Save
Else
DoCmd.RunCommand acCmdUndo
End If
DoCmd.Close acForm, "Functional Risk Profile", acSaveNo
End Sub

Please help! I cannot get this to work!

[lfpm062010]

You probably should check the Combobox (Save_or_Submit).

Sub NameOfCombo_afterupdate()
If Me.Save_or_Submit = "Submit" Then
If Me.[1b Answer] = "Yes" Then
If IsNull(Me.[1b Comments]) Or Me.[1b Comments] = "" Then
MsgBox "You must enter a comment for question 1b."
Cancel = True
Exit Sub
End If
End If
End If
End Sub

[thexxvi]

any other away you can think of? I don't want what will be perceived as an error message popping up every time someone selects yes (there are really 37 questions).

[lfpm062010]

Save_or_Submit is a required field. When the user selects 'Save', the information is saved into the table (no required fields). When the user selects 'Submit', 1b Comments is required only when 1b Answer is answered 'Yes'.

The code will check as you stated.

Unless I misunderstood something or I don't understand what you are asking for or what you are trying to do.

[cbende2]

thexxvi, run a forloop, You will want it checking all 37 questions. It will then prompt the users with one(s) they miss. This is good.