Access 03 double listbox and subform, suggestions please 1

[GinaStar]

Am just upgrading to Access 03 from Access 97 and still figuring out what it will do. Am not sure if this will work. Suggestions please? Here is what I want to do and how I am trying to do it. I thought possibly select case statements or the limit the values of a combo box or the double listbox suggestion in the faqs.

3 tables, tblQuarry, tblProduct, tblPrice. Each is in a one-to-many relationship. One quarry, many products. One Product, many prices (update price yearly).

Would like to have a form with two list boxes and a subform.

lstboxQuarry, lstboxProduct
subformPrices

Logic Statement:

Case 1
LstboxQuarry Not Null (one quarry selected)
LstboxProduct Is Null (no product selected)
Show in lstboxProduct all products related to selected quarry.
Show in subformPrices all prices for related products.

Case 2
LstBoxQuarry Is Null (No quarry Selected)
LstboxProduct Is Not Null (one product selected)
Show in lstboxQuarry all quarries that have particular product
Show in subformPrices all prices for those quarries that carry that product.

Case 3
LstboxQuarry Is Not Null (One Quarry Selected)
lstboxProduct Is Not Null (One Product Selected)
Show in subformPrices price for particular product/quarry

Case 4
lstboxQuarry Is null (No quarry Selected)
lstboxProduct Is null (no product Selected)
Show in subformPrices price for all products/quarry

Case 3 & 4 will be easy. What I'm not sure about is 1 & 2. I have the form set up, I just need to work out the coding. I don't want someone to code it for me I want to work it out myself, but if someone wants to help point me in the right direction, I would appreciate it.

 

[willir]

A recent post go through this logic...

http://www.tek-tips.com/viewthread.cfm?SQID=876451&SPID=705&newpid=705&page=1

Richard

 

[GinaStar]

Thanks so much!! this gives me a direction to head in.

Regina