Auto-populated and Calculated Fields in Form Help

[jessebh2003]

Hi everyone! This is my first post.

I'm not too savvy with MS Access, but I'm trying. I'm building a form and based on the value of the combobox I want a number to populate in an adjacent text box. Then I want the value of all the text boxes to add up to give me a total. I've watched numerous YouTube videos and read various message boards but all to no avail. Can anyone please help me? Attached is an image of my form.

Auto-populating
My form has three columns (Activity Length, Description, and Point Value). In the Description column, each combobox is a drop down of different values, which are pulled from a table. I want the textbox in the Point Value to auto-populate based on the combo box selection. The point values are also in a table. I've tried =Dlookup but I think I'm doing something wrong.

Example: The first row of my table is Activity Length (ActivityLength_Label), combobox (ActivityLengthDescription), text box (ActivityLengthValue)
Combobox values pull from table (Source Data), column ActivityLength (≤ 3 hours, 4 - 8 hours, 9 - 16 hours, ≥ 17 hours).
Each entry has a corresponding value in column ActivityLengthValues (1, 2, 3, 4).
I want the text box (ActivityLengthValue) to populate from table (Source Data), column ActivityLength based on the selection in combobox (ActivityLengthDescription)

Calculating
Once all of the values populate in the Point Value column, I'd like for the values to add together in text box (ActivitySizeTotalPoints). I've tried:
=sum([ActivityLengthValue] + ...) which results in #Error
=CDbl(Nz([ActivityLengthValue],0))+CDbl(Nz([... which results in #Name?

Example: Here's what I'd like to happen if possible:
(ActivityLengthValue) + (NumberParticipantsValue) + (NumberFacultyValue) + (NumberGrantsValue) + (NumberExhibitorsValue) + (MarketingSupportValue) + (PlanningCommitteeValue) + (TargetAudienceValue) + (ProvidershipValue) + (LocationValue) = (ActivitySizeTotalPoints)

I really appreciate any help that can be shared. Thanks!

 

[jackd]

Here's a simple mock up of 2 combos, each has values 1,2,3,4
the textbox (text4) has the sum of the values selected in cbo0 and cbo2
I used the afterupdate of cbo0,but you could add logic to ensure all combos had values or whatever triggers your summation.

Private Sub Combo0_AfterUpdate()
Me.Text4 = val(Me.Combo0) + val(Me.Combo2)
End Sub

It may give you some ideas.
Good luck.

 

[jessebh2003]

Thanks for the information, however, I'm not sure I follow what you mean. I get to enter it in a VBA, but not sure what the other fields mean.

 

[jackd]

If combo0 and combo2 were two combos where you have points,'then
Me.Text4 = val(Me.Combo0) + val(Me.Combo2)
would put the sum of the numeric values into text4.

 

[jessebh2003]

Okay thanks. I tried it replacing "combo#" with my field names, but it didn't work.

Private Sub ActivitySizeTotalPoints_AfterUpdate()
Me.ActivitySizeTotalPoints = Val(Me.ActivityLengthValue) + Val(Me.NumberParticipantsValue) + Val(Me.NumberGrantsValue) + Val(Me.NumberExhibitorsValue) + Val(Me.MarketingSupportValue) + Val(Me.PlanningCommitteeValue) + Val(Me.TargetAudienceValue) + Val(Me.ProvidershipValue) + Val(Me.LocationValue)
End Sub

 

[jackd]

Try using the AfterUpdate event of the last combo in your list.
How do the text boxes with point values get populated?

 

[jackd]

I have made another mockup. There are 4 combo boxes -comboA, comboB, ComboC, ComboD. Each can take on values 1,2,3 or 4.
In the after update of comboD, the total values of all combos is assigned to text8.

Private Sub ComboD_AfterUpdate()
Me.Text8 = val(ComboA) + val(ComboB) + val(ComboC) + val(ComboD)
End Sub

Form design:

Form with values:

 

[Micron]

Can you not make the numeric value and text part of the combo list (both are seen when it drops down) but make it display the number? Then you don't need 9 or 10 textboxes for values when the combo can show that. Also, you'd need that many subs (one for each combo) to do it that way. Alternatively, you can write one function to add them all together. Then in design view, you grab all of the combos and in the property sheet After Update event you write YourFunctionNameHere(). Each one will then call the same single function.

 

[jessebh2003]

Hi jackd,
I recreated your mockup - thank you - however, nothing happened to the Text8 box. I set the combo boxes to pull from a table with values 1, 2, 3, and 4 and that works but not that calculation.

 

[Micron]

If you selected 4 first you will get nothing because the other 3 combos don't have the same event. That's why I suggested one function for this rather than writing the same thing 4 times.
If you selected 4 last, then perhaps you have more than 1 column in the combo, in which case its value may not be what you see.
If neither of those are true, put a break on the first line of the code, change the value of combo4 to trigger the event and step through until the last line is highlighted and mouse over the control names to see what values they hold.