Auto-populated and Calculated Fields in Form Help

jessebh2003

New Member
Joined
Feb 28, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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)

AccessForm.PNG



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

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,310
Office Version
  1. 365
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

New Member
Joined
Feb 28, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 19, 2006
Messages
1,310
Office Version
  1. 365
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

New Member
Joined
Feb 28, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 19, 2006
Messages
1,310
Office Version
  1. 365
Try using the AfterUpdate event of the last combo in your list.
How do the text boxes with point values get populated?
 

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,310
Office Version
  1. 365

ADVERTISEMENT

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.

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

Form design:
jesseMockup.PNG
jesseMockup.PNG


Form with values:
FormJessewithValues.PNG
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,058
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 28, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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.
jackd_test.JPG
 

Attachments

  • jackd_test2.JPG
    jackd_test2.JPG
    15.9 KB · Views: 4
  • jackd_test3.JPG
    jackd_test3.JPG
    47.7 KB · Views: 4

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,058
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,588
Messages
5,625,668
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top