Setting Validation values

  • Thread starter Thread starter Practical Mystic
  • Start date Start date
P

Practical Mystic

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.
Click to expand...

=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
 
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?
Click to expand...

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]
 
John said:
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?
Click to expand...

Use the Form's BeforeUpdate event instead.
Click to expand...

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.

--
 
As this is the tables/database design group, how about a Validation
Rule in the database?
Click to expand...

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]
 
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
 
Svetlana said:
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
Click to expand...


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
 
Back
Top