MsgBox Question

K

knowshowrosegrows

Can someone tell me why the following code does not generate a message box
when Agency_ID is 999 or 888 and the EventDescription field is empty?
Agency_ID is a number field and EventDescription is a memo field.

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And [EventDescription] = "") _
Or ([Agency_ID] = "888" And [EventDescription] = "") Then
MsgBox "When choosing "Other, Agency - Describe" or "Other, Sober
House - Describe", you must fill out the Description field.", vbOKOnly
End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
 
D

Daniel Pineault

Since they are numeric rather than textual strings, I do not believ you need
to place your values in quotations.

....
If ([Agency_ID] = 999 And [EventDescription] = "") _
Or ([Agency_ID] = 888 And [EventDescription] = "") Then
....

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
K

knowshowrosegrows

Thanks for your quick reply. Taking the quotes off did not make the message
box appear.
--
Thanks

You all are teaching me so much


Daniel Pineault said:
Since they are numeric rather than textual strings, I do not believ you need
to place your values in quotations.

...
If ([Agency_ID] = 999 And [EventDescription] = "") _
Or ([Agency_ID] = 888 And [EventDescription] = "") Then
...

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



knowshowrosegrows said:
Can someone tell me why the following code does not generate a message box
when Agency_ID is 999 or 888 and the EventDescription field is empty?
Agency_ID is a number field and EventDescription is a memo field.

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And [EventDescription] = "") _
Or ([Agency_ID] = "888" And [EventDescription] = "") Then
MsgBox "When choosing "Other, Agency - Describe" or "Other, Sober
House - Describe", you must fill out the Description field.", vbOKOnly
End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Empty can mean NULL or empty string, so change the code to this:


If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then
 
V

vtj

Just a thought but [EventDescription] = "" may not cover the possibilities.
It may be null as opposed to an empty string. You could try also checking
for isnull.

knowshowrosegrows said:
Thanks for your quick reply. Taking the quotes off did not make the message
box appear.
--
Thanks

You all are teaching me so much


Daniel Pineault said:
Since they are numeric rather than textual strings, I do not believ you need
to place your values in quotations.

...
If ([Agency_ID] = 999 And [EventDescription] = "") _
Or ([Agency_ID] = 888 And [EventDescription] = "") Then
...

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



knowshowrosegrows said:
Can someone tell me why the following code does not generate a message box
when Agency_ID is 999 or 888 and the EventDescription field is empty?
Agency_ID is a number field and EventDescription is a memo field.

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And [EventDescription] = "") _
Or ([Agency_ID] = "888" And [EventDescription] = "") Then
MsgBox "When choosing "Other, Agency - Describe" or "Other, Sober
House - Describe", you must fill out the Description field.", vbOKOnly
End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
K

knowshowrosegrows

You guys are great. That works for the message box.

Now I am trying to get the focus to go to the EventDescription control and
no new record to appear. Can someone help tweek the following edit?

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then

MsgBox "When choosing Other Agency - Describe or Other Sober House
- Describe, you must fill out the Description field.", vbOKOnly
Forms!frmEvents!EventDescription.SetFocus
End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
--
Thanks

You all are teaching me so much


Ken Snell (MVP) said:
Empty can mean NULL or empty string, so change the code to this:


If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



knowshowrosegrows said:
Can someone tell me why the following code does not generate a message box
when Agency_ID is 999 or 888 and the EventDescription field is empty?
Agency_ID is a number field and EventDescription is a memo field.

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And [EventDescription] = "") _
Or ([Agency_ID] = "888" And [EventDescription] = "") Then
MsgBox "When choosing "Other, Agency - Describe" or "Other, Sober
House - Describe", you must fill out the Description field.", vbOKOnly
End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
Click to expand...
Click to expand...
 
J

John Spencer

If ([Agency_ID] = "999" Or Agency_ID = "888") _
And Len([EventDescription] & vbnullstring)= 0) Then
MsgBox ...

Len([EventDescription] & vbnullstring)= 0) tests for eventDescription
being a zero length string or a null value.

And you can combine the two checks for agency number in one clause if
you uses parentheses to group them together.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
J

Jim Burke in Novi

I take it that you're saying whenever the msgbox is displayed you want those
two thing done as well. You already have the SetFocus there for the
EventDescription field, but I have a feeling that you're always going to the
new record even when you don't want it based on what I see and what you're
describing. Just add an Exit Sub statement right after the SetFocus.

Exit sub

knowshowrosegrows said:
You guys are great. That works for the message box.

Now I am trying to get the focus to go to the EventDescription control and
no new record to appear. Can someone help tweek the following edit?

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then

MsgBox "When choosing Other Agency - Describe or Other Sober House
- Describe, you must fill out the Description field.", vbOKOnly
Forms!frmEvents!EventDescription.SetFocus
End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
--
Thanks

You all are teaching me so much


Ken Snell (MVP) said:
Empty can mean NULL or empty string, so change the code to this:


If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



knowshowrosegrows said:
Can someone tell me why the following code does not generate a message box
when Agency_ID is 999 or 888 and the EventDescription field is empty?
Agency_ID is a number field and EventDescription is a memo field.

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And [EventDescription] = "") _
Or ([Agency_ID] = "888" And [EventDescription] = "") Then
MsgBox "When choosing "Other, Agency - Describe" or "Other, Sober
House - Describe", you must fill out the Description field.", vbOKOnly
End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
K

knowshowrosegrows

Thanks for the reply.

So I added the Exit Sib after the SetFocus and now this is what I get.

When I fill out the form and put in Agency_ID 999 and leave the Description
control blank and then press the Submit Entry button, I get the message box
as I had hoped. When I press the OK button on the message box I get another
message box that reads "Object doesnt support this property or method." When
I press that OK button I go back to the form with the record I had been
inputting and my focus is not in the Description field.

What I want to happen when I enter Agency_ID 999 and leave Description blank
and press the Submit Entry button is the message box. When I press ok I want
focus to be in Description. When I have Agency_ID at 999 and the Description
control filled and I push the Submit Entry button, I want a nice new blank
record form.

My current code is as follows:

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then

MsgBox "When choosing Other, Agency - Describe or Other, Sober
House - Describe, you must fill out the Description field.", vbOKOnly
End If
Forms!frmEvents!EventDescription.SetFocus
Exit Sub

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
--
Thanks

You all are teaching me so much


Jim Burke in Novi said:
I take it that you're saying whenever the msgbox is displayed you want those
two thing done as well. You already have the SetFocus there for the
EventDescription field, but I have a feeling that you're always going to the
new record even when you don't want it based on what I see and what you're
describing. Just add an Exit Sub statement right after the SetFocus.

Exit sub

knowshowrosegrows said:
You guys are great. That works for the message box.

Now I am trying to get the focus to go to the EventDescription control and
no new record to appear. Can someone help tweek the following edit?

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then

MsgBox "When choosing Other Agency - Describe or Other Sober House
- Describe, you must fill out the Description field.", vbOKOnly
Forms!frmEvents!EventDescription.SetFocus
End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
--
Thanks

You all are teaching me so much


Ken Snell (MVP) said:
Empty can mean NULL or empty string, so change the code to this:


If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message Can someone tell me why the following code does not generate a message box
when Agency_ID is 999 or 888 and the EventDescription field is empty?
Agency_ID is a number field and EventDescription is a memo field.

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And [EventDescription] = "") _
Or ([Agency_ID] = "888" And [EventDescription] = "") Then
MsgBox "When choosing "Other, Agency - Describe" or "Other, Sober
House - Describe", you must fill out the Description field.", vbOKOnly
End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Try this code:

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then

MsgBox "When choosing Other, Agency - Describe or Other, Sober
House - Describe, you must fill out the Description field.", vbOKOnly

Forms!frmEvents!EventDescription.SetFocus
Exit Sub

End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


knowshowrosegrows said:
Thanks for the reply.

So I added the Exit Sib after the SetFocus and now this is what I get.

When I fill out the form and put in Agency_ID 999 and leave the
Description
control blank and then press the Submit Entry button, I get the message
box
as I had hoped. When I press the OK button on the message box I get
another
message box that reads "Object doesnt support this property or method."
When
I press that OK button I go back to the form with the record I had been
inputting and my focus is not in the Description field.

What I want to happen when I enter Agency_ID 999 and leave Description
blank
and press the Submit Entry button is the message box. When I press ok I
want
focus to be in Description. When I have Agency_ID at 999 and the
Description
control filled and I push the Submit Entry button, I want a nice new blank
record form.

My current code is as follows:

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then

MsgBox "When choosing Other, Agency - Describe or Other, Sober
House - Describe, you must fill out the Description field.", vbOKOnly
End If
Forms!frmEvents!EventDescription.SetFocus
Exit Sub

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
--
Thanks

You all are teaching me so much


Jim Burke in Novi said:
I take it that you're saying whenever the msgbox is displayed you want
those
two thing done as well. You already have the SetFocus there for the
EventDescription field, but I have a feeling that you're always going to
the
new record even when you don't want it based on what I see and what
you're
describing. Just add an Exit Sub statement right after the SetFocus.

Exit sub

knowshowrosegrows said:
You guys are great. That works for the message box.

Now I am trying to get the focus to go to the EventDescription control
and
no new record to appear. Can someone help tweek the following edit?

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then

MsgBox "When choosing Other Agency - Describe or Other Sober
House
- Describe, you must fill out the Description field.", vbOKOnly
Forms!frmEvents!EventDescription.SetFocus
End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
--
Thanks

You all are teaching me so much


:

Empty can mean NULL or empty string, so change the code to this:


If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"knowshowrosegrows" <[email protected]>
wrote in
message Can someone tell me why the following code does not generate a
message box
when Agency_ID is 999 or 888 and the EventDescription field is
empty?
Agency_ID is a number field and EventDescription is a memo field.

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And [EventDescription] = "") _
Or ([Agency_ID] = "888" And [EventDescription] = "") Then
MsgBox "When choosing "Other, Agency - Describe" or "Other,
Sober
House - Describe", you must fill out the Description field.",
vbOKOnly
End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
K

knowshowrosegrows

Thanks for your reply AGAIN! And on the day before Thanksgiving no less.

I used your code and was getting the same error code. I put an apostrophe
in front of the error statement and then ran it and got a debug screen. The
line that showed up yellow is:
Forms!frmEvents!EventDescription.SetFocus

I double checked and it is the right name of the form and the right name of
the control.

Do you have any suggestions left?
--
Thanks

You all are teaching me so much


Ken Snell (MVP) said:
Try this code:

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then

MsgBox "When choosing Other, Agency - Describe or Other, Sober
House - Describe, you must fill out the Description field.", vbOKOnly

Forms!frmEvents!EventDescription.SetFocus
Exit Sub

End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


knowshowrosegrows said:
Thanks for the reply.

So I added the Exit Sib after the SetFocus and now this is what I get.

When I fill out the form and put in Agency_ID 999 and leave the
Description
control blank and then press the Submit Entry button, I get the message
box
as I had hoped. When I press the OK button on the message box I get
another
message box that reads "Object doesnt support this property or method."
When
I press that OK button I go back to the form with the record I had been
inputting and my focus is not in the Description field.

What I want to happen when I enter Agency_ID 999 and leave Description
blank
and press the Submit Entry button is the message box. When I press ok I
want
focus to be in Description. When I have Agency_ID at 999 and the
Description
control filled and I push the Submit Entry button, I want a nice new blank
record form.

My current code is as follows:

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then

MsgBox "When choosing Other, Agency - Describe or Other, Sober
House - Describe, you must fill out the Description field.", vbOKOnly
End If
Forms!frmEvents!EventDescription.SetFocus
Exit Sub

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
--
Thanks

You all are teaching me so much


Jim Burke in Novi said:
I take it that you're saying whenever the msgbox is displayed you want
those
two thing done as well. You already have the SetFocus there for the
EventDescription field, but I have a feeling that you're always going to
the
new record even when you don't want it based on what I see and what
you're
describing. Just add an Exit Sub statement right after the SetFocus.

Exit sub

:

You guys are great. That works for the message box.

Now I am trying to get the focus to go to the EventDescription control
and
no new record to appear. Can someone help tweek the following edit?

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then

MsgBox "When choosing Other Agency - Describe or Other Sober
House
- Describe, you must fill out the Description field.", vbOKOnly
Forms!frmEvents!EventDescription.SetFocus
End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
--
Thanks

You all are teaching me so much


:

Empty can mean NULL or empty string, so change the code to this:


If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"knowshowrosegrows" <[email protected]>
wrote in
message Can someone tell me why the following code does not generate a
message box
when Agency_ID is 999 or 888 and the EventDescription field is
empty?
Agency_ID is a number field and EventDescription is a memo field.

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And [EventDescription] = "") _
Or ([Agency_ID] = "888" And [EventDescription] = "") Then
MsgBox "When choosing "Other, Agency - Describe" or "Other,
Sober
House - Describe", you must fill out the Description field.",
vbOKOnly
End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
K

knowshowrosegrows

Thanks for your help with this. I noticed that the control source was named
EventDescription but the actual text box name was name Event Description.
Took out the space and your code did just what you said it would.

Thanks so much and have a good holiday if you are celebrating it.
--
Thanks

You all are teaching me so much


Ken Snell (MVP) said:
Try this code:

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then

MsgBox "When choosing Other, Agency - Describe or Other, Sober
House - Describe, you must fill out the Description field.", vbOKOnly

Forms!frmEvents!EventDescription.SetFocus
Exit Sub

End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


knowshowrosegrows said:
Thanks for the reply.

So I added the Exit Sib after the SetFocus and now this is what I get.

When I fill out the form and put in Agency_ID 999 and leave the
Description
control blank and then press the Submit Entry button, I get the message
box
as I had hoped. When I press the OK button on the message box I get
another
message box that reads "Object doesnt support this property or method."
When
I press that OK button I go back to the form with the record I had been
inputting and my focus is not in the Description field.

What I want to happen when I enter Agency_ID 999 and leave Description
blank
and press the Submit Entry button is the message box. When I press ok I
want
focus to be in Description. When I have Agency_ID at 999 and the
Description
control filled and I push the Submit Entry button, I want a nice new blank
record form.

My current code is as follows:

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then

MsgBox "When choosing Other, Agency - Describe or Other, Sober
House - Describe, you must fill out the Description field.", vbOKOnly
End If
Forms!frmEvents!EventDescription.SetFocus
Exit Sub

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
--
Thanks

You all are teaching me so much


Jim Burke in Novi said:
I take it that you're saying whenever the msgbox is displayed you want
those
two thing done as well. You already have the SetFocus there for the
EventDescription field, but I have a feeling that you're always going to
the
new record even when you don't want it based on what I see and what
you're
describing. Just add an Exit Sub statement right after the SetFocus.

Exit sub

:

You guys are great. That works for the message box.

Now I am trying to get the focus to go to the EventDescription control
and
no new record to appear. Can someone help tweek the following edit?

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then

MsgBox "When choosing Other Agency - Describe or Other Sober
House
- Describe, you must fill out the Description field.", vbOKOnly
Forms!frmEvents!EventDescription.SetFocus
End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
--
Thanks

You all are teaching me so much


:

Empty can mean NULL or empty string, so change the code to this:


If ([Agency_ID] = "999" And Len([EventDescription] & "") = 0) _
Or ([Agency_ID] = "888" And Len([EventDescription] & "") = 0) Then


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"knowshowrosegrows" <[email protected]>
wrote in
message Can someone tell me why the following code does not generate a
message box
when Agency_ID is 999 or 888 and the EventDescription field is
empty?
Agency_ID is a number field and EventDescription is a memo field.

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click

If ([Agency_ID] = "999" And [EventDescription] = "") _
Or ([Agency_ID] = "888" And [EventDescription] = "") Then
MsgBox "When choosing "Other, Agency - Describe" or "Other,
Sober
House - Describe", you must fill out the Description field.",
vbOKOnly
End If

If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecord_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Record Update Form 1
OnClick event for button 2
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Update or CancelUpdate without AddNew or Edit. (Error 3020) 0
Copy record code 2
Set focus problem 21
Invalid Use of Null 1
If, Then Button Code 1

Top