Access 2003 total price calculation

[Durga]

Hi Folks,



I am trying to design a form which enables a person to select a few products from a table (Used combo box, is this the best option?) which lists 'product' and 'price', and calculate the total in the total price field:



How do I go about doing this? In the combo box once I select a product, it is only displaying the product or the price but not both, even so how can I show the total for the selected products in the total price field? Is there any easier way to do this? Any help will be much appreciated. Thank you.

[phivosz]

I would let the combobox display the product description and add a text box for each product next to the combo box to display the price once the product is selected in the combo box (use the afterupdate event of the combobox to store the price in the text box). Suppose the text boxes are named Text1, Text2 etc. Then in the total price text box type "=Text1 + Text2 + Text3 + Text4 + Text5"

[Durga]

I would let the combobox display the product description and add a text box for each product next to the combo box to display the price once the product is selected in the combo box (use the afterupdate event of the combobox to store the price in the text box). Suppose the text boxes are named Text1, Text2 etc. Then in the total price text box type "=Text1 + Text2 + Text3 + Text4 + Text5"

Thank you for your reply phivosz, will try this and get back to you.

[phivosz]

My pleasure. Another idea is to use a multi select list box and select the 5 products in one go. Let me know if you need any help.

[pbaldy]

Your data is not normalized:

http://www.fmsinc.com/MicrosoftAcces...transpose.html

products sold should be in a table where each product sold is a separate record. Then your solution is a simple Sum.

[Durga]

My pleasure. Another idea is to use a multi select list box and select the 5 products in one go. Let me know if you need any help.

Will try both options but unfortunately I do not have exclusive access to the file now hence I will have to wait....

[Durga]

Your data is not normalized:

http://www.fmsinc.com/MicrosoftAcces...transpose.html

products sold should be in a table where each product sold is a separate record. Then your solution is a simple Sum.

Hi pbaldy, thank you. The products are contained in a separate table as individual records.

[Durga]

I would let the combobox display the product description and add a text box for each product next to the combo box to display the price once the product is selected in the combo box (use the afterupdate event of the combobox to store the price in the text box). Suppose the text boxes are named Text1, Text2 etc. Then in the total price text box type "=Text1 + Text2 + Text3 + Text4 + Text5"

I have got the 5 combo boxes and the five corresponding text boxes to display the prices. However the total price field is not returning a total value but just #Name?

[phivosz]

I have got the 5 combo boxes and the five corresponding text boxes to display the prices. However the total price field is not returning a total value but just #Name?

Dear Durga, That means there is something wrong with the source of the total price field.
Anyway look at the example I am sending youDatabase5.accdb

[pbaldy]

Hi pbaldy, thank you. The products are contained in a separate table as individual records.

They should be in this table as well, almost certainly. What if there are 6 products sold?

[Durga]

They should be in this table as well, almost certainly. What if there are 6 products sold?

The product field is just an example I have used whereas in my database the maximum number of 'product' any one would use at any one time is 5.

[Durga]

Dear Durga, That means there is something wrong with the source of the total price field.
Anyway look at the example I am sending youDatabase5.accdb

Will check it out thanks, thought I had done the difficult bit and that the totals field will be a lot easier, as inserting '=[textvalue1]' in the control source of the total field returns the value in the first text box but it is when using the '+' for other text boxes that triggers the error.