Count of Combo Boxes with data (1 Viewer)

Mask · Jun 28, 2017

Hi all,

I have a form with about 40 combo boxes for different business categories. Users need to select a name from at least 15 of these boxes as votes in a contest.

Is there a way to have a count somewhere on the form to show the number of boxes which have a name entered to make it easier to keep track of when at least 15 have data?

Thanks in advance.

CJ_London · Jun 28, 2017

you would look through the controls and count where the value is not null. call this sub from every combo afterupdate event

Code:

Private Sub UpdateCounter()
dim ctrl as control
dim varcount as integer
 
varcount=0 
for each ctrl in me.controls
    if ctrl.controltype=acCombobox then
        if not isnull(ctrl) then varcount=varcount+1
    end of
next ctrl
me.counter=varcount
End Sub

alternatively you can use the control tag property to indicate which comboboxes to include in the count.

ashleedawg · Jun 28, 2017

Code:

Function CountCombos_NotNull() As Integer
Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acComboBox Then If Not IsNull(ctl) Then CountCombos_NotNull = CountCombos_NotNull + 1
Next ctl
End Function

ashleedawg · Jun 28, 2017

whoops, beat me to it

CJ_London's example even updates the counter control for you.

Mask · Jun 28, 2017

Thanks. Sorry - I need a little extra direction of where to attach this code please. What do I add to the form to display the number?

Galaxiom · Jun 28, 2017

Is the list of names in each of the combos the same or similar?

Mask · Jun 28, 2017

Galaxiom said:
Is the list of names in each of the combos the same or similar?

No - all different. Each combo is a business category with company names in each being different.

Uncle Gizmo · Jun 28, 2017

I wondered if an alternative method would be of interest?

As explained in this Video:- 0 - Adding a Checklist to MS Access

Instructions in a YouTube Playlist here:-
Nifty Access - Automatically add a Checklist

More Resources HERE:-
1) Single Check List

ashleedawg · Jun 28, 2017

Mask said:
Thanks. Sorry - I need a little extra direction of where to attach this code please. What do I add to the form to display the number?

Add a text box to the form and name it "Counter". Perhaps change the text box's label to something meaningful like "Items Selected". Push ALT+F11 to go to the VBA editor and paste in CJ_London's code at the top.

Next, you'll need to go to each combo box on the form and make it run the function each time that combo box is changed. (Unfortunately you'll have to set each one individually...)

Go into Design View on the form and double click the first combo box to bring up the box's Properties window. Go to the Event tab and find "On Change", Double-click or use the down-arrow to choose "Event Procedure" and then click the 3 dots on the right hand side of that field. This will bring you to the VBA editor, at the "On Change" event for that Combo Box. Type: Call UpdateCounter

Now, the UpdateCounter will run each time that Combo box is changed. Repeat the previous paragraph for the other combo boxes.

Mask · Jun 28, 2017

ashleedawg said:
Add a text box to the form and name it "Counter". Perhaps change the text box's label to something meaningful like "Items Selected". Push ALT+F11 to go to the VBA editor and paste in CJ_London's code at the top.

Next, you'll need to go to each combo box on the form and make it run the function each time that combo box is changed. (Unfortunately you'll have to set each one individually...)

Go into Design View on the form and double click the first combo box to bring up the box's Properties window. Go to the Event tab and find "On Change", Double-click or use the down-arrow to choose "Event Procedure" and then click the 3 dots on the right hand side of that field. This will bring you to the VBA editor, at the "On Change" event for that Combo Box. Type: Call UpdateCounter

Now, the UpdateCounter will run each time that Combo box is changed. Repeat the previous paragraph for the other combo boxes.

Thanks so much! Three issues:

1 - Counter starts on "1" when no box has an entry
2 - If an entry is deleted, counter only updates after another combo is changed
3 - Free text is permitted in the boxes so that a new business can be added if not in the list. Counter only updates after another combo is changed (to the correct count).

Count of Combo Boxes with data (1 Viewer)

Mask

New member

CJ_London

Super Moderator

ashleedawg

"Here for a good time"

ashleedawg

"Here for a good time"

Mask

New member

Galaxiom

Super Moderator

Mask

New member

Uncle Gizmo

Nifty Access Guy

ashleedawg

"Here for a good time"

Mask

New member

Similar threads

Users who are viewing this thread