disable a field on form if a value is in another field

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

tom

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
 
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.
 
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...
 
In the AfterUpdate event of each of the controls and in the form's Current
event (to catch when you move from one record to another) use something like
this:

If Not IsNull(Me.Control1) Then
Me.Control2.Locked = True
Else
Me.Control2.Locked = False
End If
If Not IsNull(Me.Control2) Then
Me.Control1.Locked = True
Else
Me.Control1.Locked = False
End If

While harder to read, the above could be shortened to:
Me.Control2.Locked = Not IsNull(Me.Control1)
Me.Control1.Locked = Not IsNull(Me.Control2)

--
Wayne Morgan
Microsoft Access MVP


tom said:
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...
Click to expand...
 
Back
Top