I'm making a database that helps keeping track of particular lots of ingredients going into certain recipes.
The relevant tables are
tblSupplements (SupplementID, SupplementName)
tblSupplementLots (LotID, SupplementID, ArrivalDate)
tblRecipes (RecipeID, RecipeName)
tblRecipeItems(RecipeID, SupplementID, Quantity)
tblSupplementRecipeBatches (BatchID, RecipeID, MixingDate)
tblSupplementRecipeBatchItems(BatchID,LotID)
I have the top 4 tables filled out via forms and it all works swimmingly.
I'm having a hard time coming up with a scheme for filling out the bottom two.
I would like to have a form which will display the BatchID (automatically generated), allows the user to enter the MixingDate, gives a combobox bound to RecipeID which is filled from a pulldown menu based on RecipeID and RecipeName from tblRecipes.
All these I can do, but the next step is befuddling me: based on the RecipeID, I need to make a list consisting of the varying number of supplements which comprise the given recipe and allow the user to enter the LotID for each one (preferably from a pulldown menu).
I've tried a bunch of things (subforms linked on RecipeID, listboxes, VBA update queries attached to buttons). I just can't seem to understand how to have a variable number of subrecords appear and attach a new field (LotID) to each. Perhaps my table design is awkward?
I hope I've given enough details. Thanks for your help.
Yelena
The relevant tables are
tblSupplements (SupplementID, SupplementName)
tblSupplementLots (LotID, SupplementID, ArrivalDate)
tblRecipes (RecipeID, RecipeName)
tblRecipeItems(RecipeID, SupplementID, Quantity)
tblSupplementRecipeBatches (BatchID, RecipeID, MixingDate)
tblSupplementRecipeBatchItems(BatchID,LotID)
I have the top 4 tables filled out via forms and it all works swimmingly.
I'm having a hard time coming up with a scheme for filling out the bottom two.
I would like to have a form which will display the BatchID (automatically generated), allows the user to enter the MixingDate, gives a combobox bound to RecipeID which is filled from a pulldown menu based on RecipeID and RecipeName from tblRecipes.
All these I can do, but the next step is befuddling me: based on the RecipeID, I need to make a list consisting of the varying number of supplements which comprise the given recipe and allow the user to enter the LotID for each one (preferably from a pulldown menu).
I've tried a bunch of things (subforms linked on RecipeID, listboxes, VBA update queries attached to buttons). I just can't seem to understand how to have a variable number of subrecords appear and attach a new field (LotID) to each. Perhaps my table design is awkward?
I hope I've given enough details. Thanks for your help.
Yelena