Making a field required based on the value of another field

[thexxvi]

Hello,

I am trying to do something that I would think is very simple but I'm having a hard time with it. I have a form that consists of questions that the user is needed to answer. It displays the question, a combobox where the User can select 'Yes' or 'No', and a textbox where the user can input their comments. What I would like to do it the following: if the user selects 'Yes' as an answer, I want the corresponding comments box to be required. How can I accomplish this?

Thank you in advance for your assistance.

Cory

[cbende2]

The following code must be used on a form:

You can put this in the Form_BeforeUpdate event.

If Me.cboYesNo="Yes" Then  
    If IsNull(Me.CommentBox) Then
    MsgBox "You must enter in comments."
    Cancel = True
    Me.CommentBox.SetFocus
    End If 
End If

[thexxvi]

I'll give that a shot and check back. Thank you!

[cbende2]

You're welcome.

You may need to try this

If Me.cboYesNo.Column(1) = "Yes"

or

If Me.cboYesNo.Column(2) = "Yes"

depending on how your combo box is set up.

[thexxvi]

the first comment worked well...now I run into another small problem.

So, like I said, there are a number of questions that a user needs to fill out so I know that I will have to duplicate the code over and over again (obviously changing the name of the combobox/comments box for each question). From a user standpoint, I'm trying to make this process fluid...so I'd like to eliminate as many MS Access error messages from popping up in case the user triggers one.


The following is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.cbo1bAnswer = "Yes" Then
If IsNull(Me.tbo1bComments) Then
MsgBox "You must enter comments for Question 1b."
Cancel = True
Me.tbo1bComments.SetFocus
End If
Exit Sub
End If

If Me.cbo1cAnswer = "Yes" Then
If IsNull(Me.tbo1cComments) Then
MsgBox "You must enter comments for Question 1c."
Cancel = True
Me.tbo1bComments.SetFocus
End If
Exit Sub
End If

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
End Sub


What I want is for the form to go down the list and make sure all questions with a 'Yes' have comments associated with them. If not, I want the code to stop running, generate the error message, then require the user to enter comments before continuing. If all of the questions are completely answered, I want a prompt to pop that asks them if they want to save their work. That all seems to be functioning correctly. The problem that I'm coming across is when the user hits the submit button to save their record and a question is answered 'Yes' with no comments associated, it generates the appropriate error message saying that they need to enter comments, but then it populates another error message 3021 asking if they want to stop all macros. Is there any way to suppress the system generated error messages?

[cbende2]

Let me know if this works:

If Err.Number =3021 Then
    ' Do nothing. We dont care about this error '
    Err.Clear
End If

[thexxvi]

negative - still getting the error

[cbende2]

What else does the error say?

Does it only say "Do you want to stop all macros?"

[thexxvi]

The header is 'Macro Single Step'

Macro Name is listed

Error Number: 3021

Arguments: 97

The only available button to press is 'Stop All Macros'

[thexxvi]

So it's associated with a different code that I had as a macro. I moved the macro to vba so I can edit it there. It's saying Error 3021 is "No Current Record". I'll work through it.

Thank you for all of your help to this point.

[cbende2]

Glad I could help. Feel free to make a new post if you're still having trouble. I'm not too experienced with error handling etc..