Lookup drop box choices and bring back value from table

[AMJADJ]

Hi



I have searched the internet but cannot find the solution.

We are beginning to create a quotation model in which there will be certain sections which will ask the user a question/s and return the cost based on the drop down selections made.
For example, with regards to transport cost, the user will be asked to select values from 2 drop downs, Destination and then number of pallets. Based on this combination another "object" in the form will show the price to send x number of pallets to the destination.

I am assuming I need a table with the fields destination, pallets and cost (called Transport_Costs). Then create a form with 2 drop downs, one getting values from Transport_Costs.destination and the other Transport_Costs.pallets.
What I am struggling with is what do I need to do on the form to get the cost. I want the cost to change if the user changes the initial destination or number of pallets that was selected.

Hope this makes sense.
Thanking you for your time.

[Minty]

You can set the control source of an unbound control to do the maths.

Assuming the bound column of the combos is holding the values , something like;

= cmbTransCost * cmbPallets

[AMJADJ]

Sorry it seems like I may have not explained correctly.

I am assuming I have to have a table as follows:

Aberdeen 2 £3.50
Aberdeen 3 £3.00
Blackpool 2 £4.00
Blackpool 3 £3.70

If the user selects Blackpool as the destination and 2 as the number of pallets, I want the form to bring back £4.00 as the cost. I have the drop downs, just now sure how to get a "field" / "object" to show the £4.00. I guess its lookup the destination and the number of pallets in table and bring back the cost.

[Minty]

Ah sorry - that's even simpler then - Bring that value into the combo box as a hidden column. Lets assume it's the 3rd column.

In the control source of the unbound control use;

=Forms![YourFormName].cmbTransCosts.Column(2)

As you can see from the example column numbering starts at 0

[AMJADJ]

Sorry maybe I am being thick !! and by explaining further I am not insulting your intelligence.

--- > I have a table called destinations which has all my destinations in.
---> I have a table called pallets and this has individual number of pallets I can send (1-26) currently
---> I have another table which has three fields, destination, pallets and cost called costs

---> I have created a form with two combo boxes, one with a list of destinations from the destination table and the other number of pallets from the pallets table.

---> The user will select the destination and the number of pallets, at this point the entries are shown in the box.

What I want happening is when the user has selected the destination and the number of pallets, I want "something" to go to the cost table and look at the row containing the combo box values and bring back the cost of sending x pallets to destination y.

Sorry if I have just repeated myself! I just don't understand the answer above. if the user is selecting values from a drop down., then a combo box with the costs means they have to select the cost? I want the form to automatically sho9w the cost once the two combo boxes have been populated. I am hoping this can be done as I have other questions on the form which will require a cost bringing back.

Thank you for your time, I really appreciate it.

[AMJADJ]

I have solved it, maybe a crude method not sure but it has been solved!

I created a combo box for destination using the destination field and then a combo box for number of pallets.
I then inserted an unbound text object which would = value of combo1 + value of combo2 (this would change when combo1 was updated)
I then created another unbound text box whose control source was =DLookUp("[Costs]![cost]","[Costs]","[destination]= '" & [Text15] & "'")

Thank you all for your replies and taking the time out to respond.