Totalling a subform column? & Combo box to display a row from a table

[alxj]

Hi,
I have just started writing with ms access and i have come up against a couple of probs,

Firstly, how do you total a column in a subform? i.e. all the line totals added together to produce a grand total.

Secondly, i know it is possible to use a combo box to lookup a row of results from a table, i.e. select customer name = show customer details. However i need to be able to have a set of results displayed based upon more than one combo box selection (rather like using a form filter for each lookup field), i have a large product listing which needs to show the product you require after selecting its size, thickness, pack qty level, material type and Quality (so a combo box for each of the criteria with a product cost to be displayed at the end of the form/subform when the selection has been made.

 

[Fornatian]

To create a 'grand total' field
In the footer of the subform create an unbound control, in the control source enter:

=sum([WhateverFieldYouWishToSum])

You may have to use the Me.Refresh or Me.Requery methods in VBA to refresh the unbound control data when you change any info in the form.

Additionally you can do conditional sum like this:

Sum(Iif(Me![HasItBeenPaidField]="Criteria",0,[Whateverfieldyouwanttosum])

Hope that answers the first question.

Ian

 

[Neal]

I did a job application DB that addressed a problem similar to your second question.
I created a query that included all of the fields I wanted returned, and used the combo boxes on the form as criteria for the query. For instance, the criteria for the product field would be forms!yourform!ComboProduct. The criteria for the size field would be forms!yourform!ComboSize, etc. Then I put a command button on the form that opens the response form based on the data returned by the query. It gets complicated if any of the combo boxes can be empty, but you work around that with code. It also is a bit or work to set up the combo boxes so they are coordinated. I.E. If your product is "Beverages", you want the Size combo box only to show the sizes that drinks come in, not the sizes like lbs. or ft. that might apply to other products.
I hope this points you in the right direction. I can provide more details is you like. You also might look up "Filter by Form" in Help. It is the easiest way to do the same thing if you have tons of criteria.
Neal