MsgBox won't close.

[Kenny G]

Reference the below sub: I can't get the message box to close and
therefore the user can't enter anything - Y or N in the
OralAntibiotics box. Your help is appreciated.

Private Sub OralAntibiotics_Enter()
If Me.TypeofSurgery <> "Colon Surgery" Then
DoCmd.GoToControl "AntibioticAllergy"
Else
MsgBox "Data Entry Is Required - Enter a Y or an N!",
vbCritical, UHS
Me.OralAntibiotics.SetFocus
End If
End Sub

Kenny G

[Rick Brandt]

"Kenny G" <kg****@hotmail.com> wrote in message
news:81**************************@posting.google.c om...

Reference the below sub: I can't get the message box to close and
therefore the user can't enter anything - Y or N in the
OralAntibiotics box. Your help is appreciated.

Private Sub OralAntibiotics_Enter()
If Me.TypeofSurgery <> "Colon Surgery" Then
DoCmd.GoToControl "AntibioticAllergy"
Else
MsgBox "Data Entry Is Required - Enter a Y or an N!",
vbCritical, UHS
Me.OralAntibiotics.SetFocus
End If
End Sub

A MsgBox has to be closed by the user. If you want something that closes
automatically you can use your own form instead.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Stewart Allen]

Remove the second part of the If statement which calls the msgbox call and
the me.oralantibiotics.setfocus.
On the form (if there is some room) use the label control and write "Enter a
Y or N" next to the field, don't use the field's label but create a new
label. Then on the field's OnExit event place the following code to prevent
the user from leaving the field without entering data.

Private Sub OralAntibiotics_Exit(Cancel As Integer)
If Me.TypeOfSurgery = "Colon Surgery" And Me.Dirty = True Then
If Me.OralAntibiotics <> "Y" Or Me.OralAntibiotics <> "N" Then
MsgBox "Data Entry Is Required - Enter a Y or an N!", vbCritical
Cancel = True
End If
End If
End Sub

This checks if the type of surgery is Colon and the form is dirty (that the
current record is being edited). Then it checks if a Y or a N has been
enterd into the field and prompts the user to enter the correct data. The
Cancel = True prevents the user from leaving the field until the correct
data has been entered.

Stewart
"Kenny G" <kg****@hotmail.com> wrote in message
news:81**************************@posting.google.c om...

Reference the below sub: I can't get the message box to close and
therefore the user can't enter anything - Y or N in the
OralAntibiotics box. Your help is appreciated.

Private Sub OralAntibiotics_Enter()
If Me.TypeofSurgery <> "Colon Surgery" Then
DoCmd.GoToControl "AntibioticAllergy"
Else
MsgBox "Data Entry Is Required - Enter a Y or an N!",
vbCritical, UHS
Me.OralAntibiotics.SetFocus
End If
End Sub

Kenny G

[Bob Quintal]

kg****@hotmail.com (Kenny G) wrote in
news:81**************************@posting.google.c om:

Reference the below sub: I can't get the message box to close
and therefore the user can't enter anything - Y or N in the
OralAntibiotics box. Your help is appreciated.

Private Sub OralAntibiotics_Enter()
If Me.TypeofSurgery <> "Colon Surgery" Then
DoCmd.GoToControl "AntibioticAllergy"
Else
MsgBox "Data Entry Is Required - Enter a Y or an N!",
vbCritical, UHS
Me.OralAntibiotics.SetFocus
End If
End Sub

Kenny G

The me.OralAntibiotics.Setfocus is triggering the sub
OralAntibiotics_Enter(), which opens a new messagebox. It happens
so fast that you think that the messagebox never closes.

Bob Q

[Kenny G]

Thanks for your response, the code and your good explaination of that
code seem to make sense. I entered the code and if Colon Surgery is
selected I am still not prompted for a Y or N response in
OralAntibiotics.

Kenny G

"Stewart Allen" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message news:<bs**********@news.wave.co.nz>...

Remove the second part of the If statement which calls the msgbox call and
the me.oralantibiotics.setfocus.
On the form (if there is some room) use the label control and write "Enter a
Y or N" next to the field, don't use the field's label but create a new
label. Then on the field's OnExit event place the following code to prevent
the user from leaving the field without entering data.

Private Sub OralAntibiotics_Exit(Cancel As Integer)
If Me.TypeOfSurgery = "Colon Surgery" And Me.Dirty = True Then
If Me.OralAntibiotics <> "Y" Or Me.OralAntibiotics <> "N" Then
MsgBox "Data Entry Is Required - Enter a Y or an N!", vbCritical
Cancel = True
End If
End If
End Sub

This checks if the type of surgery is Colon and the form is dirty (that the
current record is being edited). Then it checks if a Y or a N has been
enterd into the field and prompts the user to enter the correct data. The
Cancel = True prevents the user from leaving the field until the correct
data has been entered.

Stewart
"Kenny G" <kg****@hotmail.com> wrote in message
news:81**************************@posting.google.c om...

Reference the below sub: I can't get the message box to close and
therefore the user can't enter anything - Y or N in the
OralAntibiotics box. Your help is appreciated.

Private Sub OralAntibiotics_Enter()
If Me.TypeofSurgery <> "Colon Surgery" Then
DoCmd.GoToControl "AntibioticAllergy"
Else
MsgBox "Data Entry Is Required - Enter a Y or an N!",
vbCritical, UHS
Me.OralAntibiotics.SetFocus
End If
End Sub

Kenny G

[Stewart Allen]

Is the "TypeOfSurgery" control on the form a combo box where the rowsource
for the combo box from a another table?
If it is, does each surgery type have its own ID number so its only the
number being stored in the main table so the combo box's recordsouce has 2
columns, SureryTypeID and SurgeryType where the SurgeryTypeID is bound to
the main table with the column widths set at 0cm; 3cm hiding the first
column?

If the control on the form is a combo box pulled from a table as above
change the code to:

If Me.TypeOfSurgery.Column(1) = "Colon Surgery" And Me.Dirty = True Then

This will read the second column of the combo box and not the first.

Please post back if this doesn't work or you're using some other method to
store the data.

Stewart
"Kenny G" <kg****@hotmail.com> wrote in message
news:81**************************@posting.google.c om...

Thanks for your response, the code and your good explaination of that
code seem to make sense. I entered the code and if Colon Surgery is
selected I am still not prompted for a Y or N response in
OralAntibiotics.

Kenny G

[Kenny G]

With regard to your question: Is the "TypeOfSurgery" control on the
form a combo box where the rowsource for the combo box from a another
table?

No, the display control: is a combo box
row source type: Value List
row source: Cardiac Surgery, Colon Surgery, Hip Replacement
etc.
bound column 1
column count 1

What I wanted to accomplish is this: If the TypeofSurgery is Colon
Surgery, I wanted the OralAntibiotics to have either a Y or N in that
field.

If other than Colon Surgery, the cursor would move to the next
appropriate field, that being "AntibioticAllergy".

After some work this weekend here is the answer.

There were two Procedures built:

One on enter:

Private Sub Oral Antibiotics_Enter()
If Me.TypeofSurgery <> "Colon Surgery" Then
DoCmd.GoToControl "AntibioticAllergy"
End If
End Sub

The second on exit:

Private Sub Oral Antibiotics_Exit(Cancel As Integer)
If Me.TypeofSurgery ="Colon Surgery" Then
If IsNull(Me.OralAntibiotics) Then
MsgBox "Data Entry Required - Enter a Y or an N!"
Cancel = True
End If
End If
End Sub

I certainly appreciate everyone's help on this just being able to
discuss this with someone and coming back fresh after awhile made a
lot of difference.
Stewart your code gave me the idea of breaking this action into two
procedures, a special thanks to you as well.

Kenny G


"Stewart Allen" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message news:<bs**********@news.wave.co.nz>...

Is the "TypeOfSurgery" control on the form a combo box where the rowsource
for the combo box from a another table?
If it is, does each surgery type have its own ID number so its only the
number being stored in the main table so the combo box's recordsouce has 2
columns, SureryTypeID and SurgeryType where the SurgeryTypeID is bound to
the main table with the column widths set at 0cm; 3cm hiding the first
column?

If the control on the form is a combo box pulled from a table as above
change the code to:

If Me.TypeOfSurgery.Column(1) = "Colon Surgery" And Me.Dirty = True Then

This will read the second column of the combo box and not the first.

Please post back if this doesn't work or you're using some other method to
store the data.

Stewart
"Kenny G" <kg****@hotmail.com> wrote in message
news:81**************************@posting.google.c om...

Thanks for your response, the code and your good explaination of that
code seem to make sense. I entered the code and if Colon Surgery is
selected I am still not prompted for a Y or N response in
OralAntibiotics.

Kenny G