To enable or disable a field in a form by changing the value of other fields

[Pascal Notic]

Hi everybody, I've got a form based on a table where i have a field called MaidenName. I want to enable or disable the field depending on the value of two other field called Sex and Status. For example i would like to enable the field of MaidenName only when i have 'female' and 'married' in the field of Sex and Status respectively. I have only two values to choose in each of these two fields, that is, Female and Male for Sex of course and Single and Married for the Status field. I would be much grateful if somebody could help on this issue. Thanks Pascal.

[June7]

Set the MaidenName textbox as Enable No or Visible No. Then code in both Sex and Status AfterUpdate events like:

Me.MaidenName.Visible = Me.Sex = "female" And Me.Status = "married"

[Missinglinq]

It's not quite as simple as that. When the first Field (either sex or status) is populated the code will fire and you'll get an 'Invalid use of Null' error message, because the other Field is Null, at this point. So you need to only run the code if both Fields have data in them.

You also need to use the Form_Current event to be sure that the Visibility of MaidenName is appropriate for that Record, else it will be formatted according to the condition of the previous Record viewed.

The same thing applies to a New Record. If you don't address this, the Visibility of the Field will depend on the Visibility of the last Record viewed. In the code below, in the Form_Current event, I set the Visibility, on a New Record, to True. That's a personal preference, so you can change that to False, if you like.

Private Sub Sex_AfterUpdate()
If Nz(Me.Status, "") <> "" And Nz(Me.Sex, "") <> "" Then
 Me.MaidenName.Visible = Me.Sex = "female" And Me.Status = "married"
End If
End Sub

Private Sub Status_AfterUpdate()
If Nz(Me.Status, "") <> "" And Nz(Me.Sex, "") <> "" Then
 Me.MaidenName.Visible = Me.Sex = "female" And Me.Status = "married"
End If
End Sub

Private Sub Form_Current()
If Me.NewRecord Then
 Me.MaidenName.Visible = True
End If
If Nz(Me.Status, "") <> "" And Nz(Me.Sex, "") <> "" Then
 Me.MaidenName.Visible = Me.Sex = "female" And Me.Status = "married"
End If
End Sub

Also note that you can only do this, in Code, if this is a Single View Form. For a Datasheet View Form or a Continuous View Form, you'll have to use the Enabled Property and Conditional Formatting, but we'll address that only if we need to.

Linq ;0)>

[Pascal Notic]

Hi June7 and Missinglinq.. I would like to thank you both for the time you took to reply to my question. I was able to use the code of Missinglinq and it worked fine though i would have preferred to use enable instead of visible. Stay blessed.
Pascal.

P.S. If in case it's not too much asking and if does not require much of your time I would be grateful if you could let me have the code for the enable too. Thanks

[Missinglinq]

...If in case it's not too much asking and if does not require much of your time I would be grateful if you could let me have the code for the enable too...

Don't know if I have enough time, and know that you're new at this, and this is kind of complicated, but

1. Search through the code I gave you
2. Find Visible
3. Replace it with Enabled
4. Repeat Steps 2-3 until done
   

Couldn't resist that!

Glad we could help!

Linq ;0)>