Ken Milam
asked on
Popup Messge Box before update
Hello Experts,
I have a sub sub form with a data entry field, [Measurement]. This sub sub form also has a field named [Status].
If errant data is entered into [Measurement], I write the value "Error" into the [Status] field w/ an After Update event on the [Measurement] field.
Prior to saving a record with [Status]="Error", I want to present the user with a courtesy Popup Message Box that reads "Entered value is out of specification." If the user hits okay, record saves, if they hit cancel, focus is returned to [Measurement].
How do I accomplish this and where does the VBA reside?
Thanks!
I have a sub sub form with a data entry field, [Measurement]. This sub sub form also has a field named [Status].
If errant data is entered into [Measurement], I write the value "Error" into the [Status] field w/ an After Update event on the [Measurement] field.
Prior to saving a record with [Status]="Error", I want to present the user with a courtesy Popup Message Box that reads "Entered value is out of specification." If the user hits okay, record saves, if they hit cancel, focus is returned to [Measurement].
How do I accomplish this and where does the VBA reside?
Thanks!
Use the Before Update event to cancel the Update and populate the error indicator..and of course clear the erroneous data
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Note also when you're dealing with subforms (and subsubforms), data in their parent forms is automatically saved when you move INTO the subforms. This might not matter to you, but it's best to be aware of this when trying to validate.
ASKER
Thanks to all. I ended up using the following code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If [StatusCalc] > 0 Then
MsgBox "Your data is out of spec.", , "Missing Data"
Cancel = True
End If
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
If [StatusCalc] > 0 Then
MsgBox "Your data is out of spec.", , "Missing Data"
Cancel = True
End If
End Sub
As I recommended ...too bad I was on my phone and couldn't post long extra detailed solution
ASKER
John, thanks for your continued support. Sorry for not yet having the technical aptitude to pick on the short hand. Thanks, Ken
you're welcome, Ken ~ happy to help. John and Scott had helpful comments too.
comment about the code you posted:
thanks for sharing! By the way, it would be good to use Me.StatusCalc instead of just StatusCalc without referencing the form. This is to be explicit about what is being referenced ... and help the compiler too. Not sure if "Option Explicit" is at the top of the module so references are checked when code is compiled, but it is a good idea to have. Did you compile?
What if StatusCalc is Null*? If this might be the case, convert Null to zero and change to something like this:
have an awesome day,
crystal
comment about the code you posted:
thanks for sharing! By the way, it would be good to use Me.StatusCalc instead of just StatusCalc without referencing the form. This is to be explicit about what is being referenced ... and help the compiler too. Not sure if "Option Explicit" is at the top of the module so references are checked when code is compiled, but it is a good idea to have. Did you compile?
What if StatusCalc is Null*? If this might be the case, convert Null to zero and change to something like this:
if Nz(Me.StatusCalc, 0) > 0 then
* Null means NO VALUE ... and nothing can't be compared to something -- so change Null to zero, or whatever else you specify in the second parameterhave an awesome day,
crystal
Good to know Kevin about your technical aptitude in order to know when my contribution would be helpful .
ASKER
Great concluding tips/explanations, Crystal. Thanks!
thanks, Ken, you're welcome ~ happy to help
have an awesome day,
crystal
have an awesome day,
crystal