Creating an instance of a recipe. (1 Viewer)

ShyLynx

New member
Local time
Today, 04:13
Joined
Apr 16, 2014
Messages
3
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
 

Isskint

Slowly Developing
Local time
Today, 11:13
Joined
Apr 25, 2012
Messages
1,302
Hello ShyLynx, a nice little puzzle.

A query is the answer to your befuddlement (love that word;) ). You will need to create joins across multiple tables and joins on joins!! Please look at the following SQL.
Code:
SELECT tblSupplementRecipeBatches.BatchID, tblSupplementLots.LotID
FROM (tblSupplements INNER JOIN ((tblRecipes INNER JOIN tblRecipeItems ON tblRecipes.RecipeID = tblRecipeItems.RecipeID) INNER JOIN tblSupplementRecipeBatches ON tblRecipes.RecipeID = tblSupplementRecipeBatches.RecipeID) ON tblSupplements.SupplementID = tblRecipeItems.SupplementID) INNER JOIN tblSupplementLots ON tblSupplements.SupplementID = tblSupplementLots.SupplementID
WHERE (((tblSupplementRecipeBatches.BatchID)=[IDTOCHECK]));
Assuming you have created the relevant relationships between your tables and based on the data you have included in your OP, this should produce a list of LOTIDs related to IDTOCHECK. Replace IDTOCHECK with the relevant batch ID field/control.
You can use this query as the RowSource for your combobox.

Would it follow that the entries for tblSupplementRecipeBatchItems.LotID would be every item on the resulting query? If that is the case you can save the need for the user to record the LotID's by changing the select query into an Append query. Just add the following to the beginning of the above code.
Code:
INSERT INTO tblSupplementRecipeBatchItems ( BatchID, LotID )
 

ShyLynx

New member
Local time
Today, 04:13
Joined
Apr 16, 2014
Messages
3
Thank you Isskint, but this doesn't do what I need. This query simply pulls up a list of paired values: BatchID, LotID

I should have mentioned that there are multiple lots of each supplement and what I need is for the user to be able to choose the appropriate one. So I was looking to make a form which has some controls populated from a query, similar to the one you've given, but to have the control connected to LotID be filled by the user. The trouble is that sometimes a Recipe will have 2 Supplements in it and sometimes 4.

I tried making a continuous subform based on the tblRecipes which would list the SupplementID's. If I put an unbound cbxLotID on this subform, which gives a list of LotID's based on each corresponding SupplementID, how do I then stuff all these into the right table.

(As it turns out, I can't even get this cbxLotID selection to work. Somehow the order in which the controls are populated is such that when the query looks for SupplementID that control is empty.) Ouch. Problems are multiplying.

Let's say that the subform approach is the one that can work. Suppose that on the outer form I have a tbxBatchID control and a cbxRecipeID (the last one populated from a query that lists all the RecipeID's and RecipeNames).

The subform is Master-Child linked on the RecipeID and is based on the query that pulls ups all the SupplementID's and SupplementNames corresponding to a given RecipeID. It also has an unbound cbxLotID. I want it to be populated by the user from the pulldown list created by a query based on the SupplementID. This doesn't work right now. The query finds nothing in the specified control and asks me for input. If I manually put in the correct SupplementID, it puts the same list into every record on the continuous form. Not what I wanted.

Assuming I can get it to work somehow, now I have the single tbxBatchID on the outer form, and multiple instances of the cbxLotID on the subform (it's continuous, remember). How do I put these into my tblSupplementRecipeBatchItems ? I need somehow to handle the records on the subform one at a time, but I don't really know how to do this. Do I need to fire off a VBA event when the LotID is picked?

Let me know whether some screen shots would help clarify.

Thanks again for responding so promptly. Very kint of you ;)
Perhaps explaining the problem to you will help me get out of my befuddlement.

Yelena
 
Last edited:

Users who are viewing this thread

Top Bottom