How best to output dynamic data in Access

[cbrennan]

Hello, I am writing a recipe management application in Access and could
use some help figuring out how to create a form to display any given
recipe.

I have defined the database such that one recipe can include another,
for example:

recipe A: "pizza crust"

ingredient 1
ingredient 2
....

recipe B: "pizza"

ingredient 1 = "pizza crust" = recipe A
ingredient 2
ingredient 3
.....

I don't understand how to output the expanded recipe B to include all
the ingredients from recipe A.

My first thought was to do a query on recipe A in VB, then iterate
through the recordset and do more queries whenever I ran into an
ingredient that is another recipe. For every ingredient that isn't a
recipe, I would add a text label control with the ingredient details to
the form, and when complete, the user would see all the ingredients on
the form. Coming from a web design background, this seemed like the way
to go, but in Access it seems really hard to do dynamic form creation.
I understand that I would have to put the form into creation mode, add
the controls, save the form, then redisplay.

I've been researching for a better way, so now it seems like the thing
to do is create a temporary table and feed the results of the queries
into it, then when all the data is ready, set this table as the source
for a form, and display that.

I already have a form made for selecting the recipe to display, and a
button to initiate the code to display the expanded recipe. If I can
write the code to output the expanded recipe to a temp table and create
a form from this table, can I then show this new form in a subform
object embedded in my selector form?

Can someone tell me if this approach makes sense? To all you Access
experts out there, what seems like the "right way" to go about doing
what I am trying to do in this case?

Thanks in advance for any help!

-An Access newbie

 

[(PeteCresswell)]

Per (e-mail address removed):

I don't understand how to output the expanded recipe B to include all
the ingredients from recipe A.

My first thought was to do a query on recipe A in VB, then iterate
through the recordset and do more queries....

I sounds like a case for recursive relationships.

But what about this: there must be dozens, if not hundreds of commercial
applications out there that do recipes. Some of them are probably downloadable
as demo versions. Download a few and see how far they go in handling
something like this.

My guess is that they don't go as far as you want to go - and probably for good
reason; namely that it opens such a can of worms that the added data normalcy
isn't worth it.

 

[cbrennan]

Thanks for your quick response, Pete

I sounds like a case for recursive relationships.

I avoided including a recursive relationship in the database design for
fear of creating problems with adding new data items. By mentioning
this, are you saying that with a recursive relationship I could create
a single query that could serve as the data source for a form to
display all the expanded ingredients?

But what about this: there must be dozens, if not hundreds of commercial
applications out there that do recipes.

That's true... I'm searching now for some examples that might help but
I'm not really expecting to find any that are close to my client's
specific requirements. I've already completed the recipe management
part of the app, which is tied in with an inventory management app.
The eventual aim is to be able to automatically deduct the ingredients
from inventory.

So far I have spent very little time on the project and seen good
results, and have very little left to complete the thing, but now I'm
stumped!

This is what I think I should do:

Query for ingredients in a specified recipe
Iterate the ingredients and do another query for any ingredient that is
the result of another recipe
Stuff the resulting data into a temp table or temp recordset
When done, use the result as the source data for a newly created form
"Paste" this form into a subform so the user can see the full list of
ingredients
Also, with the resulting data set I can correctly deduct all the base
ingredients from inventory.

Does this procedure make sense?

Thanks for your help and for any more forthcoming tips

 

[(PeteCresswell)]

Per (e-mail address removed):

By mentioning
this, are you saying that with a recursive relationship I could create
a single query that could serve as the data source for a form to
display all the expanded ingredients?

It got kind of messy for me - probably because I wimped out on creating dynamic
SQL based on a walk through of the max # of recursions in the found set.

The problem is the indeterminate number of recursions. e.g. Recipe A may
include Recipe B, which includes Recipe C. OTOH, Recipe X may include no
other recipes and Recipe Y may only include Recipe B.

So what I wound up doing was making an assumption there would never be more than
N levels of recursion (I think I chose six...). Based on that, I concocted a
monster Union query consisting of seven parts: No recursions, 1 level of
recursion, 2 levels of recursion... and so-forth up to six levels of recursion.

It ran OK speed-wise, but every time I had to populate a work table, screen, or
report I had to do the same song-and-dance. That, and pity the poor person
who has to maintain the app...

That's what prompted me to bring up the "can of worms" aspect...

 

[cbrennan]

Thanks for your help, Pete. Your mention of "work table" helped me a
lot! I did some google searches on that term and ended up with a
working solution. Basically I had to get out of the old web design
habit of creating the output structure using code, and instead use the
Access style of creating the data set using code and then outputting
using a straight form.

fyi, you wrote

The problem is the indeterminate number of recursions. e.g. Recipe A may
include Recipe B, which includes Recipe C. OTOH, Recipe X may include no
other recipes and Recipe Y may only include Recipe B.

Luckily, I was able to come up with some code to handle any number of
recursions.

here is an outline of the code I used:


Private Function InsertIngredients(RecipeID As Integer)

Dim db As Database
.....
strSQL = "SELECT all the ingredients for RecipeID"
Set rstIngrédients = db.OpenRecordset(strSQL)

If rstIngrédients.RecordCount <> 0 Then
Do While Not rstIngrédients.EOF

If Not IsNull(rstIngrédients![Produit.IDRecette]) Then

InsertIngredients(rstIngrédients![Produit.IDRecette])

Else

DoCmd.SetWarnings False
strSQL = "INSERT INTO RecetteRépandueTemp .. insert
ingredients into work table"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

End If

rstIngrédients.MoveNext

Loop
End If
End Function

There is more that I took out, like tracking the order of ingredients
and calculating the amounts of each ingredient (involving some complex
lookups and conversions) but the above shows how I handled the
recursion.

Basically each recipe contains a number of products as ingredients.
Any product can be the result of a recipe, and if it is, it contains a
recipe ID, otherwise it is Null. So my code iterates the ingredients
of a recipe and recursively iterates any ingredients that have a
non-null recipe ID.

The neat part is that I can just INSERT each ingredient with a non-null
recipe ID as I come across it, and when all is said and done, I just do
a .Requery on the sub form that displays the contents of the "work
table". So, in answer to my question about outputing dynamic data, a
good way (if not the best) is to use a work table.

So, again thanks for the help and hopefully this method and code
outline can be helpful to someone else out there

Carl