SQL statement doesn't pick up variable (1 Viewer)

[ClaraBarton]

Dim intDistance As Integer


strSQL = "SELECT t_recipe.recipeid," & _
" t_recipe.recipename," & _
" t_cookbookchapterassociation.cookbookchapterid," & _
" t_cookbookchapterassociation.distance" & _
" FROM t_recipe INNER JOIN t_cookbookchapterassociation" & _
" ON t_recipe.recipeid = t_cookbookchapterassociation.recipeid" & _
" WHERE distance = " & intDistance & _
" ORDER BY recipename"


intDistance = 1
Set rsRecipes = db.OpenRecordset(strSQL)


When I debug.print this strSQL, intDistance is always 0

Is there a problem with my statement?

 

[Gasman]

Yes, you set it after you construct the SQL string?

 

[ClaraBarton]

Yes... just like it is here. I've cut out other stuff but this is the order.

 

[ClaraBarton]

I don't know if this is pertinent... I'm loading a treeview so the SQL is run three times for 3 levels (distance) of nodes.

 

[Gasman]

Try

intDistance = 1

strSQL = "SELECT t_recipe.recipeid," & _
" t_recipe.recipename," & _
" t_cookbookchapterassociation.cookbookchapterid," & _
" t_cookbookchapterassociation.distance" & _
" FROM t_recipe INNER JOIN t_cookbookchapterassociation" & _
" ON t_recipe.recipeid = t_cookbookchapterassociation.recipeid" & _
" WHERE distance = " & intDistance & _
" ORDER BY recipename"

Set rsRecipes = db.OpenRecordset(strSQL)

 

[plog]

Yes... just like it is here. I've cut out other stuff but this is the order.

Gasman's point is that order is important. you are trying to use the value of intDistance prior to setting the value of intDistance. You can't take $100 out of the bank unless you've first deposited at least $100 in the bank.

 

[Micron]

I think the supposition is that a variable is dynamic throughout the code, thus if you construct a sql statement with a variable or form control reference first, then set the variable value, the value used by the sql will be the assigned value. That is not the case.

An integer defaults to zero, thus when used first without setting that value, the value used by the sql will be 0 when the string is created.

 

[ClaraBarton]

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

 

[ClaraBarton]

So you're saying I need to repeat the SQL statement 3 times, for every I change the distance? Doesn't the Set statement run it each time?

 

[Isaac]

When you assign a value to the variable strSQL, that values persists and doesn't change, unless you explicitly write more code to actually re-set its value. Period.

Why don't you post your full code rather than a sample? It might be easier that way.

 

[ClaraBarton]

Gladly! Critique away...

Private Sub tvCookbooks_Load()
On Error GoTo ErrorHandler

Dim db As DAO.Database
Dim rsCookbooks As DAO.Recordset
Dim rsChapters As DAO.Recordset
Dim rsRecipes As DAO.Recordset
Dim strSQL As String
Dim intDistance As Integer

' Clear the tree

Me.tvCookbooks.Nodes.Clear

Me.tvCookbooks.Nodes.Add _
Key:="Cookbooks", _
Text:="Recipes by Cookbook & Chapter"


Set db = CurrentDb()

'add the cookbooks
strSQL = "SELECT cookbookid, name " & _
"FROM t_cookbook " & _
"ORDER BY name"
Set rsCookbooks = db.OpenRecordset(strSQL)

Do Until rsCookbooks.EOF
Me.tvCookbooks.Nodes.Add _
Relative:="Cookbooks", _
Relationship:=tvwChild, _
Key:="CB" & rsCookbooks!cookbookid, _
Text:=rsCookbooks!Name
rsCookbooks.MoveNext
Loop

'add the chapters
strSQL = "SELECT cookbookchapterid, name, cookbookid " & _
"FROM t_cookbookchapter as tblChapter " & _
"ORDER BY name"

Set rsChapters = db.OpenRecordset(strSQL)

Do Until rsChapters.EOF
Me.tvCookbooks.Nodes.Add _
Relative:="CB" & rsChapters!cookbookid, _
Relationship:=tvwChild, _
Key:="CH" & rsChapters!cookbookchapterid, _
Text:=rsChapters!Name
rsChapters.MoveNext
Loop

'add the recipes using cookbookchapterid
strSQL = "SELECT t_recipe.recipeid," & _
" t_recipe.recipename," & _
" t_cookbookchapterassociation.cookbookchapterid," & _
" t_cookbookchapterassociation.distance" & _
" FROM t_recipe INNER JOIN t_cookbookchapterassociation" & _
" ON t_recipe.recipeid = t_cookbookchapterassociation.recipeid" & _
" WHERE distance = " & intDistance & _
" ORDER BY recipename"


'add recipes with index 1
intDistance = 1
Set rsRecipes = db.OpenRecordset(strSQL)
Debug.Print strSQL
Do Until rsRecipes.EOF
Me.tvCookbooks.Nodes.Add _
Relative:="CH" & rsRecipes!cookbookchapterid, _
Relationship:=tvwChild, _
Key:="R" & rsRecipes!recipeid, _
Text:=rsRecipes!recipename
rsRecipes.MoveNext
Loop

'add recipes with index 2
intDistance = 2
Set rsRecipes = db.OpenRecordset(strSQL)
Debug.Print strSQL
Do Until rsRecipes.EOF
Me.tvCookbooks.Nodes.Add _
Relative:="CH" & rsRecipes!cookbookchapterid, _
Relationship:=tvwChild, _
Key:="R" & rsRecipes!recipeid, _
Text:=rsRecipes!recipename
rsRecipes.MoveNext
Loop

'add the recipes with index 3 (tied to chapters)
intDistance = 3
Set rsRecipes = db.OpenRecordset(strSQL)

Do Until rsRecipes.EOF
Me.tvCookbooks.Nodes.Add _
Relative:="CH" & rsRecipes!cookbookchapterid, _
Relationship:=tvwChild, _
Key:="R" & rsRecipes!recipeid, _
Text:=rsRecipes!recipename
rsRecipes.MoveNext
Loop

cleanup:
On Error Resume Next
rsCookbooks.Close
Set rsCookbooks = Nothing
rsChapters.Close
Set rsChapters = Nothing
rsRecipes.Close
Set rsRecipes = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume cleanup

End Sub

 

[ClaraBarton]

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.

 

[Isaac]

Yes, the way you have written it strSQL will continue to look at what it was assigned, which is whatever intDistance was at the time it was assigned.

Another option may be to declare intDistance as a global variable in a new module. Then create strSQL as a public function that looks at the global variable. That way each time the strSQL function is called, it will re-look at the intDistance.

Just one option.

 

[ClaraBarton]

OK! Thank you very much. I would never have figured this out!

 

[Isaac]

 

[plog]

Whenever you feel the need to copy and paste some of your code into new parts of it, you are doing it wrong. With a more efficient SQL statement you can eliminate about 50 lines of code. Replace this:

'add the recipes using cookbookchapterid
strSQL = "SELECT t_recipe.recipeid," & _
" t_recipe.recipename," & _
" t_cookbookchapterassociation.cookbookchapterid," & _
" t_cookbookchapterassociation.distance" & _
" FROM t_recipe INNER JOIN t_cookbookchapterassociation" & _
" ON t_recipe.recipeid = t_cookbookchapterassociation.recipeid" & _
" WHERE distance = " & intDistance & _
" ORDER BY recipename"

With this:

'add the recipes using cookbookchapterid
strSQL = "SELECT t_recipe.recipeid," & _
" t_recipe.recipename," & _
" t_cookbookchapterassociation.cookbookchapterid," & _
" t_cookbookchapterassociation.distance" & _
" FROM t_recipe INNER JOIN t_cookbookchapterassociation" & _
" ON t_recipe.recipeid = t_cookbookchapterassociation.recipeid" & _
" WHERE distance = 1 OR distance=2 OR distance=3"& _
" ORDER BY distance, recipename"

Then you only need to follow it with this section of code once, instead of 3 times and intDistance is no longer necessary:

'add recipes
Set rsRecipes = db.OpenRecordset(strSQL)
Debug.Print strSQL
Do Until rsRecipes.EOF
Me.tvCookbooks.Nodes.Add _
Relative:="CH" & rsRecipes!cookbookchapterid, _
Relationship:=tvwChild, _
Key:="R" & rsRecipes!recipeid, _
Text:=rsRecipes!recipename
rsRecipes.MoveNext
Loop

 

[Isaac]

@plog
I thought OP wanted these in different nodes.

 

[plog]

Not familar with nodes but doesn't that do it every time it enters the loop?


Do Until rsRecipes.EOF
Me.tvCookbooks.Nodes.Add _

 

[ClaraBarton]

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

 

[Isaac]

Honestly, neither am I. It just seemed like the Nodes.Add was desired to be corresponded with the different Where clause criteria.