Access text box visible depending on combo box value

0
votes

I'm trying to set up a form with in access so that depending on what Value the user picks from Company combo box (Employers Rep or Contractor) depends on what set of contract Actions combo boxes and Clauses text boxes are shown. With the code below I have been able to hide them though not able to get them to become visible again. 

 Private Sub Company_Change()
  Select Case Trim(Me.Company.Text)
  Case "Employers Rep"
   Me.ER_Action.Visible = True
   Me.ER_Action2.Visible = True
   Me.ER_Clause.Visible = True
   Me.ER_Clause2.Visible = True
  Case "Contract"
   Me.ER_Action.Visible = True
   Me.ER_Action2.Visible = True
   Me.ER_Clause.Visible = True
   Me.ER_Clause2.Visible = True
  Case Else
   Me.ER_Action.Visible = False
   Me.ER_Action2.Visible = False
   Me.ER_Clause.Visible = False
   Me.ER_Clause2.Visible = False
   Me.Con_Action.Visible = False
   Me.Con_Action2.Visible = False
   Me.Con_Clause.Visible = False
   Me.Con_Clause2.Visible = False
End Select

Any help would be most appreciated. Thanks A.S.H this code now works. Changed this Select Case Me.Company for this Select Case Trim(Me.Company.Text).

2
formsms-accessvba
"I have been able to hide them though not able to get them to become visible again." - Is that when you change the Combo Box again for the current record, or is it when you move to another record or create a new one? – Gord Thompson
Both, I have noticed that the code doesn't seem to be running anything before it gets to Case Else So if I change the false to true on any line after Case Else, it makes the text boxes visible. – BenC
Is there anything else in you combobox to select other than "employers rep" and "Contract"? – MatthewD
Did you check for the precise text values in the combo: case characters, white spaces, etc? You need that they match exactly the compared Strings. also, try Select Case Trim(Me.Company.Text) – A.S.H
I would change the combo box to have two columns, one with a unique ID number and the other with text, so that you code does not rely on the text remaining the same (or not having spaces!) Me.Company.Text would be changed to Me.Company.Value – HarveyFrench

2 Answers

0
votes

you may like to consider this too.

 Private Sub Company_Change()

 Select Case Trim(Me.Company.Text)
  Case "Employers Rep"
   blnShowit = true

  Case "Contract"
   blnShowit = true

  Case Else
   blnShowit = false

End Select


   Me.ER_Action.Visible = blnShowit
   Me.ER_Action2.Visible = blnShowit

   Me.ER_Clause.Visible = blnShowit
   Me.ER_Clause2.Visible = blnShowit

   Me.Con_Clause.Visible  = blnShowit       
   Me.Con_Clause2.Visible  = blnShowit
0
votes

put your code in the Afterupdate event of your combo-box. that would do the trick