473,320 Members | 1,883 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

main form total

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
4 1826
lee123
556 512MB
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
Scotter
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
556 512MB
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
Scotter
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

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

Similar topics

0
by: Ellen Manning | last post by:
I've got an A2K form with a subform based on a query. This query has a checkbox and an amount field and returns records if checkbox is checked. I Dsum the amount field and display on the main...
1
by: Mike | last post by:
I have an access data project (MS-SQL Svr 2000 backend and MS-Access 2003 Front End) that has a sub-form based on a query view. On the sub form I have several column's in which I have a running...
3
by: virtualgreek | last post by:
Dear all, I have a scenario that is driving me nuts. (MS Access 2003) I have a form/subform (Continuous form) where it gets its data from tables Order and Order_Details. In the footer...
2
by: post | last post by:
Sorry that this is rather basic. But I have tried the various suggestions given in this group and cannot get them to work. I have a main form, for simplicity called "Main". On it are two...
16
by: mlcampeau | last post by:
I'm fairly new to this so I'll try to be as clear as possible. I have a database that stores employee information and all job titles available. I have a main form that is based from a query and...
4
by: drago | last post by:
Hi guys, I will be quick this time... Just want to know how we can calculate generic values in the same column. I am using invoice form with a services subform . I have a column of 'total' in subform...
5
JustJim
by: JustJim | last post by:
I must have Friday-itis! I have a main form which has a sub-form. The sub form is shown as a datasheet, but in design view I have a control on the Form Footer which totals a field on the Detail...
1
by: vvasnm | last post by:
Hi This is surya, I am struggling with one issue regarding the updation of the value in the sub form ,when the related data has been deleted from the data sheet view of the main form. I am...
12
D Giles
by: D Giles | last post by:
Access 2003: A subreport control (sum total calculated textbox located in the subreport report footer) does not show total of all records when referenced as a total in the main report footer - only...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.