Another Combo Question

[Navyguy]

I am not sure if I am on the right track with this example...

I want to select down (perhaps filter is the better term) to a specific product price.

I have completed a series of cascading combos to get me to the "final price" selection, but as you might guess when certain selections are picked I get a list of options for price...and not the one based on the selected criteria in the previous combos.

I am not sure if I am approaching this correctly or not.

I am open to suggestions on approach and if I have fundamentally made a error in what I am trying to do.

Attached is a sample of what I am trying to achieve.

Cheers

 

[Poppa Smurf]

Your form works as designed to give you the final price.

My question is why do you have different prices for the same product e.g. Product code 47174815? The two entries for Product code 47174815 have the same value for each field except the price. You either have one product code 47174815 and price or if you need two prices then somewhere maybe in the units columns have (old price) to distinguish the prices and then only one price will be displayed.

 

[Navyguy]

Thank you for taking the time to look at this.

The difference in price would be based on the Unit Of Issue. One price would be by the metre (which in reality would be more expensive), the second which is by the roll (which is less expensive).

But you have also identified the culprit to some respects. As an example, in the last column (if you select that product number) you will get two prices for metre (because there are two records in the table for Metre), where there should only be one; the one linked to the options (filter) of both the product number and the Unit Of Issue.

Cheers

 

[Navyguy]

Just to add another question to this...perhaps I should be adding a ProductID field (AutoNumber) to this table and incorporating this into the SQL?

Would that refine my result to the required price?

Thoughts?

Cheers

 

[Poppa Smurf]

As i said in my previous post

My question is why do you have different prices for the same product e.g. Product code 47174815?

I know that there are different prices for a product depending on the unit quantity. But in your data you have two prices for the same unit quantity. Adding a ProductID field will not help as you would then need to know the ProductID to obtain the correct price. Finally, is there a requirement to have two prices and for what purpose?

 

[Navyguy]

Well you were right that a ProductID did not help.

WRT the dual prices, I believe what you are seeing is the prices for everything for that "Unit of Issue" and not the product number.

You are correct that there are two prices for the same product (47174815, price per metre is .91 and price per roll is 136.50), but the prices that are displayed are based on the "Unit of Issue", depending on what you select, for this example it is Roll (150 m) you will get two prices, but one is 136.50 and the second is 193.50 which is a different product number.

In the sample, product 49501 actually has three prices based on three different UOI...but depending on what UOI you select you might see 4 prices or only one because of the limited sample data.

I need to sit down an focus on this...for some reason this simple filtering form has got me messed up...

I think perhaps I need to look at the design of the table...from scratch.

Thank you for your help so far.

Cheers

 

[jayanth1808]

http://www.access-programmers.co.uk/forums/forumdisplay.php?f=9

 

[Poppa Smurf]

I had another look at your code and have found the problem. The Price was displayed for ALL products that matched the Unit of Issue see you code in Sub CBO_ProductUnitOfIssue_AfterUpdate()

Solution:
Category your drop down will be DISTINCT. IN the after_update of category the row source code for the Descrption is correct.

Now in the after_update of Description the row source for Product Number must include Category. In other words you want to list the Product Number(s) that have a category as selected in Product Category AND Description that is in Product Description. In your current code you are saying for the Product Number list all the Product Numbers that match the seelcted Description.

For Manufacture it will be same row source code as for Product Number plus will have to add AND Product number.

Finally for Product Price you will end up with a criteria that is something like this. List the Price for a product that has a category as selected in Product Category AND has a Description as selected in Product Description AND has a Number as selected in Product Number AND has a Unit of Issue as selected in Product Unit of issue. This will result in only one price being displayed

 

[Navyguy]

@ jayanth1808, thank you for the link, however I am not sure what is it supposed to display. It takes me back to the current forms thread...

@ Poppa Smurf, thank you for looking at it again. Of course when you stated the obvious, it seemed so simple. I think it was just an issue of not focusing on it correctly. Thank you for "holding my hand" and walking me through this.

Time to get it done. Lets hope I don't mess up the code!!!!

Cheers