filtering values in a combo box by previous selection

[Tsumara]

I have a parent table with Primary key CustID and a child table with a field ContainerType. This field is a query based combo box, on a subform data sheet with 16 values to choose from.
After a ContainerType value is selected, I don't want it to be available again for that CustID.
Any help would be greatly appreciated.

 

[KenReay]

Hi

Modify the Query on which the Container combo is based to something along the lines of:

SELECT ContainerId FROM tblContainters WHERE ContainerId NOT IN (SELECT ContainerId FROM tblX)

I have guessed at table and column names, and as the name of the table which contains the list of selected Containers but I think there is enough there to get the idea

You will need to .requery the containers combo box, to refresh the list, I would suggest doing this in the GotFocus event

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Tsumara]

Sorry I was so short on the Original Post.

My Select Query now looks like this.

SELECT tblProducts.[ProductID#], tblProducts.ProdName, tblProducts.Terms
FROM tblProducts
WHERE (((tblProducts.[ProductID#]) Not In (SELECT Container FROM tblAgreements)) AND ((tblProducts.Terms)<>"Purchase"));

This is almost what I need.
tblAgreements is where I am storing the selected containers.
The containers are associated to a customer by CustID.
I need to have the whole list for each customer.(just no duplicate containers associated to one customer.)
Currently when one customer picks a container, it is no longer available to the other customers.

Thanks for pointing me in the right direction.
Once again Any help would be greatly appreciated.

 

[KenReay]

Hi

The SELECT you show has no reference to CustId, which you mentioned in your first post

Would you post the structure of tables tblProducts and Agreements, particulalrly with respect to occurences of CustId and Containers

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Tsumara]

Hi KenReay

Ok
tblProducts: ProductID#, ProdName, Terms (rental, loan, purchase), Stock#...

tblAgreements: Agreement#, CustID# (associated CustID#), ProductID#(=Container), Quanity.

Replace Container in initial post with ProductID#.

Thanks

 

[PHV]

Something like this ?
SELECT [ProductID#], ProdName, Terms
FROM tblProducts
WHERE [ProductID#] Not In (SELECT [ProductID#] FROM tblAgreements WHERE [CustID#]=Forms![Name of parent form]![Name of CustID control]) AND Terms<>"Purchase";

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Tsumara]

Ok
Now I after a container/terms is chosen the query starts returning nothing.
Also after a selection is made, and I focus on the next record set the previous selection disappears. The data is still recorded, it's just not displayed.

SELECT [ProductID#], ProdName, Terms
FROM tblProducts
WHERE [ProductID#] Not In (SELECT [ProductID#] FROM tblAgreements WHERE [CustID#]=Forms![Name of parent form]![Name of CustID control]) AND Terms<>"Purchase";

Thanks