UnionQuery for Treeview (1 Viewer)

[Kryst51]

Hi all,

I am using an example of a treeview that I obtained on this thread.

I learn by doing, and fixing things as they come up.

In the code for building the tree view is a union query which I modified how I thought it would fit my needs. I start with a query instead of a table for my records.

This query is as follows:

Dim IssID as Integer
IssID = Nz(Me.pkNCRHeaderID, 0)
strBaseQuery = " SELECT Trim([ErrorCategory])" & _
" AS ErrorCat, Trim([ErrorSubCategory]) AS ErrorSubCat, Trim([ErrorName])" & _
" AS ErrName, tblNCRIssues.pkNCRIssueID, tblNCRIssues.[pkNCRIssueID] AS IssueID" & _
" FROM tblErrorCategories INNER JOIN (tblErrorSubCategories INNER JOIN (tblErrors INNER JOIN tblNCRIssues ON tblErrors.pkErrorID = tblNCRIssues.fkErrorID) ON tblErrorSubCategories.pkErrorSubCategory = tblErrors.fkErrorSubcategoryID) ON tblErrorCategories.pkErrorCategoriesID = tblErrorSubCategories.fkErrorCategoryID" & _
" WHERE (((tblNCRIssues.fkNCRHeaderID) =" & issID & "))" & _
" GROUP BY Trim([ErrorCategory]), Trim([ErrorSubCategory]), Trim([ErrorName]), tblNCRIssues.pkNCRIssueID, tblNCRIssues.[pkNCRIssueID];"

I believe I have worked all the errors out of this.

I then use this as the basis for this UNION query. I haven't worked with union queries before and am getting a "Syntax error in union query." error. Error Number -2147467259. I've googled this and have not found any of the answers helpful.

Here is my current query:

strSQL = "SELECT BuildKey(IssueID) As NodeKey, ErrorCat As NodeText, BuildKey(0) As BelongsTo" & _
             " FROM (strBaseQuery) As BaseQuery" & _
             " UNION ALL SELECT BuildKey(IssueID) As NodeKey, ErrorSubCat As NodeText, BuildKey(Nz(ErrorCat,0) As BelongsTo" & _
             " FROM (strBaseQuery) As BaseQuery" & _
             " UNION ALL SELECT BuildKey(IssueID) As NodeKey, ErrName As NodeText, BuildKey(Nz(ErrorSubCat,0) As BelongsTo" & _
             " FROM (strBaseQuery) As BaseQuery" & _
             " UNION ALL SELECT TOP 1 BuildKey('<NULL>') As NodeKey, '<Un-Assigned' As NodeText, BuildKey(0) As BelongsTo" & _
             " FROM (strBaseQuery) As BaseQuery" & _
             " ORDER BY NodeText"

Originally "strBaseQuery" was not surrounded by the perenthesis nor was the "As baseQuery" part of it. But I thought maybe it needed to be dealt with as a nested query, so I googled how to do those in a "FROM" clause and this is what I came up with. Now, the truth is I do not understand how the tree-view stuff works, so maybe I am using it incorrectly.

But my goal is to get the queries working then step through the code to see what it is doing. But I don't even understand this starting point. (Sorry for the rambling).

Any help is appreciated.

 

[Banana]

While this may not be the final fix, I'd be very wary of re-using a table alias within a query. Suggest that you have your aliases be "BaseQueryOne", "BaseQueryTwo" and so forth so we can confidently rule aliasing out. (It's generally okay to re-use column alias between UNIONs but should be careful with reusing as occasionally Access may complain about circular references under certain circumstances or get confused in other circumstances).

Also, you shouldn't have semi-colon in your strBaseQuery.

 

[Kryst51]

While this may not be the final fix, I'd be very wary of re-using a table alias within a query. Suggest that you have your aliases be "BaseQueryOne", "BaseQueryTwo" and so forth so we can confidently rule aliasing out. (It's generally okay to re-use column alias between UNIONs but should be careful with reusing as occasionally Access may complain about circular references under certain circumstances or get confused in other circumstances).

Also, you shouldn't have semi-colon in your strBaseQuery.

Hi Banana, Thank you for your answers. (I tried adding to your rep, but as the same folks always provide answers to me, there are several people I am not able to until I "spread it around" ).

I have made the changes you suggested. The problem is still not fixed but the lesson is learned. Except for the semi-colon one, why is that? Is it because it is being used in another query?

 

[Banana]

Correct - in most SQL variants I work with, we don't add an semicolon since it's a terminating statement so for subqueries or where we have UNION, it's not applicable because the statement isn't finished. We only add semicolon where we intend to terminate the SQL statement. Now, someone did point me to a funny quirk where this was actually legal in Access but I don't remember when, and would worry that it'd break in different context. So best to be consistent with the treatment of semi-colon as a terminating statement.

Let's look at the query itself, then. If you take out each of statement between UNION and execute it alone, does it work? Put it back and add union, and repeat until you have an error or a complete statement.