ACCESS FEED: [MS_AccessPros] Re: Set validation rule

Senin, 13 Mei 2013

[MS_AccessPros] Re: Set validation rule

Thanks John,

I will bear your fix in mind for the case where
a combo is used.

Regards, Clive.

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Clive-
>
> I guess I just assumed that when he mentioned a list of status codes.
>
> 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 Clive
> Sent: Monday, May 13, 2013 4:32 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Set validation rule
>
> Bonjour John,
>
> I can't see any mention of a combo box in Kumar's post.
>
> Have you got insider information?
>
> Regards, Clive.
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@>
> wrote:
> >
> > Kumar-
> >
> > In addition to what Clive offered, there's no reliable way to reset
> > the status combo box because you cannot set the value of the control
> > in code in the BeforeUpdate event. You could try a Me.Undo, but that
> > doesn't always achieve what you wanted. To ask the user, the code looks
> like:
> >
> > If vbYes = MsgBox("Do you want to proceed?", vbYesNo + vbQuestion
> > +
> > vbDefaultButton2) Then
> > ' .. actions if responding Yes
> > Else
> > ' .. actions if responding No
> > End If
> >
> >
> > 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 Clive
> > Sent: Sunday, May 12, 2013 3:15 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: Set validation rule
> >
> > Hi Kumar,
> >
> > The following should work so that the focus is set to the
> > DateCompletedfield read for the date to be entered.
> >
> > Private Sub Form_BeforeUpdate(Cancel As Integer)
> > If Nz(Me.Status, 0) = 50 And IsNull(DateCompleted) Then
> > MsgBox "Please enter the CompletionDate first"
> > Cancel = True
> > Me.DateCompleted.SetFocus
> > End If
> > End Sub
> >
> > Hope that helps.
> >
> > Regards, Clive.
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "access_kri"
> > <access_kri@> wrote:
> > >
> > >
> > > John,
> > > Thanks. It is working but it would be nice if the message asks
> > > for
> > user input and when user clicks "yes", the focus is set to
> > datecompleted field and if response is "no", the status is reset to
> > previous status. At present, user has to click escape to undo the status
> change.
> > >
> > > Thanks and Regards,
> > > Kumar
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas
> > > <JohnV@>
> > wrote:
> > > >
> > > > Kumar-
> > > >
> > > > To make this easier for users, the DateCompleted text box should
> > > > be in the tab order before the Status combo box. I would do this:
> > > >
> > > > Private Sub DateCompleted_AfterUpdate()
> > > > ' If user entered something
> > > > If Len(Me.DateCompleted & "") > 0 Then
> > > > ' Auto-update the status
> > > > Me.cmbStatus = 50
> > > > End If
> > > > End Sub
> > > >
> > > > Private Sub cmbStatus_BeforeUpdate(Cancel As Integer)
> > > > ' Make sure a Date Completed entered first
> > > > If Len(Me.DateCompleted & "") = 0 Then
> > > > ' Tell the user
> > > > MsgBox "You must enter a Date Completed before " & _
> > > > "you can set Status to Completed. Press " & _
> > > > "Esc to clear your edit and enter a " & _
> > > > "Date Completed first."
> > > > ' Cancel the edit
> > > > Cancel = True
> > > > End If
> > > > End Sub
> > > >
> > > > 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
> > > > access_kri
> > > > Sent: Sunday, May 12, 2013 12:12 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: [MS_AccessPros] Set validation rule
> > > >
> > > > Dear All,
> > > > I have statuscode field which has statuses 10,20,30,40 and
> 50.
> > > > StatusCode 50 indicates the activity is completed. There is also
> > > > another field DateCompleted. I desire that when users update the
> > > > statuscode to 50 (completed), they must first enter the
> > > > dateCompleted field. In other words, when the dateCompleted field
> > > > is blank and users updates the statuscode to 50 (complete), the
> > > > user should get a validation text or messagebox that "You must
> > > > first update the DateCompleted" and new status should be cancelled
> > > > and focus should shift to DateCompleted field. For other
> > > > statuscodes, it
> > should not give any message.
> > > >
> > > > I tried using beforeUpdate event of Statuscode to achieve
> > > > this but was not successful. Require some assistance.
> > > >
> > > > Kind Regards,
> > > > Kumar
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>