By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

main form total

P: 80
Hello. I'm having problems understanding how to calculate a total on my main form based on the items in my subform. I've tried runing update querys and thats not working, and Ive tried just calculating within the form, and thats not working correctly either.

My main form is called Form1, and my subform is testquery2_subform.

Whats happening is, the customers information(first name, last name, etc...), and the delivery/pickup dates, is on my main form. And my subform has the items that make up the order, tents, tables, chairs, etc...

For example if a customer orderd 5 tables and 30 chairs, there would be 2 rows in the subform.

I have gotten to the point where I put an onUpdate event on the subform where if I update a record it will mulitply the number or chairs or whatever by 2 and put that value into my main form. The problem is this only works for 1 row, I can't get it to update from all the records in the subform (that match the specific order number)

This is what I have now...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_AfterUpdate()
  3. Dim sendval As String
  4. Dim val1 As String
  5. Dim val2 As String
  6. Dim val3 As String
  7. Dim val4 As String
  8. Dim val5 As String
  9. Dim val6 As String
  10.  
  11. If Not IsNull(txtNumTables) Then
  12. val1 = txtNumTables
  13. End If
  14.  
  15. If Not IsNull(txtnumChairs) Then
  16. val2 = txtnumChairs
  17. End If
  18.  
  19. If Not IsNull(txtnumT_Cloths) Then
  20. val3 = txtnumT_Cloths
  21. End If
  22.  
  23. val4 = "2"
  24. val5 = "4"
  25. val6 = "1"
  26.  
  27. sendval = 0
  28. sendval = sendval + (Val(val1) * Val(val4))
  29. sendval = sendval + (Val(val2) * Val(val5))
  30. sendval = sendval + (Val(val3) * Val(val6))
  31.  
  32. Forms![Form1]![txtTotal] = sendval
  33.  
  34. End Sub
  35.  
  36.  
Thanks,
Scotter
Aug 20 '07 #1
Share this Question
Share on Google+
4 Replies


lee123
100+
P: 556
well the only way i can tell you to do is when i need a total for my orders is have a header and a footer in the footer copy and paste the textbox of whatever you need a total on and paste it in the footer then in the properties of the textbox find the controlsource box an put this in it.

=sum([what ever the text box is called])

lee123

i think this is what your talking about. if not disregaurd this message.
Aug 20 '07 #2

P: 80
Hi lee, I did make a footer, and put a text box down there to hold the total. With the textbox down there i can calculate a total and the pass it to my main form and it is actually working good. But the problem now is, when ever i modify something on the subform, everything updates, but only the value for that record is passed into the main form, instead of the values that match the specific order number I'm looking at.

To help you understand what I'm talking about, this is what my subform looks like

Expand|Select|Wrap|Line Numbers
  1. Order # |  Item   |   tent Size  |   Tables  |   Chairs   | Table Cloths |
  2. --------------------------------------------------------------------------
  3. 999     |Tent     | 20 x 20      |           |            |              |
  4. 999     |Tables   |              |150        |            |              |
  5. 999     |Chairs   |              |           |600         |              |
  6. 999     |T Cloth  |              |           |            |150           |
  7.  
What I'm talking about is, if i changed the 150 tables to 200, instead of taking 200*(price of tables)+600*(price of chairs)+150*(price of table cloths), it would just do 200*(price of tables), so I wouldnt be getting the full total.

Any ideas why I can't get everything to addup?
Aug 20 '07 #3

lee123
100+
P: 556
is any of this based on any query or is it based in code, because what is your subform based on. another word is how did you set up you subform.
please explain.


lee123
Aug 20 '07 #4

P: 80
Yes, both my main and subform are based on queries, linking by order number. Sorry for not mentioning eariler. Also I figure I should mention, I have seprate tables for the price of each item. For example, tents come in 4 sizes, 20 x 20,20 x 30, 20 x 40, 30 x 30. I have a seprate table that has a price for each size. Same is with table cloths, I have different colors with different prices for each of thoose.
Aug 22 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.