Warning message

[Dan @BCBS]

Help I'm stuck -
I need to add a line of code - that pops up a message if a certain value is
entered.

Example: If Me.MBR_GROUPNO = 12345-678 Then
MsgBox "Please report this member ASAP"
DoCmd.CancelEvent
End If

When I add this to my code, nothing happens????
Here is my code...
Thanks



If IsNull(Me.MBR_GROUPNO) Or IsNull(Me.MBR_GROUPNAME) Or
IsNull(Me.MBR_COUNTYCODE) Then
If IsNull(Me.MBR_GROUPNO) Then
MsgBox "Please enter Group Number"
DoCmd.CancelEvent
End If

If IsNull(Me.MBR_GROUPNAME) Then
MsgBox "Please enter Group Name"
DoCmd.CancelEvent
End If
If IsNull(Me.MBR_COUNTYCODE) Then
MsgBox "Please enter County"
DoCmd.CancelEvent
End If


Exit Sub
End If

 

[Brendan Reynolds]

Perhaps ...

If Me.MBR_GROUPNO = "12345-678" Then

Note the quotes.

Without the quotes, you're comparing the value of MBR_GROUPNO to the number
11667 (12345 minus 678) which is probably not what you intended?

 

[Dan @BCBS]

I had tried that, it did not work...
Am I putting it in the wrong place???

This is my entire code for the Form:
Where and what would you code for a message when a certain value is entered?


Option Compare Database
Option Explicit
Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click

If IsNull(Me.MBR_GROUPNO) Or IsNull(Me.MBR_GROUPNAME) Or
IsNull(Me.MBR_COUNTYCODE) Then
If IsNull(Me.MBR_GROUPNO) Then
MsgBox "Please enter Group Number"
DoCmd.CancelEvent
End If
If IsNull(Me.MBR_GROUPNAME) Then
MsgBox "Please enter Group Name"
DoCmd.CancelEvent
End If
If IsNull(Me.MBR_COUNTYCODE) Then
MsgBox "Please enter County"
DoCmd.CancelEvent
End If

If Me.MBR_GROUPNO = "12345-678" Then
MsgBox "Please report this member ASAP"
DoCmd.CancelEvent
End If


Exit Sub
End If


Forms!frmmember.Refresh
DoCmd.Close
Select Case gPath
Case "frmMain"
'do nothing
Case "frmTrackingData"
Forms!frmTrackingData.Refresh
End Select
Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click



End Sub

Private Sub Form_AfterInsert()
End Sub

Private Sub Form_Load()

End Sub

 

[Tom Lake]

Help I'm stuck -
I need to add a line of code - that pops up a message if a certain value is
entered.

Example: If Me.MBR_GROUPNO = 12345-678 Then
MsgBox "Please report this member ASAP"
DoCmd.CancelEvent
End If

When I add this to my code, nothing happens????
Here is my code...

Shouldn't Me have a bang (!) after it rather than a dot?

Tom Lake

 

[BruceM]

First of all, when you say that something doesn't work, be specific.
"Doesn't work" without context or details means no more than saying the car
won't start.

The form's Before Update event would probably be the best place to perform
validation. Attempting to close the form will cause the record to be saved,
which will trigger the Before Update event. Any attempt to navigate to
another record or close the database will similarly cause Before Update to
run.

I have a few observations. There is no need for:
If IsNull(Me.MBR_GROUPNO) Or IsNull(Me.MBR_GROUPNAME) Or
IsNull(Me.MBR_COUNTYCODE) Then
etc.
You are testing for those conditions in the next part of the code anyhow.
Exit Sub is the end of the event. There is no point to more code after
that.
See Help for information on zero-length strings. They are different from
Nulls. If you want to avoid them, you can use table design view to disallow
zero-length strings.
Try setting up a test for a single condition such as leaving MBR_GROUPNO
blank, then create a record with that field left blank. See if you get the
error message when you try to save the record (by trying to navigate to
another record or close the form).
If a field is Null, I suggest setting the focus to the control that is bound
to the field before you cancel the rest of the event. It makes it easier
for the user.

 

[Dan @BCBS]

I appreciate your observations and comments.. I'll try to be more specific.
But the only point I am addressing is If Me.MBR_GROUPNO = "12345-678"

I just need the last 3 values to be any numbers (wildcards)..
All 9 values must have an entry...

When the user enters 12345-??? (any 3 values) my message pops up...
It works correct if I code: (If Me.MBR_GROUPNO = "12345-678") and the user
enters 12345-678. But I need it to pop up with 12345-XXX (any 3 numbers)..

Thanks
Dan

 

[BruceM]

You are now asking a different question than you did originally. Isn't this
now the same question you asked in the queries newsgroup?

 

[Dan @BCBS]

Yes, originally on 2/14, then I realized it was not a Query question.

The code is correct to this point - but my last step is to somehow WildCard
the last 3 values....

I've tried to use Left and create a string but I'm not having much luck..

Thanks

 

[BruceM]

I am not going to monitor the other thread any more. In your last posting
there you wrote:

The Left command works fine, but I put it into the first Sub - instead of
the
BEFORE UPDATE:

If Left(Me.MBR_GROUPNO, 5) = "51735" Then
MsgBox "Check ERISA 180 Day Requirement"
DoCmd.CancelEvent
End If

What do you mean "I put it into the first Sub"? I specified in some detail
how to put the code into the text box's After Update event. That will do
what you say you need (unless you have left out something). Why are you
putting the code someplace else?

There is no need to use a wild card on the last three characters. I don't
know how you would do that in any case. I think you are saying that you are
looking for any instance of MBR_GROUPNO that starts with "51735". The code
I provided finds it and delivers a message. What difference does it make
what the last three numbers are? Is there a time when the five numbers on
the left are "51735" and you do NOT want the message box to appear?

What do you mean "I've tried to use Left and create a string"? After you
enter a value in a text box that is bound to a field, then exit the text
box, the value in the field (and in the text box) is changed, or updated.
That's when any code in the text box's After Update event will run. If the
new value meets certain criteria (such as starting with "51735" then you can
cause a message box to appear, or whatever you want to have happen. You can
check the value of the field at other times, such as before the new or
changed record is saved (the form's Before Update event), but you need to
pick an event that will do what you need. If you check the value of a field
when a Print command button is pressed, and if the user never presses that
button, then the field will not be checked.