How to Limit the Value of Form input by

[hwong]

I would like to know how to control the value input in an access form by depending another value inputted in the same form.



Say, my form has two fields, 1st is "Sex" and 2nd is "Fee".

If the user chooses the value of 1st field is Male. Then the fee should be nil. I would like to make the 2nd field as "N/A" and cannot be editable. On the other hand, If the user chooses "Female", the user has to input the "Fee" value manually.

How can I make it? Can anyone teach me?

Thanks x 100!!

[JoeM]

You can add code to the After Update event of your first field (go into the Properties of that field, go to the Events tab, select "After Update", pick Event Procedure. This should set up the "shell" for the code you need, and then just place your VBA code in between. It should look something like this:

Private Sub SexField_AfterUpdate()

    If Me.SexField="Male" Then
        Me.FeeField="N/A"
        Me.FeeField.Enabled=False
    Else
        Me.FeeField.Enabled=True
    End If

End Sub

BTW, if your FeeField is formatted as a numeric field, you aren't going to be able to enter a text value like "N/A" in it! So you may need to change the format, or change what you put in it (maybe leave it as Null).

[Missinglinq]

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.

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

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)>

[JoeM]

I also prefer using the Locked Property rather than the Enabled Property, but that's a personal choice, either will work just fine.

Yep, I prefer "Enabled" because when the field is disabled, it is grayed out, making it a bit more obvious that they cannot update it (and with my user group, the more obvious we make things for them, the better!).

[Missinglinq]

Programming would be a great gig, if you didn't have to deal with end users, wouldn't it?

Linq ;0)>

[JoeM]

Programming would be a great gig, if you didn't have to deal with end users, wouldn't it?

Its kind of a double-edged sword. They keep us in business, but they have tendency to drive us crazy!

[hwong]

Thanks above. It helps very much!