G
Gen
Hi there,
I'm struggling trying to design an appropriate relationship for some data. I
want to create a database of batch recipes. Each batch can include one or
more ingedients, and each ingredient can have one or more supplier. Ideally,
the form would have the batch number, then a datasheet subform where the user
can select one or more ingreients using a combobox (CBO1), then select the
appropriate supplier/Lot# from a second combobox (CBO2) which is filtered by
the selection in CBO1.
My table design has four tables: tblBatch, tbIngName, tblIngSource and
tblIngDetail with the following feilds:
tblBatch
BatchID*
tblIngName
IngNameID*
IngName
tblIngSource
IngSourceID*
IngNameID
IngSupplier
IngLot#
tblIngDetail
DetailID*
BatchID
IngNameID
IngSourceID
*Primary key
So tblIngDetail bridges the primary keys from the other three tables. I
hope you can visualize this. It seems rather complicated to me and I'm not
sure its the best set up.
The problem I have is with the form. When I set up CBO2 (supplier) to be
filtered by CBO1 (Ingredient; filtered for IngNameID =CBO1 using the SQL for
row source) with a requery for CBO2 in the On Change event for CBO1, it
filters all the CBO2, not just the one for that record. So say for Batch No
123 I selected Ingedrient 1 in CBO1, CBO2 would list suppliers A & B. I go to
the next line and select Ingredient 2 in CBO1, CBO2 is filtered for its
suppliers (C &D), but CBO2 for Ingredient 1 is now blank because it doesn't
have the same suppliers as Ingredient 2!!!!! Please help!
I'm struggling trying to design an appropriate relationship for some data. I
want to create a database of batch recipes. Each batch can include one or
more ingedients, and each ingredient can have one or more supplier. Ideally,
the form would have the batch number, then a datasheet subform where the user
can select one or more ingreients using a combobox (CBO1), then select the
appropriate supplier/Lot# from a second combobox (CBO2) which is filtered by
the selection in CBO1.
My table design has four tables: tblBatch, tbIngName, tblIngSource and
tblIngDetail with the following feilds:
tblBatch
BatchID*
tblIngName
IngNameID*
IngName
tblIngSource
IngSourceID*
IngNameID
IngSupplier
IngLot#
tblIngDetail
DetailID*
BatchID
IngNameID
IngSourceID
*Primary key
So tblIngDetail bridges the primary keys from the other three tables. I
hope you can visualize this. It seems rather complicated to me and I'm not
sure its the best set up.
The problem I have is with the form. When I set up CBO2 (supplier) to be
filtered by CBO1 (Ingredient; filtered for IngNameID =CBO1 using the SQL for
row source) with a requery for CBO2 in the On Change event for CBO1, it
filters all the CBO2, not just the one for that record. So say for Batch No
123 I selected Ingedrient 1 in CBO1, CBO2 would list suppliers A & B. I go to
the next line and select Ingredient 2 in CBO1, CBO2 is filtered for its
suppliers (C &D), but CBO2 for Ingredient 1 is now blank because it doesn't
have the same suppliers as Ingredient 2!!!!! Please help!