Ralph-
There are lots of ways to do data validation in an Access application. You
can have Access do some simple validations in the Field definition in the
table. For example, if the user must supply a value for a field, you can
set the Required property to Yes. Access won't let the user save a row with
that field left blank. You can also use the Validation Rule property of a
field to do simple comparisons. Rather than use Required = Yes (which
generates a confusing standard error message), I prefer to use the
Validation Rule set to Is Not Null and provide a custom message in the
Validation Text property. Let's say there's a LastName field in a Customers
table that must always have a value. You can set the Validation Rule to Is
Not Null and define a clearly understandable erroe message in Validation
Text such as "You must supply a last name." You can also use >, <, =, >= or
<= comparisons. Let's say a numeric field must not be negative - you can
set the Validation Rule to >=0 and set the Validation Text to something like
"The Price must not be negative."
What Phucon and I were discussing is doing more complex validation in the
BeforeUpdate event of the form that edits the record. You can check all
sorts of things, including comparing two or more fields. You can also do
validations similar to what you can define in the Field properties and point
the user to the field that has the problem. For example, if I want to be
sure there's something in the LastName field and then put the user there if
not, I can do:
Private Sub Form_BeforeUpdate(Cancel As Integer)
' If the user has left the last name blank,
If IsNull(Me.LastName) Then
' Tell the user about the problem
MsgBox "You must supply a last name value."
' Put the focus on the field in error
Me.LastName.SetFocus
' Tell Access to not save the record
Cancel = True
End If
End Sub
Does that help?
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of RalphCelento
Sent: Thursday, August 22, 2013 3:30 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Error 2115, in Form_BeforeUpdate procedure
John,
Sorry about the confusion, I was referring to the validation rule. Where
and how would this be done to validate the changes are confirmed (YES, NO,
cancelled). I only am familiar with formating limits on a field, lookup
linkage, data validation (Alpha, Numeric, etc.).
Any help you can provide is greatly appreciated.
Ralph Celento, GS-12, DAFC
Program Analyst, US Air Force
210-565-2095 (work)
RalphCelento@gmail.com
Ralph.Celento.1@us.af.mil
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...>
wrote:
>
> Ralph-
>
> I'm not sure I understand what you mean by "process check."
>
> You would normally use the BeforeUpdate event to perform final
> validation of data entered by the user - usually something that can't
> be handled with normal Field or Table validation rules. You can also
> use it as Phucon has done to give the user one final prompt before
> saving a record. When the data doesn't validate or the user replies
> "no", use Cancel = True to halt saving the record.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications SQL Queries for Mere Mortals
> http://www.viescas.com/ (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> RalphCelento
> Sent: Wednesday, August 21, 2013 4:27 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Error 2115, in Form_BeforeUpdate
> procedure
>
> John,
>
> I was reading Phucon and your messages on this error. As Phucon said
> thank you for explaining the BeforeUpdate process flow. My question
> is where would you put the process check at?
>
> Thank you,
>
> Ralph Celento, GS-12, DAFC
> Program Analyst, US Air Force
> 210-565-2095 (work)
> RalphCelento@...
> Ralph.Celento.1@...
>
> --- In MS_Access_Professionals@yahoogroups.com, "saigonf7q5"
> <saigonf7q5@> wrote:
> >
> > That works great. Thank you very much Mr.Viescas, and thank you for
> > the
> explanation about how the BeforUpdate works. I now understand why it
> caused the error.
> >
> > My name is Phucon. Thanks again.
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com, John Viescas
> > <JohnV@>
> wrote:
> > >
> > > Saigon- (name?)
> > >
> > > You cannot force a save in the BeforeUpdate event because Access
> > > is still waiting for you to decide whether to cancel the update or
not.
> > > If it allowed you to save, you would get in an infinite loop
> > > because BeforeUpdate would try to fire again. Just let the code
> > > exit if the user replies Yes - Access will immediately save the
> > > record. When the user replies No, you should also do Cancel = True to
halt the save.
> > >
> > > John Viescas, Author
> > > Microsoft Access 2010 Inside Out
> > > Microsoft Access 2007 Inside Out
> > > Microsoft Access 2003 Inside Out
> > > Building Microsoft Access Applications SQL Queries for Mere
> > > Mortals http://www.viescas.com/ (Paris, France)
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > > saigonf7q5
> > > Sent: Monday, August 19, 2013 10:52 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Error 2115, in Form_BeforeUpdate
> > > procedure
> > >
> > > I have the procedure as shown here below. When the response is
> > > Yes, it generated the Error 2115(The macro or function set to the
> > > BeforUpdate or ValidationRule property for this field is
> > > preventing Microsoft Access from saving the data in the field.)
> > >
> > > Even though the error says ".preventing Access from saving." the
> > > changes were still saved, however. I have also tried the
> > > "DoCmd.RunCommand acCmdSaveRecord", it still produced the same
> > > error message. What or where trigger the error??
> > >
> > > Thanks
> > > Phucon
> > >
> > > Private Sub Form_BeforeUpdate(Cancel As Integer) On Error GoTo
> ErrorHandler
> > > If Me.Dirty = True Then
> > > If MsgBox("You have made changes to the data. Save
> > > changes?", vbYesNo, "Information") = vbYes Then
> > > Me.Dirty = False 'force the record to be saved
> explicitly.
> > > 'DoCmd.RunCommand acCmdSaveRecord
> > > Else
> > > Me.Undo
> > > End If
> > > End If
> > > ExitProcedure:
> > > Exit Sub
> > >
> > > ErrorHandler:
> > >
> > > MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
> > > procedure Form_BeforeUpdate"
> > > Resume ExitProcedure
> > > End Sub
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
------------------------------------
Yahoo! Groups Links
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (8) |
Tidak ada komentar:
Posting Komentar