Making a Field Visible/Invisible

[Jonathan Smith]

Using Access 2002, in a Windows XP Environment:

I have the following fields:

cmbActivityCode
cmbClient
cmbStudent

If cmbActivityCode is a certain Set of Values, I want cmbClient to be
visible. If cmbActivityCode is another Set of Values, I want smbStudent
to be visible.

Or, if I could change the source of the cmbClient field, based on the
value of cmbActivityCode ...

if cmbActivityCode = 1001 then cmbClient is from tbl3001
if cmbActivityCode = 1003 then cmbClient is from tbl0502

I think I may have asked this question previously, but I cannot find the
answer ...

HELP, please!!!!!!!!!!!!!!!!!!

 

[MikeC]

Below is one way to do it. You'll need to add your own
error handling, etc...

Private Sub cmbActivityCode_AfterUpdate()

Dim ctl1 As Control
Dim ctl2 As Control
Dim ctl3 As Control

Set ctl1 = Me!cmbActivityCode
Set ctl2 = Me!cmbClient
Set ctl3 = Me!cmbStudent

Select Case ctl1
Case Value1, Value2, Value3
ctl2.Visible = True
ctl3.Visible = False
Case Else
ctl2.Visible = False
ctl3.Visible = True
End Select

Select Case ctl1
Case 1001
ctl2.RowSourceType = "Table/Query"
ctl2.RowSource = "tbl3001"
Case 1003
ctl2.RowSourceType = "Table/Query"
ctl2.RowSource = "tbl0502"
Case Else
ctl2.RowSourceType = "Table/Query"
ctl2.RowSource = "SomeTable"
End Select

Set ctl1 = Nothing
Set ctl2 = Nothing
Set ctl3 = Nothing

End Sub