Hi LG,
Will the below still work?
Not as written, because of the line of code that reads: Cancel = True, which
is cancelling the Update event. You can try commenting out this line of code,
but you still may not like the effect produced. For example, if you are
displaying navigation buttons, and the conditions are right to produce the
message box (ie. reason code =1, platform= QL and Carrier=QLLAZBOY), a person
could attempt to navigate to a new record. They would receive the message box
prompt, and then the form would be displaying a different record (or a new
record).
If you are going to allow this combination, I would change the text in the
title property, so that it is something different than "Invalid Platform
Code...". You've got a couple of possibilities for how to handle this
situation:
1.) Use the Form_Current procedure.
Pro: User will receive prompt if conditions are met, even if they do not
edit the record.
Con: Such prompts might drive your users nuts if there are a lot of records
that would produce the message box.
2.) Use an enhanced Form_BeforeUpdate procedure
Requires additional code, and perhaps the addition of a Yes/No field to the
underlying table. More on that, but only if you want to pursue this route.
3.) Use Conditional Formatting
This might be the most efficient method of notifying the user, by simply
turning the font red for the Platform code, if the other conditions are met.
Or, you could conditionally make the visible property for a label visible, to
give them a prompt that alerts them, but doesn't force them to click an OK
button in a message box. I think this last option is certainly the easiest to
implement.
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
:
Thank you. I would not need to check previous records since this will be
going forward as of Feb 1. I just want a message box to pop up to warn them
that they need to check if they are covered on another platform. It does not
necessarily mean that combination can not be entered. Will the below still
work?
__________________________________________
:
Hi LG,
This should certainly be possible. The part I'm not entirely clear about is
this statement: "Please check elegibility on another platform". Do you want
to disallow the combination of reason code =1, platform= QL and
Carrier=QLLAZBOY, or simply prompt the user to check eligibility on another
platform, but ultimately allow QL if that is what they choose?
Also, if you want to disallow this combination from now on out, you will
probably want to run a query to first check for any existing records that
meet all three conditions, and make appropriate changes. A Form_BeforeUpdate
procedure should prevent adding a record with these three conditions, by
setting Cancel = True, cancelling the update. However, that won't do anything
for existing records that meet these conditions, unless you make some editing
change (any change) in each record, to cause the Form_BeforeUpdate event to
fire. You could use the Form_Current procedure to check for existing records,
without having to make an editing change, or simply run a query to check for
existing records first.
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
If ([reason code] = 1 And [Platform] = "QL" And [Carrier] = "QLLAZBOY")
Then
MsgBox "Please check elegibility on another platform.", _
vbInformation, "Invalid Platform Code..."
Me.Platform.SetFocus '<---Assumption: Control name is "Platform"
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
:
I have a form but need it to have a pop up box with certain conditions.
Example: The message box will only pop up if reason code =1, platform= QL
and Carrier=QLLAZBOY
I would like the message box to pop if all of the above are true and say
Please check elegibility on another platform.
Is this possible if so please send me in the correct direction.
thanks
