Getting the sum from a multivalue combobox

[polis]

Hi everyone,

In my reservations form i have a multivalue combobox(based on a lookup column) which displays the entertainment description(first column) and the entertainment price (second column). The user can select more than one options, how can i show the total of the entertainment price in a text box.


Your help, will be much appreciated.

[rpeare]

How are you storing this information? From the way you're describing it I can only assume you're using a multi value field in your table and you are using a multi select LIST box not a combo box?. If you are there's no simple solution to your question you will likely have use this type of code:

http://stackoverflow.com/questions/2...a-list-box-vba

Where you would retrieve the column number with the amount in it (remember columns in list boxes start with 0 not 1) and sum them then return that value to your main form every time the list is updated.

EDIT: if you are using a multi value field in your table try and stay away from it in the future it's a really, really bad idea. What you really should have is a sub table that lists all of the child items that are related to the parent item.

[polis]

Thanks for your fast reply rpeare,

I have a table that stores the entertainment details(id, description, price) and i have connect it to my reservation table using the lookup column . Therefore when i enter it in the form i can see three columns - column 1 tick selections , column 2 -entertainment description, column 3 - the price. Based on the selections of the customer i want to demonstrate the sum of the entertainment prices.

two days now - and still can get out of it.

[rpeare]

There are two ways to solve it if your detail is in a table and not as a multi value field on your main table.

The first way I've already given you a link to

the second way is to use the DSUM function on your main form

DSUM syntax is like this:

DSUM("[FIELDNAME]","TABLENAME", "[CRITERIAFIELD] = 'CRITERIA'"

if the criteria is a text value or

DSUM("[FIELDNAME]","TABLENAME", "[CRITERIAFIELD] = CRITERIA"

if the criteria is a number field

You just have to substitute in your values for field/form/text box names to get it to work

[polis]

Thanks again,

apparently there in not an easy way to do the the dsum calculation from a multi value field, i reconstructed the tables and now everything looks fine...