Scrolling with the Treeview Control

So there are plenty of examples of treeview controls out there that show you how to load the control and how to drag and drop items within the control. But what happens when you load so many items into your control that you have to scroll to see them all, and then you want to drag an item from the bottom up to the top? Well, you could drag it part way, drop it, scroll up, then drag it up some more, drop it, scroll up, etc., until you have dragged it all the way to the top. Or you could make the treeview control scroll automatically for you. You can accomplish this using the timer event of the treeview control. The first 4 steps of this example show how to create a form with a tree control, load data into it, and create the drag and drop procedures. If you already have a working tree control, skip directly to step 5 for the step to make it scroll.

1. First create a new form named Tree. Put your treeview control on the form. In this example, I am using Microsoft Tree Control version 6.0 that comes from mscomctl.ocx. Once you add the treeview control, you will see that mscomctl.ocx will automatically be added the references in your project. (Note: If you are doing this for a project used by anyone other than yourself, you should go back at the end and remove the reference to mscomctl.ocx, and change your code to late binding to avoid reference problems). Right click on your tree control. You should see a menu item named TreeCtrl. Under that menu item, click Properties. Under OLEDragMode pick Automatic, and under OLEDropMode, pick Manual. Close the properties.

2. Next, decide what data you are going to load into your tree. I picked the 1000 most popular names of 2008 so I would have a bunch of data to work with. I put them in my "Names" table. My table has fields for NameID (autonumber, primary key), Name (text), and NameOrder(long).

3. Now write a procedure called LoadTree to load your data into the treeview control. This one is loosely based on the tree loading code provided by Microsoft in the knowledge base:

Public Sub LoadTree()
Dim nnode 'As ComctlLib.Node - eliminate early binding to avoid errors on other computers
Dim rst As DAO.Recordset
Dim sKey As String
Dim sDisplay As String
On Error GoTo EH
Application.Echo False
'First clear any existing nodes
Forms!Tree.TreeView1.Nodes.Clear
'get the data
Set rst = CurrentDb.OpenRecordset("SELECT * FROM Names Order By NameOrder;")
Do While rst.EOF = False
sKey = rst.Fields("[NameID]") & ":" & rst.Fields("[NameOrder]")
sDisplay = rst.Fields("[Name]")
Set nnode = Forms!Tree.TreeView1.Nodes.Add(, 1, sKey, sDisplay)
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Application.Echo True
Exit Sub
EH:
Application.Echo False
If Err.Number = 35602 Then 'this error doesn't matter, ignore it
Exit Sub
Else
MsgBox "Error Number: " & Err.Number & ".  " & _
"Error Description: " & Err.Description, vbOKOnly, "Error"
End If
End Sub

Place a call the LoadTree procedure from the OnLoad event of the Tree form. Now when you open your Tree form, your data should be loaded into the tree.

4. The next step is to write the drag and drop procedures for the tree control. In the OLEStartDrag event, write:

Me!TreeView1.Object.selecteditem = Nothing

This is to clear the previous selection. Next, in the OLEDragOver event, write a hit test so the nodes are highlighted as we drag over them:

Private Sub TreeView1_OLEDragOver(Data As Object, Effect As Long, _ Button As Integer, Shift As Integer, x As Single, y As Single, State As Integer)
Dim oTree
On Error GoTo EH
Set oTree = Me.TreeView1.Object
'if no node is selected, select the first node you dragged over.
If oTree.SelectedItem Is Nothing Then
Set oTree.SelectedItem = oTree.HitTest(x, y)
End If
'Highlight the node being dragged over as a potential drop target.
Set oTree.DropHighlight = oTree.HitTest(x, y)
Exit Sub
EH:
MsgBox "Error: " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
End Sub

Next, in the OLEDragDrop event, write a procedure to move the selected node to the selected location. In this example, we will accomplish this using the NameOrder field:

Private Sub TreeView1_OLEDragDrop(Data As Object, Effect As Long, Button As Integer, _ Shift As Integer, x As Single, y As Single)
Dim oTree
Dim sDropKey As String 'node key of drop node
Dim lDropOrder As Long 'order of drop node
Dim sSelKey As String 'node key of selected node
Dim lSelName As Long 'primary key of selected node
Dim lSelOrder As Long 'order of selected node
On Error GoTo EH
'Create a reference to the TreeView control
Set oTree = Me!TreeView1.Object
'If nothing is selected for drag, do nothing
If oTree.SelectedItem Is Nothing Then: Exit Sub
'If the node was dragged to an empty space,do nothing
If oTree.DropHighlight Is Nothing Then: Exit Sub
'if the node was dragged to itself do nothing
If oTree.SelectedItem.Index = oTree.DropHighlight.Index Then: Exit Sub
'Get drop node values
sDropKey = oTree.DropHighlight.Key
lDropOrder = CLng(Mid(sDropKey, InStr(1, sDropKey, ":") + 1))
'Get selected node values
sSelKey = oTree.SelectedItem.Key
lSelName = CLng(Left(sSelKey, InStr(1, sSelKey, ":") - 1))
lSelOrder = CLng(Mid(sSelKey, InStr(1, sSelKey, ":") + 1))

'First, make a space in the order for the selected node behind the drop node
CurrentDb.Execute "UPDATE Names SET NameOrder=NameOrder+1 " & _
"WHERE NameOrder>" & lDropOrder & ";"

'Next, update the order of the selected node:
CurrentDb.Execute "UPDATE Names SET NameOrder=" & lDropOrder + 1 & _
" WHERE NameID=" & lSelName & ";"

'Finally, close the empty space left behind when the selected node was moved
CurrentDb.Execute "UPDATE Names SET NameOrder=NameOrder-1 " & _
"WHERE NameOrder>" & lSelOrder & ";"
'clear the highlight
oTree.DropHighlight = Nothing
'reload the tree
LoadTree
Exit Sub
EH:
MsgBox "Error: " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
End Sub

Now open your tree form. You should be able to drag and drop the items in your control into whatever order you please. But, if you try to drag an item all the way up or down the list, you can't do it without doing multiple small drags as you work your way up the control.

5. To make the treeview scroll while dragging, we have to use the windows api. Put this declaration at the top of your tree form code:

Private Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd As Long, _
ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Dim mfX As Single
Dim mfY As Single
Dim m_iScrollDir As Integer 'Which way to scroll

And write this code in the timer event to scroll the control:

Private Sub Form_Timer()
On Error GoTo EH
Me.Form.TimerInterval = 0
Set TreeView1.DropHighlight = TreeView1.HitTest(mfX, mfY)
If m_iScrollDir = -1 Then 'Scroll Up
' Send a WM_VSCROLL message 0 is up and 1 is down
SendMessage TreeView1.hWnd, 277&, 0&, Null
Else 'Scroll Down
SendMessage TreeView1.hWnd, 277&, 1&, Null
End If
Me.Form.TimerInterval = 20
Exit Sub
EH:
MsgBox "Error: " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
End Sub

Now modify the OLEDragOver event to tell the control when to scroll:

mfX = x

mfY = y

If y > 0 And y <>

m_iScrollDir = -1

Me.Form.TimerInterval = 20

ElseIf y > (Me.TreeView1.Height - 500) And y < (Me.TreeView1.Height) Then

'scroll down

m_iScrollDir = 1

Me.Form.TimerInterval = 20

Else

Me.Form.TimerInterval = 0

End If

Finally, turn the timer off at the beginning of the OLEDragDrop event: Me.Form.TimerInterval = 0

That's it. Now, try to drag the first item all the way to the bottom of the list. You may need to play with the greater than value in the timer event to get the scrolling to work appropriately. The value of 500 works well with my control, which is sized to 3.875 inches tall. You can download my project here. You can download mscomctl.ocx from Microsoft.com.

How to fake an editable crosstab in Microsoft Access

I have spent most of the last two years working primarily in Microsoft Access, and one of the issues I ran into more than once is that there is no provision for creating an interface to edit non-aggregated data in a crosstab format. Now in most cases, editing a crosstab is simply not appropriate because 99% of the time you are dealing with aggregated or non-unique values. But in certain cases, nothing else will do. Here is an example of a dataset where editing in a crosstab format makes a lot of sense:

T-shirt size and color availability:

	Red	Blue	Yellow
Small	Yes	Yes	No
Medium	Yes	Yes	Special Order
Large	No	Yes	Yes

In order to normalize the above dataset properly, you would have to do this:

Size	Color	Availability
Small	Blue	Yes
Medium	Blue	Yes
Large	Blue	Yes
Small	Red	Yes
Medium	Red	Yes
Large	Red	No
Small	Yellow	No
Medium	Yellow	Special Order
Large	Yellow	Yes

Clearly, it is much easier to both enter and view the data in the crosstabbed format. However, if you tried to write a query to retrieve the availability for a medium red shirt, you would need the normalized format to work with instead.

So the conundrum is that we want normalized data for the purposes of retrieval, but crosstab data for the purpose of user interaction.

Access will allow you to make a crosstab query out of the normalized data, but it won't let you edit or enter the data. Why? Because Microsoft Access assumes that when you create a crosstab, you are aggregating the data, and therefore each "cell" in the crosstab does not represent a single unique value. Access does not have a provision for editing non-aggregated crosstabs, so we have to fake it.

My solution is not pretty or elegant, but if you must have an editable crosstab, it will work. The basic idea is that you store the normalized data, crosstab the data when the user wants to work with it, and then renormalize it again.

Benefits of this method:

You get to edit in a crosstab format while keeping your normalized database structure.

Downside:

-Color names (or whatever you are using for the row headers) MUST be unique.

-Sizes (or whatever you are using for the column headers) MUST be ordered perfectly (i.e., ordering starts at 1, every record has an order, and there are no breaks in the sequence).

-You cannot have more than 255 records in the table that you are using for the column headers. Access crosstabs are limited to 256 columns of data, and you need to reserve one for the row headers. (You can overcome this limitation by having one form for the first 255 columns, another form for the next 255 columns, and so on, and then have "next" and "previous" buttons that toggle the visibility of the forms).

-BIG downside: There is a long wait time to load/unload form for large data sets (you can overcome this limitation by buying a ridiculously expensive computer with multiple gigabytes of RAM, or by breathing deeply and practicing patience).

Ok, so this is how you do it:

Step 1: Make a table named tblSizes with these fields: SizeID (Autonumber, primary key), Size (Text), SizeOrder (Long). Make a second table named tblColors with these fields: ColorID (Autonumber, primary key), Color (Text), ColorOrder(Long). (Note: You need separate ordering fields because you cannot assume that the autonumbers will be continuous and sequential, what happens when you delete small and add 2X?). Put some data in each table.

Step 2: Create the normalized data set that you eventually will work with in a crosstabbed format by writing a query that contains all possible combinations of the colors and sizes you entered:

SELECT CLng([ColorID]) AS Color, CLng([SizeID]) AS Size, "Y" AS Available INTO tblAvailable FROM tblColors, tblSizes;

Now we have a normalized data set that includes every possible combination. Every possible combination is also set to "Y" (Yes) for available by default.

Step 3: Create a new crosstab query and save it as qryCrosstab. The crosstab should use the SizeOrder field for the column headers, the Color field for the row headers, and the FirstOf Availability field for the Value.

TRANSFORM First(tblColorSize.Available) AS FirstOfAvailable
SELECT tblColors.Color
FROM (tblColorSize INNER JOIN tblColors ON tblColorSize.Color = tblColors.ColorID) INNER JOIN tblSizes ON tblColorSize.Size = tblSizes.SizeID
GROUP BY tblColors.Color
PIVOT tblSizes.SizeOrder;

Step 4: Create a new module called CrosstabMadness. Create a procedure called CrosstabAvailability. This procedure will write your crosstab query to a temp table:

Public Sub CrosstabAvailability()
'This procedure writes the results of the crosstab query to a temp table
On Error GoTo EH
'delete previous temp table if it exists
If Nz(DCount("[Name]", "MSysObjects", "[Name]='tblCrosstab'"), 0) <> 0 Then
DoCmd.DeleteObject acTable, "tblCrosstab"
End If

'create crosstab
'NOTE:This only works with a maximum of 255 records in the tblSize table.
CurrentDb.Execute "SELECT qryCrosstab.* INTO tblCrosstab FROM qryCrosstab;", dbFailOnError + dbSeeChanges

Exit Sub
EH:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Sub

Go ahead and run the CrosstabAvailability procedure to make sure it works, and also to create tblCrosstab.

Step 5: Create a new form named frmShowColorSize with a record source of:

SELECT tblCrosstab.* FROM tblCrosstab;

View the field list for your form. The list should say Color, 1, 2, 3, etc. Drag all of the fields onto the form as text controls. The text controls should now be named after the data sources they are bound to, i.e., the control that displays the value of field 1 is named 1. Name the associated labels Label1, Label2, and so on to match. Don't worry about the label captions - they will be loaded at runtime. On the Color control, go to properties and set it to Locked under the data tab (you don't want users accidentally editing the color names).

Figure out the maximum number of sizes you will ever need (i.e., maybe you have 4 now, but you might have up to 10 in the future). Add additional text controls for as placeholders (i.e., 5, 6, 7, etc.). Bind and name the placeholder controls and labels to match the other controls even though those fields don't exist in your current data set. So the control that displays the value of field 5 should be named 5, and it should be associated with a label named Label5. Keep in mind, it is easier to add more now than to go back and add them later. If you have too many to do this by hand, then write a procedure with an incrementing integer that loops through each control on the form and names it appropriately.

Step 6: Go back to your module, and write two more procedures. The LoadCaptions procedure will update the captions on the form at runtime:

Public Sub LoadCaptions()
'this procedure loads the captions that will show up as the column headers
Dim i As Integer
Dim rst As DAO.Recordset
On Error GoTo EH
'get a recordset of all the sizes, make sure they are in order
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblSizes Order By SizeOrder;")
rst.MoveFirst
For i = 1 To 8 'or however many text controls you placed on your form
If rst.EOF = False Then 'load the value into the caption
Forms!frmShowColorSize("Label" & i).Caption = rst.Fields("Size")
rst.MoveNext
Else 'no more to load, set caption to blank
Forms!frmShowColorSize.Form("Label" & i).Caption = ""
End If
Next
rst.Close
Set rst = Nothing
Exit Sub
EH:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Sub

The AdjustColumnWidths will format the columns appropriately at runtime. Since we have created more controls than we are going to use, we need to hide the ones that will not be populated and we need to adjust the width of the controls to fit the data they contain.

Public Sub AdjustColumnWidths()
'this procedure adjusts the column widths to fit their current contents
Dim ctl As Control
Dim lCount As Long
On Error GoTo EH
lCount = Nz(DCount("[SizeID]", "tblSizes"), 0)
'this is how many columns are populated with data
For Each ctl In Forms!frmShowColorSize.Controls
   If ctl.ControlType = acTextBox Then
           If IsNumeric(ctl.Name) Then
           'if the name of this control is greater
           'than the number of populated controls, hide this column
                   If CInt(ctl.Name) > lCount Then
                       ctl.ColumnHidden = True
                   Else 'autofit the populated columns
                       ctl.ColumnHidden = False
                       ctl.ColumnWidth = -2
                   End If
           End If
    End If
Next
Exit Sub
EH:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Sub

Step 7: Write one more procedure to tie it all together and load the form.

Public Sub LoadCrosstabForm()

   On Error GoTo EH
'this procedure crosstabs the data, opens the form, and formats it correctly.
CrosstabAvailability
DoCmd.OpenForm "frmShowColorSize", acFormDS
LoadCaptions
AdjustColumnWidths
Exit Sub
EH:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Sub

Call this procedure from the on click event from a button on another form.

Click the button. Your form should now open and display your crosstab data. But we aren't done yet. What happens if you edit the data, and you want to save the changes?

Step 8: Create a new table named tblNormalize with three fields: ColorName (text), SizeOrder (long), and Availability (text).

Step 9: Go back into your module and create a new procedure called NormalizeIt:

Public Sub NormalizeIt()
Dim rst As DAO.Recordset
On Error GoTo EH

'delete existing data from temp table
CurrentDb.Execute "DELETE * FROM tblNormalize;", dbFailOnError + dbSeeChanges

'get a recordset of the column headers
Set rst = CurrentDb.OpenRecordset("SELECT SizeOrder FROM tblSizes;")
rst.MoveFirst
Do While rst.EOF = False
' "un" crosstab the data from tblCrosstab into tblNormalize
CurrentDb.Execute "INSERT INTO tblNormalize ( ColorName, SizeOrder, Availability )" & Chr(10) & _
"SELECT Color, " & rst.Fields("SizeOrder") & ", [" & rst.Fields("SizeOrder") & "]" & Chr(10) & _
"FROM tblCrosstab;", dbFailOnError + dbSeeChanges
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

'update the original normalized dataset based on edited dataset
CurrentDb.Execute "UPDATE ((tblNormalize INNER JOIN tblSizes ON tblNormalize.SizeOrder = tblSizes.SizeOrder) " & _
"INNER JOIN tblColors ON tblNormalize.ColorName = tblColors.Color) " & _
"INNER JOIN tblColorSize ON (tblColorSize.Size = tblSizes.SizeID) AND " & _
"(tblColors.ColorID = tblColorSize.Color) " & _
"SET tblColorSize.Available = [Availability];", dbFailOnError + dbSeeChanges

Exit Sub
EH:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Sub

Step 10: Now add a call to the NormalizeIt procedure in the close event of frmShowColorSize.

That's it - you should now be able to open your form, view the data in the crosstab format, edit the data, and have the changes saved.

You can download the sample project (email me, I will send it to you). (This is done in Microsoft Access 2003, and requires a reference to DAO 3.6).

This is a rudimentary implementation of a project, meant to show the basic technique. If you were to actually use this method, at a minimum, you would also need to:

1. Implement error prevention/handling

2. Create an interface for adding/deleting/editing the available colors and sizes.

3. Enforce the ordering sequences of the colors and sizes (i.e., every color and size must be ordered, and the order must be continuous without any breaks in the sequence.)

4. Enforce the maximum number of sizes (i.e., you can't add more sizes than you created text controls for on your crosstab form).

5. Enforce uniqueness on color and size names.

6. Create a procedure to update tblColorSize whenever records are added or deleted in tblColor and tblSize.

7. Validate user input on the crosstab form (i.e., they have to enter Y, N, or S, etc.).

You are free to use this project and/or any of the code in it however you want. I would appreciate your feedback on whether you found this project useful, and any suggestions or improvements you have.

First post

I just have to get the first post out of the way, or I will never write anything for fear of sullying the clean page.  Whew, glad that's over with.