Adding up entry selections from combo boxes on a form

[sheckay]

Hello. I have a form with a multiple combo boxes that have the same items to be selected. I'd like to know if there's a way to get a running total of the different selections? Say there's 10 combo boxes, and "bread", "milk", "butter", and sugar" are what can be chosen, combo box by combo box. I'd like to have 4 different totals boxes that carry a running total. "Bread Total", "Milk Total", "Butter Total", "Sugar Total". And the totals would be numeric of course. Can this be done?

[ranman256]

why are there so many combo boxes? Is it like:
cboBread, with different types of bread
and
cboMilk, with different types of milk?

if not, explain.
If not, why not 1 list box with everything, and 1 filter combo box, to filter item types in the list
combo values: Milk, breads, sugar,etc
this prevents MORE combo boxes getting added for more food types.

dbl-click the items in the listbox to add it to the cart. The cart form can subtotal by type.

[kd2017]

It can certainly be done but it's definitely not typical. I'm trying to think of a way to do it that would be as dynamic/flexible, and maintainable as possible but it's tough not knowing much about your project. Maybe you could explain big picture what you're trying to do? Is this like a meal planner?

[sheckay]

Thank you both. There are multiple boxes being shipped out per order. And they can and will vary from boxes to cartons to spools. And as they're entered/selected, I'd like a running total of what's been chosen.

[Bob Fitz]

Perhaps you will get more specific help if you post a copy of the db

[davegri]

We need a LOT more information.
For example, if you choose "Milk" from your "Milk" combo box, how do you know how many to total? What if Milk comes in different sizes - quarts, pints, gallons etc? How do you know what order is being fulfilled?

[kd2017]

It's not exactly what OP asked for but it's the least code dependent option I could think of. The only code required will be to requery the totals.

[kd2017]

Otherwise, another option that is more along the lines of what OP asked for, you can create a single generic function that you pass the value your trying to count and it will loop through a predefined list of comboboxs and count the matches. You can use this vba function in your text box control source. Something like.

Private Function ComboCount(match_value As String) As Long
On Error GoTo ErrHandler
    Dim combo_names As String
    Dim combo_name As Variant
    Dim combo_arr() As String
    Dim running_sum As Long
    
    running_sum = 0
    
    '-----------------------------------------------------------------------
    '---------------------------- ENTER YOUR FORM'S COMBO BOX NAMES HERE !!!
    '-----------------------------------------------------------------------
    combo_names = "Combo0,Combo2,Combo4,Combo6,Combo8,Combo10,Combo12,Combo14,Combo16,Combo18"
    
    '-----------------------------------------------------------------------
    
    
    combo_arr = Split(combo_names, ",")

    For Each combo_name In combo_arr
        If Me.Controls(combo_name) = match_value Then
            running_sum = running_sum + 1
        End If
    Next combo_name
    
ExitHandler:
    ComboSum = running_sum
    Exit Function
    
ErrHandler:
    MsgBox Err.Description, vbInformation, "Error #" & Err.Number
    Resume ExitHandler
    
End Function

Then in a summary text box you would set the control source to something like =ComboCount("bread"). This is a lot more error prone, more work, less manageable, less flexible than the I approach I previously posted.

[sheckay]

Sorry I haven't had a chance to thank any of you. Had a long hard day. But I should get a chance to try these out tomorrow. I'll keep you posted. Thank you all.