show and hide a combo box

[vX987]

Hi!

I have a combo box 1 with a list of choices
let's say:

Blue
Green
Red
Purple

are my choices

What I want to happen is when I choose a certain choice in combo box 1, have another combo box 2 appear with more choices.

Example, when I choose Blue in combo box 1, automatically another combo box 2 would appear with a list of choices.

BUT I don't want the combo box 2 showing on the form AT ALL if I chosed Red as my choice in combo box 1. Meaning, Blue would be the only choice in combo box 1 that has other subcategories and combo box 2 only appears when Blue is chosen otherwise it stays hidden.

I've already tried using this code
Me![combo 2].Visible = (Me![combo 1] = "Blue")
in the AfterUpdate event of combo box 1 and in the On Current event of the form, but it doesn't seem to work. I only see combo box 1 and when I choose Blue as my choice for combo box 1, combo box 2 does not appear at all.

Am I putting my code in the wrong places??? Help please? THANK YOU!

 

[Dennisk]

I think the second part of your statement is not returning true/false as it may be taken as an assignment. add a debug.print statement

debug.print (Me![combo 1] = "Blue")

to see what is being returned

or redevelop you code as an If ...Then....Else

 

[missinglinq]

In Design View select the Combo2 and goto Properties - Format and set the Visible Property to NO. Then in code:

Private Sub Combo1_AfterUpdate()
If Combo1 = "blue" Then
 Combo2.Visible = True
Else
 Combo2.Visible = False
End If
End Sub

If you want to have Combo2 visible if you return to a record, depending on what color was previously chosen, you would have had to store the Combo1 value in a control on your form; let's call it ChosenColor. Then

Private Sub Combo1_AfterUpdate()
If Combo1 = "blue" Then
 Combo2.Visible = True
Else
 Combo2.Visible = False
End If
End Sub

Private Sub Combo1_BeforeUpdate(Cancel As Integer)
 Me.ChosenColor = Combo1
End Sub

Private Sub Form_Current()
If Me.ChosenColor = "blue" Then
 Combo2.Visible = True
Else
 Combo2.Visible = False
End If
End Sub

Good Luck!

 

[boblarson]

Also, you may be putting your code in the wrong place (the code was wrong to begin with, but from your description you may not know exactly where to put it, so I am enclosing screenshots). Now, this was created for someone else and the events are different, but the concept is the same, so just remember to use the correct event.

 

[vX987]

missinglinq,

"If you want to have Combo2 visible if you return to a record, depending on what color was previously chosen, you would have had to store the Combo1 value in a control on your form; let's call it ChosenColor."

how would I set a control on my form for the combo1 value?

In combo 1, there is only ONE color that has subcatergories all the rest of the options in combo1 will not have subcatergories, is it still necessary that I have a control?

THANKS!

 

[vX987]

thank you boblarson for double checking,


but... I am putting it in the right place Thanks.

 

[boblarson]

I created a sample for you. Check this out and see if it does what you want and then look around carefully to see what I did.

 

[vX987]

boblarson, THANK YOU SO MUCH for making me a sample. It is exactly what I wanted, however, after following exactly what you've created it still does not seem to work in my database. I've done numerous trials and have looked everywhere to make sure mines is the same as yours and it is exactly the same, I've changed the table and form values in the code to match mines ....., but still... no success.

What I keep getting is an Enter Parameter Value for [Forms]![DDDsubPtDiagnosis]![cboDiagnosis1] in my database.


There is, however, a slight difference to mines then the one you created. My combo boxes would be in continuous forms and not single form like yours. Also, my form that contains these combo boxes will actually be a subform within a bigger form..... which all shouldn't matter right??

Question, this event was placed in the After Update section of combo1

If Me.cboColor.Column(2) = True Then
Me.cboSubColor.Visible = True
Else
Me.cboSubColor.Visible = False
End If
Me.cboSubColor.Requery

Do I have to change the Column(2) value or no? What is this value?

 

[boblarson]

subform syntax is definitely different. You will find this to be a good reference:

http://www.mvps.org/access/forms/frm0031.htm

 

[AdvocateNJS]

Something similar

So I'm working on something very similar but having a different problem. I would like my selection in cbo1 to determine whether cbo2 is visible. I have the code written to the point where when I select the value that makes cbo2 visible it is, but then it remains visible in all my other records. Its not specific to the one record.

this is what my code looks like:

Option Compare Database

Private Sub Combo35_AfterUpdate()
If Me.Combo35 = "3" Then
Me.OLSInvestigator.Visible = True
Else
Me.OLSInvestigator.Visible = False
End If
Me.OLSInvestigator.Requery

End Sub


Any suggestions/help would be much appreciated.

Nick

 

[boblarson]

So I'm working on something very similar but having a different problem. I would like my selection in cbo1 to determine whether cbo2 is visible. I have the code written to the point where when I select the value that makes cbo2 visible it is, but then it remains visible in all my other records. Its not specific to the one record.

this is what my code looks like:

Option Compare Database

Private Sub Combo35_AfterUpdate()
If Me.Combo35 = "3" Then
Me.OLSInvestigator.Visible = True
Else
Me.OLSInvestigator.Visible = False
End If
Me.OLSInvestigator.Requery

End Sub


Any suggestions/help would be much appreciated.

Nick

Put the same code in the form's ON CURRENT event too.

 

[AdvocateNJS]

Thanks!

So simple. And so fast! Thanks Bob. You can expect some more questions in the near future...

Nick

 

[AdvocateNJS]

Told you

Told you I would have another question. On the same topic. If, on the same form I want to run the same code on different controls, how do I include them all in the on current event procedure of the form? Or can I?

 

[boblarson]

yes you can just have them in separate If...Then...Else statements within the same event.