Calculate a grand total in a form

[hrseebeck]

Hi! I have a form with a subform that list out products and prices. One of the subform fields caluculates the total cost by multiplying qty*price. I would like to have a grand total of that on my main form would be prefered but everything I try is not working. Does anyone have any suggestions?

I have attached a test copy of the database in Access 07.

 

[bob fitz]

Perhaps this link will help: http://office.microsoft.com/en-ca/a...-and-displaying-it-on-a-form-HP005187909.aspx
If you still have problems post back.

 

[hrseebeck]

That site was helpful so thank you; however, I am getting an error still on both sections. Can you help me understand why? I have attached an updated test database in Access 07 for you to review.

Thank you!

 

[bob fitz]

Can you attach an A2003 mdb version of your db

 

[hrseebeck]

Here is a copy in Access 03.

 

[bob fitz]

I'm not sure, but I think you are having problems because the "Price" text box is not bound to a field in the forms Record Source.
However, I'm not sure that you are doing this the best/right/usual way.
If the price of a product is changed after creating a quote, the new price will be applied to the quote.
Take a look at the attached db, which shows how I think it should be done. Only my opinion though.

 

[hrseebeck]

Thank you. How did you get the price to pull into the table?

 

[bob fitz]

Thank you. How did you get the price to pull into the table?

I replaced the field in the table called "Total" with one called "Price".
On the sub form, I changed the "Control Source" property of the text box control to "Price" which is now in the forms Record Source table.
I wrote a little bit of code in the After Update event of the product combo box on the sub form. The code asigns the the price shown in the combo boxes column to the text box called "Price".

 

[hrseebeck]

THANK YOU! I was able to complete this project with your help.

 

[bob fitz]

I have been looking at the db you posted in a little more detail and would like to offer you the following thoughts, advice and opinions:

I can’t see a need for the following fields in the table “tblQuote”.
ProdNo
ProdName

I would advise you to use a more descriptive name for the Primary Key field in table “tblProduct”. Perhaps ProdID.

I would advise against the use of combo boxes in tables. Better to just store the PK and use a combo/list box at the point of data entry.

In table “tblCustomer” you have several fields that are repeated (Address fields for example). In my opinion it would be better to move these to another table.
Imagine you were designing a simple db of contacts. You would have a Primary Key field (auto-number) and fields for FirstName and LastName. You could also have a PhoneNumber field. If you need to store more than one number for a contact you could create another new field. Now you have a field for the “home” number and a field for the “work” number. Where would you put a mobile number? You could create another field. Now you have three fields, all with the same kind of data. You will have problems when you need to search your table for the owner of a particular number because you won’t know which column (field) it is in. Much better to create a new table with a “one to many” relationship for phone numbers that includes the Primary Key from the Contacts table as a Foreign Key.
I believe this is called “Normalization of tables”. Normalization of tables is a frequent topic of threads in this and other Access forums. Try a search of this forum and google. I am certain you will find a mass of information from people with more in depth knowledge than my own.

Good luck with your project.