Option group criteria

[jadabug28]

Hi all,

I have a subform with 7 option groups (with check boxes), and a text
box for comments at the bottom. I need the txtcomments to be a
required field IF any "no" is checked for any of the option groups. If
an attempt is made to close the form without adding a comment (and one
or more of the check boxes is checked "no"), I want a message to
display "Please enter a comment...". Please help!

 

[Jeff L]

You would need to check the value of each option group and then if one
is No, display your message. An option group usually assigns a number
to each value, say 1 for Yes and 2 for No. Put this in the On Unload
property of the form.

If Me.OptionGroup1 = 2 OR Me.OptionGroup2 = 2 OR Me.OptionGroup3 = 2 OR
Me.OptionGroup4 = 2 OR Me.OptionGroup5 = 2 OR Me.OptionGroup6 = 2 OR
Me.OptionGroup2 = 2 Then

Msgbox "You need to enter comments", vbOKonly
Cancel = True
End IF

 

[jadabug28]

You would need to check the value of each option group and then if one
is No, display your message. An option group usually assigns a number
to each value, say 1 for Yes and 2 for No. Put this in the On Unload
property of the form.

If Me.OptionGroup1 = 2 OR Me.OptionGroup2 = 2 OR Me.OptionGroup3 = 2 OR
Me.OptionGroup4 = 2 OR Me.OptionGroup5 = 2 OR Me.OptionGroup6 = 2 OR
Me.OptionGroup2 = 2 Then

Msgbox "You need to enter comments", vbOKonly
Cancel = True
End IF

 

[jadabug28]

You would need to check the value of each option group and then if one
is No, display your message. An option group usually assigns a number
to each value, say 1 for Yes and 2 for No. Put this in the On Unload
property of the form.

If Me.OptionGroup1 = 2 OR Me.OptionGroup2 = 2 OR Me.OptionGroup3 = 2 OR
Me.OptionGroup4 = 2 OR Me.OptionGroup5 = 2 OR Me.OptionGroup6 = 2 OR
Me.OptionGroup2 = 2 Then

Msgbox "You need to enter comments", vbOKonly
Cancel = True
End IF

Maybe I should have mentioned the option groups are in a subform. I
entered the above in the On Unload property of the main form, but upon
closing the form I got this error: Compile error: method or data
member not found
"Private Sub Form_Unload(Cancel As Integer)" is highlighted in yellow

I'm new at this, I have a huge Access book in front of me, and have
been googling like crazy. Thanks for your help!

 

[Jeff L]

Yes, having the option groups in a subform definately makes a
difference. In that case your syntax would be
Forms!MainFormName!SubFormName!OptionGroupName instead of
Me.OptionGroupName. Substitute the Names for whatever you have called
them.

 

[jadabug28]

Yes, having the option groups in a subform definately makes a
difference. In that case your syntax would be
Forms!MainFormName!SubFormName!OptionGroupName instead of
Me.OptionGroupName. Substitute the Names for whatever you have called
them.

Okay, I changed the syntax in all cases, and it is still located in the
main form's On Unload action. Now, I am not getting an error message,
in fact, nothing at all seems to be happening pertaining to this If
statement. The msgbox never pops up, and the Comments text box is
allowed to remain blank. Sorry for the confusion, please continue to
help! This is the exact code: Private Sub Form_Unload(Cancel As
Integer)
If Forms![All BC]![IEA Review Forms]![Frame130] = 0 Or Forms![All
BC]![IEA Review Forms]![Frame144] = 0 Or Forms![All BC]![IEA Review
Forms]![Frame151] = 0 Or Forms![All BC]![IEA Review Forms]![Frame158] =
0 Or Forms![All BC]![IEA Review Forms]![Frame165] = 0 Or Forms![All
BC]![IEA Review Forms]![Frame172] = 0 Or Forms![All BC]![IEA Review
Forms]![Frame179] = 0 Then

MsgBox "Please enter comments indicating all problems with this
procedure.", vbOKOnly
Cancel = True
End If
End Sub

 

[jadabug28]

Yes, having the option groups in a subform definately makes a
difference. In that case your syntax would be
Forms!MainFormName!SubFormName!OptionGroupName instead of
Me.OptionGroupName. Substitute the Names for whatever you have called
them.

Okay, I changed the syntax in all cases, and it is still located in the
main form's On Unload action. Now, I am not getting an error message,
in fact, nothing at all seems to be happening pertaining to this If
statement. The msgbox never pops up, and the Comments text box is
allowed to remain blank. Sorry for the confusion, please continue to
help! This is the exact code: Private Sub Form_Unload(Cancel As
Integer)
If Forms![All BC]![IEA Review Forms]![Frame130] = 0 Or Forms![All
BC]![IEA Review Forms]![Frame144] = 0 Or Forms![All BC]![IEA Review
Forms]![Frame151] = 0 Or Forms![All BC]![IEA Review Forms]![Frame158] =
0 Or Forms![All BC]![IEA Review Forms]![Frame165] = 0 Or Forms![All
BC]![IEA Review Forms]![Frame172] = 0 Or Forms![All BC]![IEA Review
Forms]![Frame179] = 0 Then

MsgBox "Please enter comments indicating all problems with this
procedure.", vbOKOnly
Cancel = True
End If
End Sub

0, no; -1, yes

 

[Jeff L]

Are you selecting No for an option and are you certain that the No
value is 0 on all your Option Groups? Please check that just to be
sure. I have tested what I told you and it does work. I can't see
anything wrong with the code itself.

 

[jadabug28]

Are you selecting No for an option and are you certain that the No
value is 0 on all your Option Groups? Please check that just to be
sure. I have tested what I told you and it does work. I can't see
anything wrong with the code itself.

I am selecting No for an option, and No=0 in all option groups. I'm
missing something...

 

[Edward Reid]

Use basic debugging techniques: before the IF, write a series of MSGBOX
statements to display all the values (one at a time) that you are going
to use in the IF. Most likely that will isolate the location of the
problem.

Edward

 

[jadabug28]

Use basic debugging techniques: before the IF, write a series of MSGBOX
statements to display all the values (one at a time) that you are going
to use in the IF. Most likely that will isolate the location of the
problem.

Edward

I'm getting really close now, all msgbox work properly, problem: if the
comments box is not null (or if any of the option groups, me.steps,
etc, are -1), I want the form to close and open the form Start Up.

Private Sub Command219_Click()
'Error handling not included
Select Case MsgBox("Would you like to save this review?", _
vbYesNoCancel + vbQuestion, "Save Review?")
Case vbNo 'User does not want to save changes
Me.Undo
Case vbCancel 'User Cancelled the action
Exit Sub
Case Else 'Must have said "Yes"
'Just let it save and close
End Select
If Me.Steps = 0 Or Me.Equip = 0 Or Me.Consequences = 0 Or Me.SHE =
0 Or Me.PPE = 0 Or Me.Format = 0 Or Me.Grammar = 0 Then
If IsNull(Me.Comments) Then MsgBox "Please enter comments in the
bottom of this form indicating all problems found with procedure."
Me.Comments.SetFocus
ElseIf Not IsNull(Me.Comments) Then
DoCmd.Close
DoCmd.OpenForm "Start Up"
Exit Sub
End If
End Sub

 

[Jeff L]

If IsNull(Me.Comments) Then
MsgBox "Please enter comments in the bottom of this form indicating all
problems found with procedure."
Me.Comments.SetFocus
Else
If Not IsNull(Me.Comments) Then (this line is not needed)
DoCmd.OpenForm "Start Up"
DoCmd.Close acForm, "FormNameYouWantToClose"
Exit Sub (This is not needed either)
End If
End Sub

 

[jadabug28]

If IsNull(Me.Comments) Then
MsgBox "Please enter comments in the bottom of this form indicating all
problems found with procedure."
Me.Comments.SetFocus
Else
If Not IsNull(Me.Comments) Then (this line is not needed)
DoCmd.OpenForm "Start Up"
DoCmd.Close acForm, "FormNameYouWantToClose"
Exit Sub (This is not needed either)
End If
End Sub

Thank you for your help...
If I select "No" for one and leave the Comments box blank, the first
Msgbox comes up asking if I want to save; if I click yes, another
Msgbox asks me to leave comments and sets focus on comments box (just
like I want it to). At this time, If I type comments in and click
close (Command219) again, it asks if I want to save, I click yes, and
it sets focus back to comments (no msgbox comes up). If comments are
in the field, I want it to go ahead and close. I hope this makes
sense...

 

[Jeff L]

To do what you described:

If Me.Steps = 0 Or Me.Equip = 0 Or Me.Consequences = 0 Or Me.SHE =
0 Or Me.PPE = 0 Or Me.Format = 0 Or Me.Grammar = 0 Then
If IsNull(Me.Comments) Then MsgBox "Please enter comments in the
bottom of this
form indicating all problems found with procedure."
Me.Comments.SetFocus
Exit Sub 'We don't want to close the form because we need to
add Comments
End If 'End the IsNull If Statement
End IF 'End the If Statement with all the Or's
DoCmd.OpenForm "Start Up"
DoCmd.Close
End Sub

 

[jadabug28]

To do what you described:

If Me.Steps = 0 Or Me.Equip = 0 Or Me.Consequences = 0 Or Me.SHE =
0 Or Me.PPE = 0 Or Me.Format = 0 Or Me.Grammar = 0 Then
If IsNull(Me.Comments) Then MsgBox "Please enter comments in the
bottom of this
form indicating all problems found with procedure."
Me.Comments.SetFocus
Exit Sub 'We don't want to close the form because we need to
add Comments
End If 'End the IsNull If Statement
End IF 'End the If Statement with all the Or's
DoCmd.OpenForm "Start Up"
DoCmd.Close
End Sub

With this I get Compile Error: End If without block If
Private Sub Command219_Click()
'Error handling not included
Select Case MsgBox("Would you like to save this review?", _
vbYesNoCancel + vbQuestion, "Save Review?")
Case vbNo 'User does not want to save changes
Me.Undo
Case vbCancel 'User Cancelled the action
Exit Sub
Case Else 'Must have said "Yes"
'Just let it save and close
End Select
If Me.Steps = 0 Or Me.Equip = 0 Or Me.Consequences = 0 Or Me.SHE =
0 Or Me.PPE = 0 Or Me.Format = 0 Or Me.Grammar = 0 Then
If IsNull(Me.Comments) Then MsgBox "Please enter comments in the
bottom of this form indicating all problems found with procedure."
Me.Comments.SetFocus
Exit Sub 'We don't want to close the form because we need to
add comments
End If 'End the IsNull If Statement
End If 'End the If Statement with all the Or's
DoCmd.OpenForm "Start Up"
DoCmd.Close
End Sub

Private Sub line is highlighted in yellow, and the last End If
statement is indicated. I tried using a variation of this by placing
the entire If criteria in an Unload event of the subform, but this also
failed. Thanks again for your help...

 

[Edward Reid]

My experience with VB errors is small, but I've found that the error
has been detected at the line highlighted in yellow -- which means the
error is in that line OR ABOVE. I don't know how you mean the last End
If is "indicated".

In general, this message will mean that the compiler did not find the
IF, so look for reasons that the IF might have been hidden, such as a
stray apostrophe.

The general approach to difficult syntax errors is to reduce the
procedure to just the SUB and END SUB, and then add statements back one
at a time.

Also, it's a good idea to change the name of the command button from
the default Command219 to something meaningful. Not necessary, but it
helps you keep track of what's going on.

Edward

 

[Jeff L]

Trying again:
If (Me.Steps = 0 Or Me.Equip = 0 Or Me.Consequences = 0 Or Me.SHE =
0 Or Me.PPE = 0 Or Me.Format = 0 Or Me.Grammar = 0) AND
IsNull(Me.Comments) Then
MsgBox "Please enter comments in the bottom of this form
indicating all problems found with procedure."
Me.Comments.SetFocus
Exit Sub 'We don't want to close the form because we need to add
Comments
End IF
DoCmd.OpenForm "Start Up"
DoCmd.Close acform, "MainFormName"
End Sub

 

[jadabug28]

Trying again:
If (Me.Steps = 0 Or Me.Equip = 0 Or Me.Consequences = 0 Or Me.SHE =
0 Or Me.PPE = 0 Or Me.Format = 0 Or Me.Grammar = 0) AND
IsNull(Me.Comments) Then
MsgBox "Please enter comments in the bottom of this form
indicating all problems found with procedure."
Me.Comments.SetFocus
Exit Sub 'We don't want to close the form because we need to add
Comments
End IF
DoCmd.OpenForm "Start Up"
DoCmd.Close acform, "MainFormName"
End Sub

Private Sub CloseSave_Click()
If Me.Steps = 0 Or Me.Equip = 0 Or Me.Consequences = 0 Or Me.SHE = 0 Or
Me.PPE = 0 Or Me.Format = 0 Or Me.Grammar = 0 Then
If IsNull(Me.Comments) Then
MsgBox "Please enter comments indicating all problems with this
procedure."
Me.Comments.SetFocus
DoCmd.CancelEvent
ElseIf Me.Steps = 0 Or Me.Equip = 0 Or Me.Consequences = 0 Or Me.SHE =
0 Or Me.PPE = 0 Or Me.Format = 0 Or Me.Grammar = 0 Then
If Not IsNull(Me.Comments) Then
Select Case MsgBox("Would you like to save this review?", _
vbYesNoCancel + vbQuestion, "Save Review?")
Case vbNo 'User does not want to save changes
Me.Undo
Case vbCancel 'User Cancelled the action
Exit Sub
Case Else 'Must have said "Yes"
'Just let it save and close
End Select
DoCmd.Close
ElseIf Me.Steps =-1 And Me.Equip =-1 And Me.Consequences =-1 And Me.SHE
=-1 And Me.PPE =-1 And Me.Format =-1 And Me.Grammar =-1 Then
Select Case MsgBox("Would you like to save this review?", _
vbYesNoCancel + vbQuestion, "Save Review?")
Case vbNo 'User does not want to save changes
Me.Undo
Case vbCancel 'User Cancelled the action
Exit Sub
Case Else 'Must have said "Yes"
'Just let it save and close
End Select

DoCmd.Close
End If
End If
End If
End Sub

Okay, this works for all Ifs except the last one. -1 means yes, I
tried that and =<0, neither the msgbox nor the close action worked.
Any ideas? Thanks again for all of your help.

 

[Jeff L]

The last If statement will only execute if ALL your checkboxes are
checked. Is that what you want?

 

[jadabug28]

The last If statement will only execute if ALL your checkboxes are
checked. Is that what you want?

Yes, please