Summary Costing Form

[sud2017]

Hi Guys,

Would greatly appreciate for your help. I am trying to create a summary costing form that will contain a combobox (for selection of opportunity). Once an opportunity is selected from the combobox, it should populate 'sum of item qty* item cost' for the selected opportunity in a textbox. If I have confused you, the attached image might help get a clear understanding. There is bit more to it, the cost calculation (sum of item qty * item cost) should consider different currencies into account which is stored into a different table (linked to the table shown in my image by VendorID). Different items have different currencies and I have an exchange rate in a separate table as well. I want to populate the total cost for selected opportunity in both US$ and CAD$ in this form. Your help or guidance to approach this would be very highly appreciated. Thank you!

[NTC]

the combo box set up must contain all of the fields you seek to work with, though they don't all have to display if you don't wish. You control the display by the column width.

the combo box will use the PK as its bound value, and then display the next column (which is count 1) .... let's assume then that QTY is column 2 and UnitCost is column 3.

in your unbound text box you will have its control source: = [ComboBoxName].column(2) * [ComboBoxName].column (3)

You state that there is more to it involving currency type - generally you won't want to attempt to cram all that into a single unbound text box and so to handle a more complex calculation you might consider instead going to another stand alone query that does all the math and then in the unbound text box use a DLookUp.

[sud2017]

Thank you very much for your advise NTC. That sounds appropriate but I went with another approach that I thought would be simpler for my application but need some further help. I have a subform (generated from query with fields including OppID, item qty, item cost, currency etc.) and then I am filtering the subform data from combobox selections, to get the Sum(of item qty* unit cost) in a corresponding textbox in the main form.

I have few such comboboxes in the main form to select different opportunities, which once selected should get the sum (item qty*unit cost) calculated for that particular OppID in corresponding textboxes.

I have successfully done it for 1st combobox and textbox, but the subsequent comboboxes doesn't filter data in the subform.

So I am wondering if there is a way to lock the first textbox value once populated, and then reset the subform filter so that the subsequent combobox can use a fresh filter and then get the calculated sum in the second textbox for the second opportunity selected. The subform has item details for all opportunities of the company.

Anyone's guidance/help would be greatly appreciated. Thanks very much.