How to Set Visibility of TextBoxes Depending on a Multi-Select ComboBox (2010)

[SeadooRider]

I saw the post on how to make one textbox visible if a selection in the combobox was chosen. However I have a multi-selection combobox and each selection within this combobox needs to control the visibility of different textboxes if chosen.

I'm working in version 2010.

[ADezii]

1. Let us assume the following:
   1. You have 6 Text Boxes named Text1 thru Text6.
   2. You have a Multiselect 'List Box' named lstTest.
   3. lstTest has 3 Items, namely: Choice 1, Choice 2, and Choice 3.
   4. You set the Tag Property of all Text Boxes involved in this process = Test.
2. To now control the Visibility of the 6 Text Boxes based on the number of Item(s) selected:
   

   Dim varItm As Variant
   Dim ctl As Control
   Dim ctl2 As Control
    
   Set ctl = Me![lstTest]
    
   'RESET all Text Boxes with the Tag Property = 'Test' to False
   For Each ctl2 In Me.Controls
     ctl2.Visible = ctl2.Tag <> "Test"
   Next
    
   'No Item(s) Selected
   If ctl.ItemsSelected.Count = 0 Then Exit Sub
    
   For Each varItm In ctl.ItemsSelected
     Select Case ctl.ItemData(varItm)
       Case "Choice 1"
         'Make 1st & 3rd Text Boxes Visible
         Me![Text1].Visible = True
         Me![Text3].Visible = True
       Case "Choice 2"
         'Make 2nd & 3rd Text Boxes Visible
         Me![Text2].Visible = True
         Me![Text3].Visible = True
       Case "Choice 3"
         'Make 5th & Last Text Boxes Visible
         Me![Text5].Visible = True
         Me![Text6].Visible = True
     Case Else
       'Do nothing
     End Select
   Next varItm
    

[NeoPa]

I've updated your question for you as Access ComboBoxes do not have a MultiSelect property. As ListBoxes do, we've assumed that's what you meant to say ;-)

[SeadooRider]

Wow, thanks for the quick reply...Actually, I'm using Access 2010 and there is the option for multiSelect property with comboboxes...but I would think it will work the same as each item in the combobox has a value also.

[NeoPa]

(Blush) I beg your pardon in that case. I'll change it back to reflect your requirement.

[SeadooRider]

No prob. however, the textboxes did not become visible. I put the code under After_Update on the combobox. Is that where it should go?

It is working on the "change" event for the combobox but think I need If statements instead of case. If I choose one or two boxes, it makes all textboxes visible. Also, if they are deselected, the textboxes remain visible.
Just sayin ;)

[NeoPa]

SeadooRider:
I put the code under After_Update on the combobox. Is that where it should go?

I expect so, yes.

If it isn't working then I suggest you post the actual code you're using. Working with a simple description of what you've done, rather than any actual code, is unnecessarily confusing. Please include all relevant code - especially the procedure wrappings.

[SeadooRider]

Perhaps I don't understand the full code. I get that
(For i = 0 to Listbox1.listcount -5) is items from none up to the amount of items in the listbox. However, I get an error at .list(x) when I try to run the code. list is highlighted with the error Method or data member not found.

Private Sub listbox1 AfterUpdate()
      Dim str As String
      Dim i As Integer
      For i = 0 To ListBox1.ListCount - 5
          If ListBox1.Selected(i) Then
              str = & ListBox1.List(i) & ", "
          End If
      Next i
      text1 = str

[NeoPa]

There is no item (Either Property, Method or Event) of a ListBox called List.

At this stage I will link you to Before Posting (VBA or SQL) Code, as that has some very basic instructions that will help get rid of the simpler and more obvious errors in your code (What you learn from the link is likely to prove very helpful to you in future). Compiling your code is a first, and very important, step.

When you have done that, and if you still have any problems outstanding with it, please post back with your new code.

NeoPa:
Please include all relevant code - especially the procedure wrappings.

What I mean by the procedure wrappings is to make sure you include all the relevant code from the line :

Private Sub listbox1 AfterUpdate()

up to and including the line :

End Sub

Otherwise, you've described the issue very clearly so I was able to identify exactly what was causing your immediate problem. Unfortunately, there were various others too which you should find by compiling.

For i = 0 To ListBox1.ListCount - 5

FYI. This code (intentionally or otherwise) will actually ignore the last four (4) lines of your ListBox control. From your description it's not clear that you appreciated this point. Also, if you have less than four items contained therein, it will throw an error.

[ADezii]

@SeadooRider
The Code that you posted is flawed in several areas. It should look like something similar to:

Private Sub ListBox1_AfterUpdate()
Dim str As String
Dim i As Integer
 
If Me![ListBox1].ItemsSelected.Count = 0 Then Exit Sub
 
With Me![ListBox1]
  For i = 0 To .ListCount - 1
    If .Selected(i) Then
      str = str & .ItemData(i) & ","
    End If
  Next i
End With
 
'Remove Trailing Comma
str = Left$(str, Len(str) - 1)
 
Me![Text1] = str
End Sub

[Seth Schrock]

For clarification, the question asks about a combobox, and then it was repeated that the question was about a combobox and not a listbox in post #4. However, there is no Multi-select property in a combobox (A2010 is what I checked in) and the code provided by the OP is in the Listbox1 After_Update event. As Listbox1 would be the default name given to a new listbox control by Access and not what I would expect to be given by the OP to a combobox, I sense that this is in fact a listbox and not a combobox as originally thought by NeoPa in post #3. I have been following this thread for my own edification and would like this clarified so that I can understand what is happening better.

[NeoPa]

Very good point Seth. I'm a little shocked, if I'm honest, that someone would make such a claim after the idea had already been called into question. I don't often use my A2010 system, so I'm in the hands of others on this, but to have someone blatantly state something which is simply not true is a surprise. I suppose there may be some confusion in their mind as to what is meant be the terms involved.

Nevertheless, even if we assume we're dealing with a ListBox, the question remains. ADezii's code illustrates how to treat each selected item, but simply adds it to a string. We can help the OP with their code only after they post it again after removal of the main, more obvious, bugs.

[ADezii]

FYI, in Access 2007 you have have a Multi-Select Combo Box as long as it is Bound to a Multi-Value Field.

[Seth Schrock]

That is interesting ADezii. Access help for A2007 and A2010 doesn't list it as one of the properties available for the combo box, but does for the list box. I will have to try it out though with the multi-value field.

[NeoPa]

I don't suppose you have any links I could check on that ADezii? I searched, but finding the documentation for Office on the web is difficult as all the returned pages seem to be forum questions. These don't typically help when trying to confirm a technical point, but I found nothing except loads of comments of MVPs and other experts explaining that there is no Multi-Select property for ComboBoxes.

A definitive link would be good for me, but also fairly essential for the thread now the point has been raised again.

[ADezii]

This is tricky since there appears to be no Documentation on this per say, it exists on in a specific circumstance, and there is no way to explicitly set the Multi-Select Property of the Combo Box. Try the following to get a visual:

1. Create a Form Bound to a Table containing a Multi-Valued Field.
2. Create a Combo Box on this Form and Bind it to the Multi-Valued Field.
3. All Multi-Values will now appear in this Drop Down Combo.
4. The mechanism for selecting Multiple Values is different, namely: Check Boxes as opposed to Line Select.
5. In case you are wondering, executing:

   Debug.print Me![<Combo Box Name>].ControlType

   will return

   111

   or the Intrinsic Constant for

   acComboBox

   .
6. If I am somehow misinterpreting this, kindly let me know.

[NeoPa]

As there is limited documentation (and I don't currently have access to such a system), can you post whether or not the ComboBox control you created has a property called MultiSelect, and whether or not it has a property called ItemsSelected. These are the two properties that are normally used to handle multiple selection in a Multi-Select ListBox.

PS. I don't think you're misinterpreting this any more than any of us is. We want to get to the bottom of this definitively and you're providing information that might help (and that none of us knew about until you did so). If it turns out to be irrelevant then so be it, but we need to check it first to determine that. None of us knows already so we're all in the same boat together.

[ADezii]

@NeoPa

can you post whether or not the ComboBox control you created has a property called MultiSelect

Apparently it does not

whether or not it has a property called ItemsSelected.

Yes, but it does not appear to work in a similar vein as with a List Box

P.S. - Interestingly enough, ItemsSelected can be mimicked as such:

Dim varItm As Variant
Me![Combo1].SetFocus
 
varItm = Split(Me![Combo1].Text, ",")

[NeoPa]

Thanks ADezii. So the .Text property reflects a text string of all the individual items concatenated together as a comma separated list. That's straightforward enough, though not a Mulit-Select ComboBox I would suggest.

Nevertheless, considering all of us have been somewhat confused by what is and what isn't, it's perfectly understandable that anyone may get that wrong.

At this point, considering so much confusing conversation has passed under the bridge since SeadooRider has last posted, it seems only sensible to explain what is now expected from them before we can continue to help with their precise problem.

1. Firstly, follow the instructions in post #9 to tidy up the basics of the code, before then submitting what we need to see.
2. Confirming exactly what they mean when they indicate they are working with a Multi-Select ComboBox. One would expect a ComboBox with the setup that ADezii has described or a Multi-Select ListBox. As we've seen already, the code for, and the handling of, the two would be very different.

May I add congratulations to all those who have contributed to this thread so far. I doubt any of us individually could have achieved as much as we've managed together.