Make listbox visible after selection of another listbox

[lacey]

Okay then, after much trouble and confusion, I finally realized I need to use an Extended listbox in order to allow for multiple items to be selected from a list on my form (rather than the evil multiple selection combobox!).

However, now I am trying to figure out how to make one listbox (IndustryClassification) only be visible if the item "Industry" is selected in another listbox (TypeOfBusiness). Can anyone help out with the coding I can use for this in the AfterUpdate event of the listbox?

Thanks!

 

[missinglinq]

Private Sub TypeOfBusiness_AfterUpdate()

Dim varItm As Variant

 For Each varItm In TypeOfBusiness.ItemsSelected
   If TypeOfBusiness.ItemData(varItm) = "Industry" Then
     IndustryClassification.Visible = True
     Exit Sub
   Else
    IndustryClassification.Visible = False
   End If
 Next varItm

End Sub

In order for the formatting to be appropriate, as you move from Record to Record, you'll need the same code in the Form_Current event, as well.

Linq ;0)>

 

[lacey]

Thanks linq. However, when I use the code in both AfterUpdate and FormCurrent, it doesn't work right. First off, the IndustryClassification listbox is visible when the form is opened. When I try to select an item in the TypeOfBusiness list box, the IndustryClassification list disappears and won't appear again even if you select "Industry" in the TypeOfBusiness list.

Does the fact that they are extended selection listboxes make a difference? Any thoughts? Thanks again!

 

[missinglinq]

Sorry, should have told you to set the Visible Property to No/False in the Properties Pane for IndustryClassification.

You need to double-check the spelling of the names of the two Listboxes; it has to be exact. Other than that, all I can tell you is to save the file in 2007 or, better yet, 2003 format, Zip it up and attach it to a post. The code I gave you works for me during testing. Not sure about the extended selection listboxes making a difference; think I used Simple Multi-select. May need to do more testing.

Linq ;0)>

 

[lacey]

Here it is. Hope you can find something!

View attachment Contact DB.zip

 

[lacey]

P.S. Did set the visibility property of the listbox to No, didn't seem to help though.

 

[missinglinq]

...Does the fact that they are extended selection listboxes make a difference...

In the middle of watching Jeopardy, a short while ago, this comment came back to me, and the answer is, of course it does!

When the Multi-Select Property of a Listbox is set to anything other than 'None,' the Listbox has no Value Property. That's why you have to loop through all possible selections to determine which ones have actually been selected. And because there is no single selection, the Listbox cannot really have a Control Source. You can have one listed, in the Properties Pane, but that only applies if Multi-Select is set to 'None.' Because of this, Multi-Select Listboxes are actually Unbound and thus there is no way, using the Listbox, to have the Formatting be appropriate as you move from Record-to-Record. You can see the truth of this by making a selection or selections from the Listbox, then moving to another Record; the Listbox will still show the selection(s) that were made in the Previous Record, which is the usual behavior of an Unbound Control.

You can use the code I gave you in the TypeOfBusiness_AfterUpdate event, and it will immediately make the IndustryClassification Listbox Visible, if 'Industry' is selected, but the Formatting will not stay appropriate when moving to another Record. In order to maintain the appropriateness of Formatting, when moving from Record-to-Record, the selection of 'Industry' has to be in a Bound Control. When you move to another Record, you check that Bound Control, and set the Formatting based on whether or not 'Industry' is the Control. You can do this, but it will be more complicated.

To summerize:

1. In TypeOfBusiness_AfterUpdate, you need to Loop through the Listbox Selections and make the IndustryClassification Listbox Visible/Invisible, dependent on whether 'Industry' was selected (using the code I gave you for this)
2. Loop through the Listbox, again, and assign each selection made to a single Bound Textbox, each one separated by a delimiter, such as a comma or semi-colon
3. In the Form_Current event, use the Instr() Function to ascertain whether 'Industry' is found in the concatenated value held by the new Textbox, and Format the Visibility of IndustryClassification appropriately

You can work on this, and see how it comes along; maybe you can get it working from the summary above. If not, I'll stop back by in the morning and help you with it.

Linq ;0>

 

[lacey]

Back to work today after a nice long weekend! Thanks for your information, linq. After more thought given to the project, I think the complication level is way over my head and too much for me to take on at this point, given my current work load. We are preparing to do some trasitions and the database work is going to be shifting to an even less experienced person than me, so I am going to opt to use some bound check boxes on the form instead.

Thanks again for your help. Hopefully this info can help someone else out in the future!