disable a field if a value in a given field

  • Thread starter Thread starter tom
  • Start date Start date
T

tom

Thanks for the input. I do think I will need the VB
code. I would rather it kick in before they have to save
the record. I am not clear of what to put though for the
event procedure.

Please help somemore.

Thanks,
Tom
-----Original Message-----
You may be able to set this up as a record validation rule in the table. To
access this, open the table in design mode then open the Properties sheet.
Under Validation Rule type something like

IIf(Not IsNull([Field1]),IsNull([Field2]),IIf(Not
IsNull([Field2]),IsNull([Field1])))

Under Validation Text, put a meaningful message so the user will understand
what is happening if they violate this rule.

The better way to handle this though, would be using VBA. In the form's
Current event and the AfterUpdate event of each of the 2 controls, lock or
unlock the other control depending on whether or not this control contains a
value. Using VBA will make this much more user friendly. The Validation Rule
won't kick in until they try to save the record.

--
Wayne Morgan
Microsoft Access MVP


I need a form to not allow an entry in a certain field if
there is a value of any sort in another given field for
the same record. For example you can not enter hours &
weight for the same record - only one or the other. Do I
need to set up VB or can I just do it in properties.

HELP
Thanks
Click to expand...
Click to expand...
 
I need a form to not allow an entry in a certain field if
there is a value of any sort in another given field for
the same record. For example you can not enter hours &
weight for the same record - only one or the other. Do I
need to set up VB or can I just do it in properties.
Click to expand...

Well, you can't have BOTH fields disabled or the user wouldn't be able
to enter EITHER one. In any case you'll need some VBA code.

What I'd suggest is putting code in the AfterUpdate event of each
textbox on the Form to disable the other if the user enters anything,
and code in the Current event of the Form to check each control for
data and disable the other one:

Private Sub txtHours_AfterUpdate()
If Me!txtHours & "" <> "" Then
Me!txtWeight.Enabled = False
End If
End Sub

<and reciprocally for txtWeight>

Private Sub Form_Current()
If Me.NewRecord Then Exit Sub
If Me!txtHours & "" <> "" Then
Me!txtWeight.Enabled = False
Elseif Me!txtWeight & "" <> "" Then
Me!txtHours.Enabled = False
End If
End Sub

I must say that this table design violates third normal form: the
allowed values in a field should depend only on the Primary Key, not
on any other field!
 
Back
Top