How would I make a field on a subform visible by what is selected in a combo box on the parent form?

Any help is greatly appreciated.

Hi Bill

Yes you can make a subform visible/not visible depending on a value selected in a combo box.

You would need to add the coding to the AfterUpdate event of the combo box. How you decide to code the response to the values selected, which will in turn change the subform visibility,depends on how many values are involved.

That sounds a bit obscure, but if you have a small number of values in your combo box then you may prefer to use

If/Then/Else/End If

With more values you may want to use Select Case.

For example

Sub cboMyCombo_AfterUpdate
On Error GoTo Err_cboMyCombo_AfterUpdate


Select Case Me!cboMyCombo

Case 1
Me!frmMySubform.Form.Visible = False

Case 2 To 8
Me!frmMySubform.Form.Visible = True

Case 9
Me!frmMySubform.Form.Visible = False

End Select

Exit_cmdMyCombo_AfterUpdate:
Exit

Err_cmdMyCombo_AfterUpdate:
MsgBox Error$
Resume Exit_cmdMyCombo_AfterUpdate

End Sub

This would work if your values were numerical - 1 to 9. If your values were words then you would put:

Case "Subform Visible"


Case "No Subform Visible"

Or if the subform was to be displayed for employees then you would have:

Case "employee"
Me!frmMySubform.Form.Visible = True

Case "client"
Me!frmMySubform.Form.Visible = False


Try it out! I hope it works for you!

Rich

Thank for your response Rich....I am 99% with you but still a little confused. I have a department combo box on a parent form with (3) department names for the user to select. When a certain department is selected....say "Payroll" I would like for (2) fields on a subform to be visible for data entry. However on that same subform there are (6) other fields that are visible all the time. Thanks for your help.

Sorry Bill!

I misread your first posting and took it to be a subform you wanted to hide (not a field on a subform).

However, you could create two subforms, one with all six fields and the other with only the two (when Payroll is selected). Your code you make one visible and the other invisible at the same time (and place them with superimposed one on top of the other in design view).