As Joe said, the Fee Field has to be defined as Text, in order to have 'N/A' as a Value. That's perfectly OK; if you need to do math with the Field when Sex = "Female" you just wrap it with the Val() Function:
Val(Me.Fee)
Another thing is that if the user selects 'male" and realizes that this is incorrect, the Fee Field needs to be set back to Null.
Code:
Private Sub Sex_AfterUpdate()
If Nz(Me.Sex, "") <> "" Then
Me.Fee.Locked = False
Me.Fee = Null
Else
Me.Fee.Locked = True
End If
If Me.Sex = "male" Then
Me.Fee = "N/A"
Me.Fee.Locked = True
ElseIf Me.Sex = "female" Then
Me.Fee.Locked = False
End If
End Sub
When moving from Record to Record, you have to
- Lock/Unlock the Fee Field as is appropriate for each Record
- Have the Fee Field Unlocked if this is a New Record, until/if Sex is entered and is "male"
For this you'll need code in the Form_Current event as well as in the Sex_AfterUpdate event
Code:
Private Sub Form_Current()
If Nz(Me.Sex, "") <> "" Then
Me.Fee.Locked = False
Else
Me.Fee.Locked = True
End If
If Me.Sex = "male" Then
Me.Fee = "N/A"
Me.Fee.Locked = True
ElseIf Me.Sex = "female" Then
Me.Fee.Locked = False
End If
End Sub
I think this covers all possibilities. This could be done with fewer lines of code, but I prefer to take up a little more space and make what's being done easier to understand.
I also prefer using the Locked Property rather than the Enabled Property, but that's a personal choice, either will work just fine.
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007