Before Update

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to add a pop-up comment when the user enters a certain number in
the text box.
The code below I added to the text box but it does nothing.

Any suggestions.

Private Sub MEMBERNO_BeforeUpdate(Cancel As Integer)
If Me.MEMBERNO = "H23917176" Then
MsgBox "** Please contact the fraud dept. **"
Exit Sub
End If
End Sub
 
You need to put it in the after update event instead because in the before
update, the value is not in the box yet.
 
That is not correct, Dennis. It is in the form control (box), but not yet
updated to the form's recordset. One thing that is missing is canceling the
event. I would put a line
Cancel = True
just before the Msgbox line.

Run the code in debug mode to determine why it is not showing the message box.
 
That is perfect - Thanks

One last question - I'm trying to add an "OR" statement:
If Me.MEMBERNO = "H23917176" Or "H39592261" Then

I've tried about 12 other was and I'm getting no-where..

Suggestions??
 
Compile error - Suggestions for #14??

If Me.MEMBERNO = "H23917176" Or Me.MEMBERNO "H39592261" Then
Cancel = True
Else
MsgBox "**.....
 
Actually, this should only be 13.1
If Me.MEMBERNO = "H23917176" Or Me.MEMBERNO = "H39592261" Then

Oops, left out the =
 
What is the actual error?
post the entire sub or function.
Is MEMBERNO the name of a control on your form?
 
There is no actual error - the message pops up - but it pops up for any entry.
Yes MEMBERNO is the control on the form which is straight from the table, no
query.


Private Sub MEMBERNO_BeforeUpdate(Cancel As Integer)
If Me.MEMBERNO = "H23917176" Or Me.MEMBERNO = "H39592261" Then
Cancel = True
Else
MsgBox "** Contact the fraud dept. ASAP **"
Exit Sub
End If
End Sub
 
Okay, I see what you are saying. The code is doing exactly what you are
telling it to do. (that's what I hate about computers)

Private Sub MEMBERNO_BeforeUpdate(Cancel As Integer)
If Me.MEMBERNO = "H23917176" Or Me.MEMBERNO = "H39592261" Then
Cancel = True
Else
MsgBox "** Contact the fraud dept. ASAP **"
Exit Sub
End If
End Sub

As written, the code will throw the message box for every member number
except the two you have in the code. If you want the message to show only
for those 2 codes, it needs to be this way:

Private Sub MEMBERNO_BeforeUpdate(Cancel As Integer)
If Me.MEMBERNO = "H23917176" Or Me.MEMBERNO = "H39592261" Then
Cancel = True
MsgBox "** Contact the fraud dept. ASAP **"
End If
End Sub

However, I would not hard code something like member numbers. I would have
a field in the member table that would tell us to flag this as a fraud alert.
Then if you have to add or remove member numbers, you don't have to change
the code, you only need to change the flag in the member record:

Private Sub MEMBERNO_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("[FRAUD_ALERT]", "tblMember","[MEMBERNO] = '" &
Me.MEMBERNO & "'",0)) = True Then
Cancel = True
MsgBox "** Contact the fraud dept. ASAP **"
End If
End Sub

--
Dave Hargis, Microsoft Access MVP


Dan @BCBS said:
There is no actual error - the message pops up - but it pops up for any entry.
Yes MEMBERNO is the control on the form which is straight from the table, no
query.
Click to expand...
Private Sub MEMBERNO_BeforeUpdate(Cancel As Integer)
 
Words or Money could not express my thankfullness..
So I'll just say it - Thank you...

Klatuu said:
Okay, I see what you are saying. The code is doing exactly what you are
telling it to do. (that's what I hate about computers)

Private Sub MEMBERNO_BeforeUpdate(Cancel As Integer)
If Me.MEMBERNO = "H23917176" Or Me.MEMBERNO = "H39592261" Then
Cancel = True
Else
MsgBox "** Contact the fraud dept. ASAP **"
Exit Sub
End If
End Sub

As written, the code will throw the message box for every member number
except the two you have in the code. If you want the message to show only
for those 2 codes, it needs to be this way:

Private Sub MEMBERNO_BeforeUpdate(Cancel As Integer)
If Me.MEMBERNO = "H23917176" Or Me.MEMBERNO = "H39592261" Then
Cancel = True
MsgBox "** Contact the fraud dept. ASAP **"
End If
End Sub

However, I would not hard code something like member numbers. I would have
a field in the member table that would tell us to flag this as a fraud alert.
Then if you have to add or remove member numbers, you don't have to change
the code, you only need to change the flag in the member record:

Private Sub MEMBERNO_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("[FRAUD_ALERT]", "tblMember","[MEMBERNO] = '" &
Me.MEMBERNO & "'",0)) = True Then
Cancel = True
MsgBox "** Contact the fraud dept. ASAP **"
End If
End Sub

--
Dave Hargis, Microsoft Access MVP


Dan @BCBS said:
There is no actual error - the message pops up - but it pops up for any entry.
Yes MEMBERNO is the control on the form which is straight from the table, no
query.
Click to expand...
Private Sub MEMBERNO_BeforeUpdate(Cancel As Integer)
If Me.MEMBERNO = "H23917176" Or Me.MEMBERNO = "H39592261" Then
Cancel = True
Else
MsgBox "** Contact the fraud dept. ASAP **"
Exit Sub
End If
End Sub
Click to expand...
Click to expand...
 
I'd rather have the money :)

You are welcome, sorry about my initial confusion.
--
Dave Hargis, Microsoft Access MVP


Dan @BCBS said:
Words or Money could not express my thankfullness..
So I'll just say it - Thank you...

Klatuu said:
Okay, I see what you are saying. The code is doing exactly what you are
telling it to do. (that's what I hate about computers)

Private Sub MEMBERNO_BeforeUpdate(Cancel As Integer)
If Me.MEMBERNO = "H23917176" Or Me.MEMBERNO = "H39592261" Then
Cancel = True
Else
MsgBox "** Contact the fraud dept. ASAP **"
Exit Sub
End If
End Sub

As written, the code will throw the message box for every member number
except the two you have in the code. If you want the message to show only
for those 2 codes, it needs to be this way:

Private Sub MEMBERNO_BeforeUpdate(Cancel As Integer)
If Me.MEMBERNO = "H23917176" Or Me.MEMBERNO = "H39592261" Then
Cancel = True
MsgBox "** Contact the fraud dept. ASAP **"
End If
End Sub

However, I would not hard code something like member numbers. I would have
a field in the member table that would tell us to flag this as a fraud alert.
Then if you have to add or remove member numbers, you don't have to change
the code, you only need to change the flag in the member record:

Private Sub MEMBERNO_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("[FRAUD_ALERT]", "tblMember","[MEMBERNO] = '" &
Me.MEMBERNO & "'",0)) = True Then
Cancel = True
MsgBox "** Contact the fraud dept. ASAP **"
End If
End Sub

--
Dave Hargis, Microsoft Access MVP


Dan @BCBS said:
There is no actual error - the message pops up - but it pops up for any entry.
Yes MEMBERNO is the control on the form which is straight from the table, no
query.
Click to expand...
Private Sub MEMBERNO_BeforeUpdate(Cancel As Integer)
If Me.MEMBERNO = "H23917176" Or Me.MEMBERNO = "H39592261" Then
Cancel = True
Else
MsgBox "** Contact the fraud dept. ASAP **"
Exit Sub
End If
End Sub





:

What is the actual error?
post the entire sub or function.
Is MEMBERNO the name of a control on your form?

--
Dave Hargis, Microsoft Access MVP


:

13.2
The message box appears no matter what is entered??





:

Actually, this should only be 13.1
If Me.MEMBERNO = "H23917176" Or Me.MEMBERNO = "H39592261" Then

Oops, left out the =
--
Dave Hargis, Microsoft Access MVP


:

Compile error - Suggestions for #14??

If Me.MEMBERNO = "H23917176" Or Me.MEMBERNO "H39592261" Then
Cancel = True
Else
MsgBox "**.....




:

Here is number 13 :)

If Me.MEMBERNO = "H23917176" Or Me.MEMBERNO "H39592261" Then

--
Dave Hargis, Microsoft Access MVP


:

That is perfect - Thanks

One last question - I'm trying to add an "OR" statement:
If Me.MEMBERNO = "H23917176" Or "H39592261" Then

I've tried about 12 other was and I'm getting no-where..

Suggestions??



:

That is not correct, Dennis. It is in the form control (box), but not yet
updated to the form's recordset. One thing that is missing is canceling the
event. I would put a line
Cancel = True
just before the Msgbox line.

Run the code in debug mode to determine why it is not showing the message box.
--
Dave Hargis, Microsoft Access MVP


:

You need to put it in the after update event instead because in the before
update, the value is not in the box yet.

:

I'm trying to add a pop-up comment when the user enters a certain number in
the text box.
The code below I added to the text box but it does nothing.

Any suggestions.

Private Sub MEMBERNO_BeforeUpdate(Cancel As Integer)
If Me.MEMBERNO = "H23917176" Then
MsgBox "** Please contact the fraud dept. **"
Exit Sub
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
That is not correct. If you follow tthe posts, you will see it was a logic
coding error and has been resolved.
If what you are saying were true, the Before Update event would be useless.
The value is in the control as soon as the user enters it. The Before Update
event does not fire until the user moves the focus to another control.
 
Back
Top