SQL statement doesn't pick up variable

[Pat Hartman]

So you're saying I need to repeat the SQL statement 3 times, for every I change the distance?

That's what happens when you use embedded SQL rather than a querydef. You could rearrange the code so that the beginning part of the SQL string is populated once and then just before you run the query, you populate the variable part.

I would use a querydef that takes an argument rather than using embedded static SQL.

 

[Isaac]

It Works!!! THANK YOU THANK YOU THANK YOU. ...I'm singing to you here..

Can you share with us what worked? Either way, I am glad you got it working.

 

[ClaraBarton]

I used Plog's code. There are some distancing errors but I now have a structure to work from. I very much appreciate all your attention. In the original program the recipes, the ingredients, the menus, the techniques, all worked from a treeview so I've been working on a template to use throughout the program and have struggled for some time until I figured out that distance in the table meant node levels. Part of the problem is there are 90 tables with incredibly long names that I have to figure out how to put together. It's coming though and I have you guys to thank.

 

[ClaraBarton]

sorry... not guys... You All!

 

[MajP]

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

Would monitor size or resolution have anything to do with it and/or having a modal form? I'll try again when I get back home.

www.access-programmers.co.uk

You may want to look at this. Loading your tree requires no code if you can build the query.

 

[ClaraBarton]

well.... back to the drawing board... On further inspection all recipes on level 3 work, but if the recipe goes to a cookbook with no chapters (level 2) it's missing. Not asking for help; just don't want anyone else to use it....

 

[Gasman]

It is not working because you building a statement and concatenating that variable. You get the value of that variable as it is at the time you construct the statement.?
You need to construct the statement each time? AFTER the value is set, not BEFORE

no no... I'm calling the SQL in the Set Recipes statement; AFTER I set the distance.

Think of it as

Living Cookbook by Radium is no longer supported. I lost the program when I upgraded and discovered their database (with all my recipes over many years) was readable by Access. Sort of... Now I'm trying to put this inherited mess together so I can use it.

I take it you explored other avenues first?

https://www.reddit.com/r/Cooking/comments/a178j9