Setting Validation values

Practical Mystic

unread,

Jun 13, 2006, 1:19:24 AM6/13/06

to

I'm trying to set the Validation Rule for a Form Field by the contents of a
previous Field in the same Form. I tried the syntax below in both the On
Enter Event for [Payments Time] and On Exit for [Payments Processed]. It
doesn't require [Payments Time] to be >0 when the [Payments Processed] field
>0.

=IIf([Payments Processed]>0,[Payments Time].ValidationRule=">0",[Payments
Time].ValidationRule=Null)

How can I get [Payments Time] to require it's value to be >0 if [Payments
Processed]>0 but remain 0 (it's default value) if [Payments Processed]=0?

Thanks for your help

John Vinson

unread,

Jun 13, 2006, 1:39:39 AM6/13/06

to

On Tue, 13 Jun 2006 05:19:24 GMT, "Practical Mystic"
<practic...@sbcglobal.net> wrote:

>How can I get [Payments Time] to require it's value to be >0 if [Payments
>Processed]>0 but remain 0 (it's default value) if [Payments Processed]=0?

Use the Form's BeforeUpdate event instead. You can't control what
order the user enters the data, after all - they might fill in the
Payments Time first!

Try code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me![Payments Processed] = 0 Then
Me![Payments Time] = 0
Else
If Me![Payments Time] <= 0 Then
MsgBox "Please enter a Payments Time value", vbOKOnly
Cancel = True ' prevent adding the record
Me![Payments Time].SetFocus
End If
End If
End Sub

John W. Vinson[MVP]

Jamie Collins

unread,

Jun 14, 2006, 8:25:59 AM6/14/06

to

John Vinson wrote:
> On Tue, 13 Jun 2006 05:19:24 GMT, "Practical Mystic"
> <practic...@sbcglobal.net> wrote:
>
> >How can I get [Payments Time] to require it's value to be >0 if [Payments
> >Processed]>0 but remain 0 (it's default value) if [Payments Processed]=0?
>
> Use the Form's BeforeUpdate event instead.

As this is the tables/database design group, how about a Validation
Rule in the database? Something like

(payments_processed_amount = 0 AND payments_processed_effective_date IS
NULL)
OR
(payments_processed_amount > 0 AND payments_processed_effective_date IS
NOT NULL)

Jamie.

--

John Vinson

unread,

Jun 14, 2006, 4:12:41 PM6/14/06

to

On 14 Jun 2006 05:25:59 -0700, "Jamie Collins"
<jamiec...@xsmail.com> wrote:

>As this is the tables/database design group, how about a Validation
>Rule in the database?

Good point Jamie - though the OP crossposted to forms and formscoding
as well. It would probably be wise to do both - on the Form so you can
control the error message and make it friendlier (or more hostile if
you prefer <g>), and in the table to prevent "backdoor" entry of
invalid data.

John W. Vinson[MVP]

Svetlana

unread,

Jun 20, 2006, 5:32:05 AM6/20/06

to

You could make the validation you need in form's before update event or
on Exit Event of [Payments Processed] try this
If Me.[Payments Processed].Text = 0 Then
Me.[Payments Time].SetFocus
Me.[Payments Time].Text = 0
End If

Marshall Barton

unread,

Jun 20, 2006, 10:40:04 AM6/20/06

to

Svetlana wrote:

Svetlana, the .Text property is not used that way in the
Access object model. Use the .Value property instead.
Since Value is the default property, it is not even required
so, as John posted earlier, you could use:

If Me.[Payments Processed] = 0 Then
Me.[Payments Time] = 0
End If

--
Marsh
MVP [MS Access]

Svetlana

unread,

Jun 21, 2006, 7:40:26 AM6/21/06

to

Umm yes i forgot the quotes.. "..wink.."