Option group criteria

[Jeff L]

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
Else
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
If 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 Sub

 

[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!

Now all yes works, but with one or more no I get run-time error 2447 -
There is an invalid use of the .(dot) or ! operator or invalid
parenthesis. This error appears after the two msgboxes. I have looked
it over, and even tried changing the .(dots) to ! (didn't work). So
close! Thank you...

 

[Edward Reid]

Again, I'm a neophyte in this area and guessing again. Searching the
newsgroup, I find that a wide variety of conditions can cause a 2447
error, despite the narrow description in the message text.

At an earlier stage you had this code on the Form_Unload event, but at
some point it got moved to a click on a command button. This changes
what you have to do -- in the first case you either allowed it to
continue or explicitly canceled, but when you're using a command button
to close and save, you either explicitly close (as you've done) or do
nothing and leave the form as it is.

I'm suspicious of your DoCmd.CancelEvent call. The documentation for
CancelEvent says that "all events that can be cancelled in VB have a
Cancel argument". Your event procedure doesn't, and that makes sense --
logically you can't cancel a click. Why are you using CancelEvent
rather than Exit Sub? I find it conceivable that the CancelEvent is
somehow leaving the form in a state where the Undo is considered
invalid.

Edward

 

[Jeff L]

Yes, I think I would use the Exit Sub too.

 

[jadabug28]

Again, I'm a neophyte in this area and guessing again. Searching the
newsgroup, I find that a wide variety of conditions can cause a 2447
error, despite the narrow description in the message text.

At an earlier stage you had this code on the Form_Unload event, but at
some point it got moved to a click on a command button. This changes
what you have to do -- in the first case you either allowed it to
continue or explicitly canceled, but when you're using a command button
to close and save, you either explicitly close (as you've done) or do
nothing and leave the form as it is.

I'm suspicious of your DoCmd.CancelEvent call. The documentation for
CancelEvent says that "all events that can be cancelled in VB have a
Cancel argument". Your event procedure doesn't, and that makes sense --
logically you can't cancel a click. Why are you using CancelEvent
rather than Exit Sub? I find it conceivable that the CancelEvent is
somehow leaving the form in a state where the Undo is considered
invalid.

Edward

You're right, that makes sense. I changed the CancelEvent to Exit Sub.
If I select all yes in my form, everything works fine. One no and I
get the 2447 error message. I am so frustrated with this...

 

[Edward Reid]

If I select all yes in my form, everything works fine. One no and I
get the 2447 error message.

After clicking which choice? In other words, is it failing on the
Me.Undo or on the DoCmd.Close?

I see that the default for the third argument to DoCmd.Close is
acSavePrompt -- prompt the user whether to save. Since you are already
in a close event procedure, I would guess that this might well be
invalid. I'd try specifying the third argument as acSaveNo or
acSaveYes. Probably you'll want to write two DoCmd.Close statement, in
the two applicable branches of the Select statement. (There are other
ways, but this would be the clearest.) Note that once you've done this,
you no longer need the last Exit Sub, since it will fall out the end
without doing anything more anyway.

Edward

 

[jadabug28]

After clicking which choice? In other words, is it failing on the
Me.Undo or on the DoCmd.Close?

I see that the default for the third argument to DoCmd.Close is
acSavePrompt -- prompt the user whether to save. Since you are already
in a close event procedure, I would guess that this might well be
invalid. I'd try specifying the third argument as acSaveNo or
acSaveYes. Probably you'll want to write two DoCmd.Close statement, in
the two applicable branches of the Select statement. (There are other
ways, but this would be the clearest.) Note that once you've done this,
you no longer need the last Exit Sub, since it will fall out the end
without doing anything more anyway.

Edward

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
Else
Select Case MsgBox("Would you like to save this review?", _
vbYesNoCancel + vbQuestion, "Save Review?")
Case vbNo 'User does not want to save changes
DoCmd.Close
Case vbCancel 'User Cancelled the action
Me.Undo
Case Else 'Must have said "Yes"
'Just let it save and close
End Select
DoCmd.Close
End IF
End IF
If 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
DoCmd.Close
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 Sub




This doesn't work either, if me.steps, etc. are all -1 and I click the
close button, nothing happens. But if any of the me.steps, etc. are 0
and I click the close button, the 2 msgbox appear as needed, and it
closes as desired. It seems something is amiss with the "yes" criteria
in my code. Once again, I am extremely new at this, so acSaveNo and
acSaveYes are beyond my understanding. If you have time to explain, I
would be forever grateful! (This is a long post, huh?)

 

[Edward Reid]

jadabug,

When you're in the VB editor, do View->Object Browser. In the top
popup, pick Access. Go down the big list to DoCmd. Click on it. In the
right panel, click on Close. Then click the yellow help button (or
right-click on Close, Help). This leads you to the explanation of the
parameters. You really can't get anywhere with VB/Access without using
this reference.

Also, when you are typing the call, the VB editor will show a popup
summarizing the parameters.

I see the code you posted still contains the CancelEvent call. I still
recommend replacing it with an Exit Sub.

Also, you did put a docmd.close in place of the me.undo. However, you
left the existing docmd.close where it is, so the code is going to try
to execute the close twice. You need to move the close that's after the
end select into the last branch of the select.

Edward

 

[jadabug28]

jadabug,

When you're in the VB editor, do View->Object Browser. In the top
popup, pick Access. Go down the big list to DoCmd. Click on it. In the
right panel, click on Close. Then click the yellow help button (or
right-click on Close, Help). This leads you to the explanation of the
parameters. You really can't get anywhere with VB/Access without using
this reference.

Also, when you are typing the call, the VB editor will show a popup
summarizing the parameters.

I see the code you posted still contains the CancelEvent call. I still
recommend replacing it with an Exit Sub.

Also, you did put a docmd.close in place of the me.undo. However, you
left the existing docmd.close where it is, so the code is going to try
to execute the close twice. You need to move the close that's after the
end select into the last branch of the select.

Edward

Oh my goodness, we did it! I turned the code around:

Private Sub CloseSave_Click()
If 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 Or 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
DoCmd.Close
Case vbCancel 'User Cancelled the action
Me.Undo
Case Else 'Must have said "Yes"
'Just let it save and close
DoCmd.Close
End Select
Else
MsgBox "Please enter comments indicating all problems with this
procedure."
Me.Comments.SetFocus
Exit Sub
End If

End Sub

woohoo! Thanks for all your help, I have learned so much already from
this group.

 

[Edward Reid]

Great! That's MUCH better code now.

Just a couple of comments on it now.

First, if I'm reading the docs correctly, the Me.Undo will reset the
form to its original state, rather than just canceling the close and
save. Most users would not expect this; rather, they would expect
canceling to leave everything as it was just before the action which
they canceled. So if it were me, I'd eliminate the Me.Undo. However,
with the caveat above, it will work.

Second, the remaining Exit Sub is now superfluous and can be deleted.
Generally it's easier to follow code which conforms to the "single
exit" convention, because you always know that the execution flows out
the end of every program construct. Again, it works fine as you have
it, just would be cleaner without the superfluous Exit Sub.

Congratulations!

Edward