Message Box if either one of two codes in field

[CanaryCrazy]

I need a message box to pop up on a form if the user selects an entry from a
combo box (control source is a field called "Requestor") that does NOT
contain either the letters "FD" or "WM" in the field AND the amount in a
different field called "Amount" is 250000 or more.

The Requestor field is a list of names, and within the field is the "FD" or
"WM" letters to designate some of the folks as working for one group
another.... others on the list may not have any such designation and THOSE
ARE THE GUYS I'm interested in. It is the folks who do NOT have the
designation of the group they work for that I need the message box to appear
for.

I have this code working fine as far as not firing the message box for
anyone who has "FD" in the field, BUT I cannot figure out how to not have it
pop for the ones that have "WM" in the field.

I currently have it written as:

Private Sub Combo100_AfterUpdate()

If Me.Amount >= 250000 And InStr(Me.Requestor, "FD") = 0 Then MsgBox "This
loan should be going through Funding Desk.", vbExclamation, "Funding Desk
Warning"

End Sub


Any help is appreciated.

 

[Klatuu]

Just add it to the check:

If Me.Amount >= 250000 And (InStr(Me.Requestor, "FD") = 0 Or
InStr(Me.Requestor, "FD")) Then MsgBox "This
loan should be going through Funding Desk.", vbExclamation, "Funding Desk
Warning"

 

[CanaryCrazy]

I revised to this and it fires on any selection made regardless of the FD or
WM being in the field.... something is still wrong, I'm afraid. I adjusted
what you suggested to show the "WM" exclusion, but I am getting the message
now for all. Mmmmm, what could the matter be?

Private Sub Combo100_AfterUpdate()

If Me.Amount >= 250000 And (InStr(Me.Requestor, "FD") = 0 Or
InStr(Me.Requestor, "WM") = 0) Then MsgBox "This loan should be going through
Funding Desk. RETURN TO SENDER and input Assigner's Comment to show it was
returned.", vbExclamation, "Funding Desk Warning"

End Sub

 

[Klatuu]

Try it like this:

Private Sub Combo100_AfterUpdate()

If Me.Amount >= 250000 Then
If InStr(Me.Requestor, "FD") = 0 Or InStr(Me.Requestor, "WM") =
0) Then
MsgBox "This loan should be going through
Funding Desk. RETURN TO SENDER and input Assigner's Comment to show it was
returned.", vbExclamation, "Funding Desk Warning"
Endif
Endif

 

[CanaryCrazy]

It is still firing on any selection from the Requestor Field (it is behaving
correctly when in considers the Amount field as far as not firing if the
amount is less than 250000).

I had to remove one ")" from your string to get away from a compile error,
ending up with this, but still have the problem. Any ideas?

Private Sub Combo100_AfterUpdate()
If Me.Amount >= 250000 Then
If InStr(Me.Requestor, "FD") = 0 Or InStr(Me.Requestor, "WM") = 0 Then
MsgBox "This loan should be going through Funding Desk. RETURN TO SENDER and
input Assigner's Comment to show it was returned.", vbExclamation, "Funding
Desk Warning"
End If

End Sub

 

[Klatuu]

I don't see the problem. Is the requestor field a Lookup field?

 

[CanaryCrazy]

The Requestor Field is in a combo box.

The data is taken from a look-up table called RefRequestor with the names of
all the people who request deals in a field called RequestorName. The combo
box then stores the name the user selected from the combo box into the
Control source called Requestor on the form (and into the table that the form
is populating).

Maybe this will help????

 

[Klatuu]

Does the combo box have more than one column? Is the value you want to
examine in the bound column? It could be this or it could be because you
are using a lookup table field. You don't get the value you are expecting
with lookups. I never use them because of the problems they have and you
will find most professional developers do not. So, if it is a lookup field
problem, I can't help.

 

[CanaryCrazy]

I see. I think my only solution to get this functionality here is to have
just one code for anyone who this message does not need to fire for (instead
of either the "WM" or "FD").

The message box works if I only use one, it's adding that second option that
discombobulates it.


Thanks so much for your thoughts and for trying to help on this. I was
thinking that once the lookup combo box populated the value into the field,
that field could be used. I'm afraid I know just enough to be dangerous with
this.

Have a good day,
Brenda

 

[Klatuu]

--
Dave Hargis, Microsoft Access MVP

I see. I think my only solution to get this functionality here is to have
just one code for anyone who this message does not need to fire for (instead
of either the "WM" or "FD").

The message box works if I only use one, it's adding that second option that
discombobulates it.


Thanks so much for your thoughts and for trying to help on this. I was
thinking that once the lookup combo box populated the value into the field,
that field could be used. I'm afraid I know just enough to be dangerous with
this.

Have a good day,
Brenda

 

[Klatuu]

sorry about that.

I am puzzled because if one works, both should.
Have you tried it by looking only FD and then change it to look for only WM?
There is something strange about this.