Trouble with visible/non-visible combobox

[lacey]

Hi all,

I have a combobox on my form called TypeOfBusiness, with fields including Corporation, Education, Industry, Non-Profit. In order to make a second combobox called IndustryClassification appear when Industry is selected from the TypeOfBusiness combobox, I have used the following code:

Private Sub Form_Current()
If Me.TypeOfBusiness = "Industry" Then
    Me.IndustryClassification.Visible = True
Else
    Me.IndustryClassification.Visible = False
End If
End Sub
 
Private Sub TypeOfBusiness_AfterUpdate()
If Me.TypeOfBusiness.Column(1) = "Industry" Then
    Me.IndustryClassification.Visible = True
    Me.LabelIndustryClassification.Visible = True
Else
    Me.IndustryClassification.Visible = False
End If
End Sub

It works just fine. However, when a user selects both, say, Industry and Education, the IndustryClassification combobox will not appear. Any ideas of what code I can use to ensure the IndustryClassification box will appear regardless if another box is checked in addition to Industry? I am wondering if it has to do with the Column attribute but I'm really not sure what to try at this point.

Thanks in advance for all of your wisdom!

 

[David R]

So Me.TypeOfBusiness is a multi-select Combobox?

Try something similar to:

Dim varItem As Variant
Dim found as Boolean

If Me.TypeOfBusiness.ItemsSelected.Count Then
            For Each varItem In Me.TypeOfBusiness.ItemsSelected
                If Me.TypeOfBusiness.Column(0, varItem) = "Industry" Then
                    found = true
                    Exit For
                End If
            Next varItem
        
           Me.IndustryClassification.Visible = found 'True if True
End If

                    Me.IndustryClassification.Visible = False 'nothing at all selected

 

[lacey]

Correct, it is a multi-select combobox.

Thanks for the suggestion! However, the code didn't work. It simply made the IndustryClassification invisible and didn't appear when any box was selected.

 

[David R]

Correct, it is a multi-select combobox.

Thanks for the suggestion! However, the code didn't work. It simply made the IndustryClassification invisible and didn't appear when any box was selected.

Try setting a breakpoint in your code (click off to the left, a red dot should appear) at the first or second Dim line, and then step through with F5. Hover your variables in each line as the breakpoint goes past. I wrote that code on the fly from a multiselect listbox so the syntax may be slightly different, but let's first make sure that found is being set True at all!

:edit: nevermind, I'm an idiot, I mean failed genius. Change the end of your code to

Else
                    Me.IndustryClassification.Visible = False 'nothing at all selected
End If

I was having the code do all that work and then setting it to Invisible regardless. This should work better.

 

[lacey]

Try setting a breakpoint in your code (click off to the left, a red dot should appear) at the first or second Dim line, and then step through with F5. Hover your variables in each line as the breakpoint goes past. I wrote that code on the fly from a multiselect listbox so the syntax may be slightly different, but let's first make sure that found is being set True at all!

Hmm. It wouldn't allow me to make a breakpoint by the dim lines. Not even sure what that means or what the purpose of that is. Always more to learn about Access...

:edit: nevermind, I'm an idiot, I mean failed genius. Change the end of your code to

I tried that and it didn't help! I am making an assumption that you intended for the code to be put in the AfterUpdate of the combobox, as well as the Form_Current event. Is that an incorrect assumption?

 

[lacey]

P.S. isn't that last bit of code you posted the same as what you posted originally? I don't see a difference.

 

[David R]

The Else is added, and the End If is later.

What it said before was "see if anything is selected and if any of the selected entries are Industry; then set it to invisible anyway". Now it says "see if anything is selected; if yes, see if any of the selected entries are Industry; if yes, set it to Visible. If none are set to Industry, make it Invisible. Lastly if nothing was selected at all, set it Invisible".

 

[David R]

I tried that and it didn't help! I am making an assumption that you intended for the code to be put in the AfterUpdate of the combobox, as well as the Form_Current event. Is that an incorrect assumption?

You are correct. I usually put it in just one (usually Form_Current) until I'm done testing, then copy it to AfterUpdate.

 

[lacey]

Gotchya. Thanks for pointing that out.

Here is what I used:

Private Sub Form_Current()
Dim varItem As Variant
Dim found As Boolean
If Me.TypeOfBusiness.ItemsSelected.Count Then
            For Each varItem In Me.TypeOfBusiness.ItemsSelected
                If Me.TypeOfBusiness.Column(0, varItem) = "Industry" Then
                    found = True
                    Exit For
                End If
            Next varItem
        
           Me.IndustryClassification.Visible = found 'True if True
Else
                    Me.IndustryClassification.Visible = False 'nothing at all selected
End If
End Sub

in the Form_Current event. Buuut... it didn't work What's missing? Did I copy anything wrong? Reviewing all again...

 

[missinglinq]

You originally had

If Me.TypeOfBusiness.Column(1) = "Industry" Then

but are now using

If Me.TypeOfBusiness.Column(0, varItem) = "Industry" Then

Which Column is 'Industry' actually in, the first Column or the second Column?

Linq ;0)>

 

[David R]

Breakpoint may not be possible on Dim statements, I forget. If not just move it down to the "If Me.TypeOfBusiness.ItemsSelected.Count Then" line.

missinglinq is probably right about the column thing (remember that column count starts at 0, not 1).

 

[lacey]

The items listed in the combobox are multiple selection, meaning they are selected with check boxes. So, would the checkbox be considered the first column (0) and the actual word "Industry" considered the second column (1)? Or are they considered the same column (0)? Either way, I tried changing it to 0 and that didn't work either.

I also tried moving the breakpoint to the "If Me.TypeOfBusiness.ItemsSelected.Count Then" lines. When I go back to the form view and try selecting Industry, it automatically brings me to the event editor with the "If Me.TypeOfBusiness.ItemsSelected.Count Then" line in the AfterUpdate event highlighted yellow. I have zero experience using breakpoint lines... any words of wisdom on what's going on?

 

[lacey]

Wow... it's too early for me this morning. Just made the connection in my head that the yellow is the debugger. It didn't give me any error message, though, just brought up the debugger with that line highlighted.

I have no clue where to go from here. Any suggestions??

 

[missinglinq]

...The items listed in the combobox are multiple selection...they are selected with check boxes...

You're talking about a Combobox that is Bound to a Multi-Value Field, and that's an entirely different thing from a standard Combobox! Also something that very, very few experienced developers here are using and thus have little or no expertise with. For the reasons that most of us shun them:


The Evils of Lookup Fields in Tables

What I do see in the line

If Me.TypeOfBusiness.ItemsSelected.Count Then

is that you're treating TypeOfBusiness as if it were a Boolean Field, and Access is reading it as

If Me.TypeOfBusiness.ItemsSelected.Count is True Then

You would expect it to be something like

If Me.TypeOfBusiness.ItemsSelected.Count = 2 Then

or

If Me.TypeOfBusiness.ItemsSelected.Count > 10 Then

What, exactly, are you trying to test for with this line?

Lastly, the line

If Me.TypeOfBusiness = "Industry" Then

is interpreted, by Access, as saying

If Me.TypeOfBusiness.Value = "Industry" Then

and if a multi-select Combobox is like a multi-select Listbox, it doesn't have a Value Property!

Linq ;0)>

 

[lacey]

Oh wow, thanks for that info! I didn't realize how troublesome they could be. Would it be a better option to have a field in the table for each of my current combobox options, and then add them to the form as a checkbox??