Remove Combo-Box Duplicates - Ignore Autonumber

[Cheg]

Hey - I used to be fairly decent with Access, but havent used it for a good 8 years, and have forgot most.



The basic story is as follows:

Table 1:

ProductID (Primary Key)
ProductName (I.E Golfclubs or Deck Chairs)
Amount (Amount of the above that can be bought - 1000, 2000 or 5000)
ProductPrice

A quick example would be:

1...DeckChairs...1000
2...DeckChairs...2000
3...DeckChairs...5000
4...Golfclubs...1000
5...Golfclubs...3000

I'm trying to figure out the easiest way to create 2 combo boxes on a form.

The first will show me a list of products I have. In this case, removing duplicates it will show DeckChairs and Golfclubs.

The second combo box needs to show the amount they can buy based on which product they chose - choosing Deckchairs shows 1000, 2000 or 5000. Choosing Golfclubs shows 1000 or 3000.

The problem I am running into is that in order to remove duplicates in the first box, I have to remove the ProductID in the rowsource, and I think this is messing stuff up.

I have a feeling the issue may be in my table to begin with though. Can anyone offer a guiding hand?

[Datagopherdan]

The problems sounds as though it's in your design. I'm assuming that you're just showing examples. In the actual database, one price would be tied to Deck Chairs, Golf Clubs etc. The basic design would be to have a Products table and a price change history table since prices do tend to fluctuate. You would do a one to many between your products and price change history table and in order to keep it simple, you can have a check-box in the price change history table for "Active". This would note which is the current price.

[weekend00]

change the data source of combo1 to:
select distinct productname from table1