FRUSTRATED: Enable field depending on value of another

[PsyberFox]

Hi,

I have the following three subs (amongst others):
Private Sub Form_Current()
If Trim(Title.Value) = "Other" Then
TitleOther.Enabled = True
TitleOther.SetFocus
Else
TitleOther.Enabled = False
End If

If MedicalAid = "OTHER" Then
MedicalAidOther.Enabled = True
MedicalAidOther.SetFocus
Else
MedicalAidOther.Enabled = False
End If
End Sub

Private Sub Title_AfterUpdate()
If Trim(Title.Value) = "Other" Then
TitleOther.Enabled = True
TitleOther.SetFocus
Else
TitleOther.Enabled = False
PatientName.SetFocus
End If
End Sub

Private Sub MedicalAid_AfterUpdate()
If MedicalAid = "OTHER" Then
MedicalAidOther.Enabled = True
MedicalAidOther.SetFocus
Else
MedicalAidOther.Enabled = False
MedicalAidNumber.SetFocus
End If
End Sub

The Title one works perfectly as it should, i.e. enabling the Title-Other
field if "Other" is selected - this list is contained in a value list in the
tblPatient table; however the MedicalAid one is not working - it gets its
values from another table called tblMedicalAid where btw the description is
OTHER (caps). Am I missing something here?

Thank you for any help!

 

[PsyberFox]

Btw, I have also tried If MedicalAid.Value = "OTHER" Then but to no avail...
W

 

[mie via AccessMonster.com]

Firstly, you should use 'Me' when refer to controls on active form.

Example:
Me.TitleOther.Enabled = True

Try this,
Private Sub MedicalAid_AfterUpdate()
If Me.MedicalAid = "OTHER" Then
Me.MedicalAidOther.Enabled = True
Me.MedicalAidOther.SetFocus
Else
Me.MedicalAidOther.Enabled = False
Me.MedicalAidNumber.SetFocus
End If

 

[Marshall Barton]

I have the following three subs (amongst others):
Private Sub Form_Current()
If Trim(Title.Value) = "Other" Then
TitleOther.Enabled = True
TitleOther.SetFocus
Else
TitleOther.Enabled = False
End If

If MedicalAid = "OTHER" Then
MedicalAidOther.Enabled = True
MedicalAidOther.SetFocus
Else
MedicalAidOther.Enabled = False
End If
End Sub

Private Sub Title_AfterUpdate()
If Trim(Title.Value) = "Other" Then
TitleOther.Enabled = True
TitleOther.SetFocus
Else
TitleOther.Enabled = False
PatientName.SetFocus
End If
End Sub

Private Sub MedicalAid_AfterUpdate()
If MedicalAid = "OTHER" Then
MedicalAidOther.Enabled = True
MedicalAidOther.SetFocus
Else
MedicalAidOther.Enabled = False
MedicalAidNumber.SetFocus
End If
End Sub

The Title one works perfectly as it should, i.e. enabling the Title-Other
field if "Other" is selected - this list is contained in a value list in the
tblPatient table; however the MedicalAid one is not working - it gets its
values from another table called tblMedicalAid where btw the description is
OTHER (caps). Am I missing something here?

As mie said, you should use Me, but you don't absolutely
have to if the control name is not also used as the name of
something else.

I don't see anything in the code that is wrong. the caps
don't make a difference so it must be something about it
being a different table. It is rather odd to have updatable
controls bound to field in more than one table. Double
check that you can set the value manually directly in the
form's record source query's datasheet view without the form
being involved. If it's not updatable there, then try
adding the tblMedicalAid primary key field to the query's
select list. If that doesn't work, then I think you should
use the more standard approach and put the medical aid data
records in a subform.

 

[PsyberFox]

Morning Marshall,

I don't know if I understand your one concern properly. I don't have a field
that is bound to fields in more than one table. The MedicalAid is a combobox
(obviously drop-down on my form) of which the record source is the
tblMedicalAid table. When a user selects OTHER from this list, it should
enable the MedicalAidOther field, which is simply a text box where the Other
Medical Aid can then be entered. If the user selects a Medical Aid other than
OTHER it should disable the Medical Aid Other text box...

Hope this sheds some more light on my problem.

Rgds,
W

 

[Jon Lewis]

It sounds as if your combo has 2 columns. The first is probably hidden and
is the bound column which means that it's value (probably the MedicalAid ID)
is the one being stored in whatever table your Form is bound to. The second
is the description you see in the combo. You need to refer to this second
column in your if statement. The column's are indexed starting at 0 for the
first one so try:

If MedicalAid.Columns(1) = "OTHER" Then

HTH

Jon

 

[PsyberFox]

Thank you so much! Works perfect as you're 100% correct - there is a hidden
column (with ID).
Blud-e marvelous!
W