Count selections from Multivalued list

[AceBK]

I am trying to count how many boxes are checked in my multivalue list box. The list box is a lookup from a different table. All I am trying to do is count how many items are picked from the list. I thought that this would be fairly simple, but I am not getting very far. I read somewhere that since this is just a lookup from a different table, the 'ItemsSelected' function won't work. I know some, but very little about SQL and VB. Please help me. I am using Access 2010 and the name of my list box is 'Products'.

 

[John Big Booty]

I found the following here;

Function CountSelected_MultiSelect() As Long
[COLOR="SeaGreen"]'Counts All Selected values in a listbox[/COLOR]
    
    Dim varItm As Variant
    Dim lngCount As Long
    Dim ctl As Control

    Set ctl = Me.lstMultiSelect
    
    For Each varItm In ctl.ItemsSelected
        lngCount = lngCount + Abs(ctl.Selected(varItm))
    Next varItm
    
    CountSelected_MultiSelect = lngCount

End Function

 

[AceBK]

So I went and followed all the links that you provided, but I am not sure this is quite what I am looking for. Let me try to paint my picture a little differently. I have a multi-select box on a form. There are 54 different items on the list. What I would like to have is maybe in a query or on the table to have a field that will populate the count of the list box. I don't know if this works or not.

 

[John Big Booty]

Have a look at the attached sample.

Check out the On Load event of the form and the On Click event of the List box.

Also note that I have used the function in my previous post and another from the same link as Private functions so they can only be referenced from they form they reside on.

 

[AceBK]

John, I followed your link and I got it to work. My first problem was I was using a combo box and not a list box so as soon as I changed that, it worked. However, I am having a hard time getting this count value onto my report. For each record I need a count and I am not sure how to get this count value onto my report. So my form is called 'changeovers' and the field that gets the value for the count is called 'noofchangeovers'. I would like my report to record the number of changeovers for each day but all I can get it to do is transfer the current number showing up on the current record that is open on the form.

 

[John Big Booty]

Have a look at the DCount() function. That should do what you are after.

 

[AceBK]

I don't think DCount is quite what I am after. I read your link and I understand the DCount to be a count of records in a series of records. What I am after is the count of the list box to show up on the report for each record. I suppose what might work, if possible, is to count the values of the listbox either on a query or on the table. This will give the count of the list box its own field which I could then in turn put on the report. Basically what I am after is in each record there are a certain number of changeovers which is captured by selecting 'x' amount of items in the list. I would like to have this number show up on my report for each record? Does this make sense to you? Thank you for helping me with this issue.