how to make treeview toplevel display as an alphabetical index

[raileyb]

I have a treeview that alphabetically displays records, but
instead of:

+ Anderson, Paul
+ Baker, Mike
+ Bonner, Terri
+ Jones, Hal
etc.

the customer would rather see:

+ A
+ B
+ C
etc.

and by expanding one of the top levels, only the names that start with that letter would appear.

+ A
- B
+ Baker, Mike
+ Bonner, Terri
+ C
etc.

It took me a while just to get the names working, now I'm stumped as to how to implement the client request. Can anyone help me?

Thanks,

Brian

 

[SWAndruski]

Brian,

I don't know if you've solved this yet, but I've got code that fills the root level of a tree control with alphabetical headings. The problem is that filtering the recordset doesn't seem to work. I was putting this together piece by piece and got this far to make sure that the "A" root was getting populated with the "A" members of the recordset, but all this does is fill the "A" root with all of the names. I'm not getting any errors, so I'm not sure why it's not working.

I hope the part that fills the root level gives you some help. Perhaps then you can help me figure out why the filter isn't working. Here's my code:

Private Sub Form_Load()
Dim alpha As Long
alpha = 65
ArtistTree.Nodes.Clear
'Populate root level with Capital Letter Alphabet
Do While alpha <= 90
ArtistTree.Nodes.Add , , Chr(alpha), Chr(alpha), "Folder"
alpha = alpha + 1
Loop
'reset alpha to "A"
alpha = 65
Dim N As Integer
Dim key As String
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
'get sorted list of recording artists
Set rst = db.OpenRecordset("View Recording Artist/Discog Query")
With rst
'filter for current "alpha"
.Filter = "SortName Like """ & Chr(alpha) & "*""" 'filtering not working
'create child nodes for current "alpha"
Do While Not rst.EOF
N = 1
key = Chr(alpha) & "_" & !RecordingArtistID
ArtistTree.Nodes.Add Chr(alpha), tvwChild, key, !SortName, "Form"
.MoveNext
N = N + 1
Loop
End With
End Sub


The form that this tree control is on is based on the same stored query that I use to open the recordset. The query is based on the following 2 tables:

Recording Artists:
RecordingArtistID
RecordingArtistName
SortName
Notes
CountryID

Countries:
CountryID
Country

The query simply brings together the fields of the 2 tables and sorts based on the SortName field to provide an alphabetical listing.

I've been looking all over for information and I'm stumped as to why this is not filtering. I'm sure it's something simple I've overlooked.

Thanks in advance for any help.

Steve

 

[gol4]

I think the trouble is you need to apply the filter before you open the recordset

Set db = CurrentDb()
.Filter = "SortName Like """ & Chr(alpha)
Set rst = db.OpenRecordset("View Recording Artist/Discog Query")
With rst

I did not test it but I am pretty sure that is the problem.

 

[SWAndruski]

gol4,

Thanks for the hint. I'll try that tonight. Every example I've found does it the way I have in my code, but it's worth a try. If this works then it may be easier to use an SQL statement in the OpenRecordset line and put the filter in as a WHERE clause. Intuitively I thought it would be easier to create the sorted recordset and then iteratively filter based on the first character of the SortName to get the subset that corresponded to that capital letter. But the bottom line is I'll do whatever gives me the results.

Thanks.

Steve

 

[raileyb]

Hey Steve and gol4,
Thanks for the suggestions and code samples. I too will give them a try and see how my situation goes.

Taz

 

[gol4]

SWAndruski,
You are correct about the sql being faster. This is pasted from access help

"Use the Filter property to apply a filter to a dynaset-, snapshot-, or forward-only–type Recordset object.
You can use the Filter property to restrict the records returned from an existing object when a new Recordset object is opened based on an existing Recordset object.
In many cases, it's faster to open a new Recordset object by using an SQL statement that includes a WHERE clause."

Usually the filter is used in forms-reports

and is used like
me.filter = "this = that"
me.filteron = true

I almost always use sql.

 

[SWAndruski]

Sounds like the SQL statement is the way to go. I may also look at a parameterized query using the QueryDef command. These are supposed to be more efficient, and since I only need to change the WHERE clause in each iteration it may be easier to code. I'll be working on this over the weekend and I'll post the code if it works.

Steve

 

[SWAndruski]

I've got this working. Here's the code for anyone interested:

Private Sub Form_Load()
Dim alpha As Long
alpha = 65
ArtistTree.Nodes.Clear
'Populate root level with Capital Letter Alphabet
Do While alpha <= 90
    ArtistTree.Nodes.Add , , Chr(alpha), Chr(alpha), "Folder"
    alpha = alpha + 1
Loop
'reset alpha to "A"
alpha = 65
Dim N As Integer
Dim key As String
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Do While alpha <= 90
'get sorted list of recording artists and filter for current "alpha"
    Set rst = db.OpenRecordset("SELECT [Recording Artists].* FROM [Recording Artists] WHERE [Recording Artists].SortName Like """ & Chr(alpha) & "*"" ORDER BY [Recording Artists].SortName")
    'create child nodes for current "alpha"
    Do While Not rst.EOF
        N = 1
        key = Chr(alpha) & "_" & rst!RecordingArtistID
        ArtistTree.Nodes.Add Chr(alpha), tvwChild, key, rst!SortName, "Form"
        rst.MoveNext
        N = N + 1
    Loop
    alpha = alpha + 1
Loop
End Sub

Now I just have to get something useful to happen when a node is clicked. Hope this helps.

Steve