Access Treeview - Is it Safe Yet?

43 views
Skip to first unread message

lauren quantrell

unread,
Sep 9, 2005, 12:13:41 AM9/9/05
to
So many postings on not to use the treeview control, but nothing
recently. Is it safe to swim there yet with Access 2000-Access 2003?

Rick Brandt

unread,
Sep 9, 2005, 7:34:04 AM9/9/05
to
"lauren quantrell" <laurenq...@hotmail.com> wrote in message
news:1126239220.9...@g47g2000cwa.googlegroups.com...

> So many postings on not to use the treeview control, but nothing
> recently. Is it safe to swim there yet with Access 2000-Access 2003?

You misunderstand. *Access* doesn't have a Treeview control. Windows has a
TreeView control as part of the Common Dialog library. To use this control in
Access means adding a reference to an external non-native library and it is the
general practice of making your Access app dependent on external libraries that
is to be avoided. This is because of versioning and other "DLL Hell" problems
that will break your app.

The common dialog library has just been one of those more often revised by MS
with the various versions (usually) not being compatible with one another so it
is one of the worst external libraries to consider adding a reference to.
Fortunately, nearly all of the functionality of the common dialog library can be
replicated with API calls, making it unnecessary to add a reference to it.
Unfortunately the Treeview is not one of these.

Now...it is possible that MS has stabilized the common dialog library in recent
years and therefore it is safer to use in your Access app, but this would not be
due to using a newer version of Access. It would be due to you (and your users)
all using a relatively new flavor of Windows. If you can count on that then you
might be alright. Personally I avoid any external references on my Access apps
unless there is absolutely no other way and only then on my corporate apps where
I have some idea and control over what is or isn't installed on the target PCs.
For an app that will be generally distributed it is just not worth the hassles
that will almost certainly occur.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com



lauren quantrell

unread,
Sep 9, 2005, 12:56:57 PM9/9/05
to
Rick,
Thanks for the reply.
In anticipation of the Treeview Control still being an issue, I created
a treeview wannabe from scratch last night (maybe 4 hours work) that
uses one continuous form, one temp table, four stored procedures and
the Wingdings font and 100% replicates the functionality of the Windows
ActiveX Treeview control but also aloows graphical button-like objects
for the dropdowns. My version has two node-like levels though there
would be no limit to how many can be added. Plus it's extremely fast-
loading about 100 levels with 10,000 records. Anyone interested in how
this was done and I'll give more details...
lq

Larry Linson

unread,
Sep 9, 2005, 2:09:17 PM9/9/05
to
"lauren quantrell" wrote

> In anticipation of the Treeview Control still
> being an issue, I created a treeview wannabe
> from scratch last night (maybe 4 hours work)
> that uses one continuous form, one temp table,
> four stored procedures and the Wingdings
> font and 100% replicates the functionality of
> the Windows ActiveX Treeview control but
> also aloows graphical button-like objects
> for the dropdowns. My version has two
> node-like levels though there would be no
> limit to how many can be added. Plus it's

> extremely fast-loading about 100 levels


> with 10,000 records. Anyone interested in how
> this was done and I'll give more details...

Lauren, I think many people would be interested in a done-all-in-Access
substitute for the TreeView. Have you considered posting it somewhere and
making it available after you have done a bit more testing? Release it first
as a 'beta' version to limit the gripes, if you decide to to so.

Larry Linson
Microsoft Access MVP


Danny J. Lesandrini

unread,
Sep 9, 2005, 2:33:44 PM9/9/05
to
An alternative that is really, really great is Access UI.

No, this isn't spam, but it is an endorsement. I reviewed the product
for the Database Journal web site and I'm using it everwhere I can.
The UI is easy to configure and has such a professional look. Check
it out at the AccessUI web site or go read my product review ...

http://www.accessui.com/
http://www.databasejournal.com/features/msaccess/article.php/3519961

--

Danny J. Lesandrini
dlesa...@hotmail.com
http://amazecreations.com/datafast/

"Larry Linson" <bou...@localhost.not> wrote in message news:hdkUe.235$b37.190@trnddc04...

lauren quantrell

unread,
Sep 9, 2005, 3:23:11 PM9/9/05
to
I am planning on doing just that. I've been testing the past few hours
and it looks terrific. No problems. Plus it looks great as well.

Larry Linson

unread,
Sep 10, 2005, 12:22:08 AM9/10/05
to
"lauren quantrell" wrote

I look forward to giving it a try, Lauren.

I really like "controls" that are done all-in-Access so I don't have to
worry about distributing other "things" with my application that have to be
loaded, registered, etc..

lauren quantrell

unread,
Sep 10, 2005, 7:03:14 PM9/10/05
to
Larry,
I rewrote the code to make it more generic (Level1ID instead of
SalesTeamID, etc.) and am populating more than one recordset with it.
It works great.

Bob

unread,
Sep 10, 2005, 10:10:44 PM9/10/05
to
Lauren -

Are you just teasing us or are you gonna post some code?

It sounds great. Please share.

Bob

lauren quantrell

unread,
Sep 10, 2005, 11:48:56 PM9/10/05
to
I hope to do so by Monday morning. It's cramming on a deadline issue
until then.

Larry Linson

unread,
Sep 11, 2005, 1:21:52 AM9/11/05
to

"lauren quantrell" wrote

> I hope to do so by Monday morning.
> It's cramming on a deadline issue
> until then.

Good luck with the "all weekender" -- I've pulled a few of those over the
last few average-programmer-lifetimes. You may want to give yourself a day
or two to recover before worrying about posting code. :-)

WJA

unread,
Sep 11, 2005, 2:24:27 AM9/11/05
to
Pardon my ignorance folks, but is the AccessUI just another control
that must be distributed with the app, potentially causing the problems
outlined above?

lauren quantrell

unread,
Sep 11, 2005, 8:27:48 PM9/11/05
to
Here goes. I'll call the project SmartTree.

SmartTree : Native Access Treeview Functionality by Lauren Quantrell

This provides the complete treeview functionality without a single
addin or additional reference using only native MS Access parts. And it
does so with graphics options not available using the Windows Treeview
addin.

For the sake of allowing users to more easily see how this is done, I
have combined what logically would be two tables into one table:
tblContactTypes. Also, when you construct the form, you must have the
cmd button controls above the text controls (use the bring to front
option.) Also, once the form is constructed, you can use the
Conditional Formatting option to colorize the buttons and rows.
Experiment with that.

Have fun with this,
lq


TABLES:

Table name: tblContacts
Rows:
ContactID int PK IDENTITY
Company nvarchar(100)
ContactTypeID int
PhoneNumber nvarchar(50)

Populate the table:
1 ABC Company 1 212-555-1212
2 DEF Company 2 202-555-1212
3 GHI Company 2 213-555-1212
4 JKL Company 3 818-555-1212
5 MNO Company 4 917-555-1212
__________________________________________________

Table name: tblContactTypes
Rows:
ContactTypeID int PK Identity (DowID)
ContactType nvarchar(50) (DowLabel)
ContactGroupID int (DowCategoryGroupID)
ContactGroup nvarchar(50) (DowCategory)

Populate the table:
1 Fast Food 1 Restaurant
2 Fine Dining 1 Restaurant
3 Flop House 2 Hotel
4 Resort Inn 2 Hotel
__________________________________________________

Table name: tblContactTypesTEMP
Rows:
TempID int PK Identity
ContactTypeID int (DowID)
ContactType nvarchar(50) (DowLabel)
ContactGroupID int (DowCategoryGroupID)
ContactGroup nvarchar(50) (DowCategory)
GroupVisible bit (CategoryVisible)
TypeVisible bit (LabelVisible)
MachName nvarchar(100)

This table will be populated with a stored procedure
__________________________________________________


STORED PROCEDURES:

Alter Procedure TreeviewOpen
/* SmartTree : Native Access Treeview Functionality by Lauren
Quantrell */

AS

set nocount on

DECLARE @CountRecords int
DECLARE @myUserMachineName nvarchar(100)
SET @myUserMachineName = HOST_NAME()

/* NOTE: The following scheme allows the user to maintain the previous
treeview settings from session to session */

SELECT @CountRecords = (SELECT COUNT(TempID) AS CountID FROM
dbo.tblContactTypesTEMP WHERE (MachName = @myUserMachineName))

IF @CountRecords = 0 /* Populate the temp table only if it is a new
user's machine */

BEGIN

INSERT INTO dbo.tblContactTypesTEMP(ContactTypeID,
ContactGroupID,ContactGroup, ContactType, GroupVisible, TypeVisible,
MachName)
SELECT
t.ContactTypeID, t.ContactGroupID, t.ContactGroup, t.ContactType, 0, 0,
@myUserMachineName
FROM
dbo.tblContactTypes t

END

__________________________________________________

Alter Procedure TreeviewRS
/* SmartTree : Native Access Treeview Functionality by Lauren
Quantrell */

AS

set nocount on

DECLARE @myUserMachineName nvarchar(100) /* used to hold the current
uer's machine name */
SET @myUserMachineName = HOST_NAME() /* get the current user's
machine name */

/* Create the # temp table: */

CREATE TABLE #Treeview (id int identity, DisplayText nvarchar(105),
SortOrder int, L1ID int, L1x char(1), L1Text nvarchar(100), L2ID int,
L2x char(1), L2Text nvarchar(100), L3ID int, L3x char(1), L3Text
nvarchar(100))

/* INSERT FIRST LEVEL: Group*/

INSERT INTO #Treeview(DisplayText, SortOrder, L1x ,L1Text, L1ID, L2ID,
L3ID)
SELECT
UPPER(t.ContactGroup), /* first level text to display */
0, /* first level sort order = 0 - DO NOT CHANGE */
CASE WHEN t.GroupVisible = 0 THEN 'x' ELSE 'y' END, /* first level
arrow to display - DO NOT CHANGE */
t.ContactGroup, /* first level text for sorting */
t.ContactGroupID, /* first level uniqueID */
0, /* second level ID = 0 - DO NOT CHANGE */
0 /* third level ID = 0 - DO NOT CHANGE */
FROM
dbo.tblContactTypesTEMP t
WHERE
(t.MachName = @myUserMachineName)
GROUP BY
t.ContactGroup, t.ContactGroupID,GroupVisible

/* INSERT SECOND LEVEL: Type*/

INSERT INTO #Treeview(DisplayText, SortOrder, L2x, L1Text, L2Text,
L1ID, L2ID, L3ID)
SELECT
' ' + t.ContactType, /* second level text to display
*/
1, /* second level sort order = 1 - DO NOT CHANGE */
CASE WHEN t.TypeVisible = 0 THEN 'x' ELSE 'y' END, /* second level
arrow to display - DO NOT CHANGE */
t.ContactGroup, /* first level text for sorting */
t.ContactType, /* second level text for sorting */
t.ContactGroupID, /* first level uniqueID */
t.ContactTypeID, /* second level ID */
0 /* third level ID = 0 - DO NOT CHANGE */
FROM
dbo.tblContactTypesTEMP t
WHERE
(MachName = @myUserMachineName) /* match the user's machine to the
temp table's values */
AND
(t.GroupVisible = 1) /* only insert second level where first level
is visible */

/* INSERT THIRD LEVEL: Contacts */

INSERT INTO #Treeview(DisplayText, SortOrder, L3x, L1Text, L2Text,
L3Text, L1ID, L2ID, L3ID)
SELECT
' ' + c.Company, /* third level text to display
*/
2, /* third level sort order = 2 - DO NOT CHANGE */
'l', /* third level dot to display - DO NOT CHANGE */

t.ContactGroup, /* first level text for sorting */
t.ContactType, /* second level text for sorting */
c.Company, /* third level text for sorting */
t.ContactGroupID, /* first level uniqueID */
c.ContactTypeID, /* second level ID */
c.ContactID /* third level ID */
FROM
dbo.tblContactTypesTEMP t
RIGHT OUTER JOIN
dbo.tblContacts c ON t.ContactTypeID = c.ContactTypeID
WHERE
(t.MachName = @myUserMachineName) /* match the user's machine to the
temp table's values */
AND
(t.GroupVisible = 1) /* only insert third level where first level
is visible */
AND
(t.TypeVisible = 1) /* only insert third level where second level
is visible */

/* CREATE THE RECORDSET: */

SELECT
DisplayText,
L1x,
L2x,
L3x,
L1ID,
L2ID,
L3ID,
/*subquery gathers phone number (this is thrown in for illustration
purposes to show how to easily add more columns to the treeview
display:*/
(SELECT c.PhoneNumber FROM dbo.tblContacts c WHERE (c.ContactID =
#Treeview.L3ID) as PhoneNumber
FROM
#Treeview
ORDER BY
L1Text,L2Text,SortOrder,L3Text

__________________________________________________


Alter Procedure TreeviewGroup
@parGroupID int,
@parValue int

/* SmartTree : Native Access Treeview Functionality by Lauren
Quantrell */

AS

set nocount on

DECLARE @myUserMachineName nvarchar(100)
SET @myUserMachineName = HOST_NAME()

UPDATE dbo.tblContactTypesTEMP
SET dbo.tblContactTypesTEMP.GroupVisible = @parValue
FROM
dbo.tblContactTypesTEMP
WHERE
(dbo.tblContactTypesTEMP.ContactGroupID = @parGroupID)
AND
(dbo.tblContactTypesTEMP.MachName = @myUserMachineName)


__________________________________________________


Alter Procedure TreeviewType
@parGroupID int,
@parValue int

/* SmartTree : Native Access Treeview Functionality by Lauren
Quantrell */

AS

set nocount on

DECLARE @myUserMachineName nvarchar(100)
SET @myUserMachineName = HOST_NAME()

UPDATE dbo.tblContactTypesTEMP
SET dbo.tblContactTypesTEMP.TypeVisible = @parValue
FROM
dbo.tblContactTypesTEMP
WHERE
(dbo.tblContactTypesTEMP.ContactTypeID = @parGroupID)
AND
(dbo.tblContactTypesTEMP.MachName = @myUserMachineName)


__________________________________________________

Alter Procedure TreeviewExpandCollapse
@parGroupID int, /*not used */
@parValue int

/* SmartTree : Native Access Treeview Functionality by Lauren
Quantrell */

AS

set nocount on

DECLARE @myUserMachineName nvarchar(100)
SET @myUserMachineName = HOST_NAME()

UPDATE dbo.tblContactTypesTEMP
SET dbo.tblContactTypesTEMP.GroupVisible = @parValue,
dbo.tblContactTypesTEMP.TypeVisible = @parValue
FROM
dbo.tblContactTypesTEMP
WHERE
(dbo.tblContactTypesTEMP.MachName = @myUserMachineName)

__________________________________________________


FORM:

Form name: frmTreeviewExample
RecordSource = dbo.TreeviewRS
Default View: Continuous Forms
Views Allowed: Form
Scroll Bars: Vertical Only
Record Selectors: No
Navigation Buttons: No
Dividing Lines: No
FormHeader Height: 0.3646"
FormFooterHeight: 0"
Detail Height: 0.1813"

Create the following controls:

In the Form Header:

Label
Name: LabelX
Caption: x
FontName: Wingdings
FontSize: 10

Label
Name: LabelY
Caption: y
FontName: Wingdings
FontSize: 10

Label
Name: P1
Caption: collapse all levels
FontName: Arial
FontSize: 8

Label
Name: P2
Caption: expand all levels
FontName: Arial
FontSize: 8

In the Form Detail:

TextBox
Name: DisplayText
ControlSource: DisplayText
FontName: Arial
FontSize: 8
BackStyle: Transparent
BorderStyle: Transparent
Left: 0.3125"
Top: 0.0104"
Width: 4.1667"
Height: 0.166"

TextBox
Name: L1X
ControlSource: L1X
FontName: Wingdings
FontSize: 12
BackStyle: Normal
BorderStyle: Transparent
Left: 0.0833"
Top: 0"
Width: 0.1979"
Height: 0.166"

TextBox
Name: L2X
ControlSource: L2X
FontName: Wingdings
FontSize: 12
BackStyle: Normal
BorderStyle: Transparent
Left: 0.2708"
Top: 0"
Width: 0.1979"
Height: 0.166"

TextBox
Name: L3X
ControlSource: L3X
FontName: Wingdings
FontSize: 10
BackStyle: Transparent
BorderStyle: Transparent
Left: 0.4792""
Top: 0.0104"
Width: 0.1979"
Height: 0.1556"

CommandButton
Name: cmd1
Transparent: Yes
Visible: Yes
Left: 0.0938"
Top: 0"
Width: 0.2021"
Height: 0.1813"

CommandButton
Name: cmd2
Transparent: Yes
Visible: Yes
Left: 0.2813"
Top: 0"
Width: 0.2021"
Height: 0.1813"

CommandButton
Name: cmd3
Transparent: Yes
Visible: Yes
Left: 0.4896"
Top: 0.0104"
Width: 0.1604"
Height: 0.1604"


Put the following code in the form's code module:

' START CODE:

Option Compare Database
Option Explicit
' SmartTree : Native Access Treeview Functionality by Lauren Quantrell


Private Sub Form_Open(Cancel As Integer)
On Error GoTo myErr

Dim mySP As String, cmd As ADODB.Command

mySP = "dbo.TreeviewOpen"

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandTimeout = 0
cmd.CommandText = mySP
cmd.CommandType = adCmdStoredProc
cmd.Execute , , Options:=adExecuteNoRecords

myExit:
On Error Resume Next
Set cmd = Nothing
Exit Sub
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub


Private Sub cmdL1_Click()
On Error GoTo myErr

Dim myL1ID As Long, myVisible As String, newVisible As Integer,
mySP As String

If Me!L2ID = 0 Then

'it is a Group so proceed:

myL1ID = Me!L1ID
myVisible = Me.L1X

If myVisible = "x" Then
'it is not visible now so new value is visible:
newVisible = 1
Else
'it is visible now so new value is not visible:
newVisible = 0
End If

mySP = "dbo.TreeviewGroup"

Call ADOUpdateLevel(mySP, myL1ID, newVisible)

'requery the form recordsource:
Me.RecordSource = Me.RecordSource

'go to the original Group after requery:
Dim RS As ADODB.Recordset, strCriteria As String
strCriteria = "[L1ID]=" & myL1ID
Set RS = Me.RecordsetClone
RS.Find strCriteria
If Not RS.EOF Then Me.Bookmark = RS.Bookmark
RS.Close
Set RS = Nothing

End If

myExit:
Exit Sub
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub

Private Sub cmdL2_Click()
On Error GoTo myErr

Dim myL2ID As Long, myVisible As String, newVisible As Integer,
mySP As String

myL2ID = Me!L2ID

If myL2ID > 0 And Me!L3ID = 0 Then

'it is a Type so proceed:

myVisible = Me.L2X

If myVisible = "x" Then
'it is not visible now so new value is visible:
newVisible = 1
Else
'it is visible now so new value is not visible:
newVisible = 0
End If

mySP = "dbo.TreeviewType"

Call ADOUpdateLevel(mySP, myL2ID, newVisible)

'requery the form recordsource:
Me.RecordSource = Me.RecordSource

'go to the original DowID after requery:
Dim RS As ADODB.Recordset, strCriteria As String
strCriteria = "[L2ID]=" & myL2ID
Set RS = Me.RecordsetClone
RS.Find strCriteria
If Not RS.EOF Then Me.Bookmark = RS.Bookmark
RS.Close
Set RS = Nothing

End If

myExit:
Exit Sub
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub

Private Sub cmdL3_DblClick(Cancel As Integer)
On Error GoTo myErr

Dim myL3ID As Long

myL3ID = Me!L3ID

If myL3ID > 0 Then

'PUT WHATEVER CODE HERE YOU USE TO OPEN A CONTACT FORM
'docmd.openform etc. WHERE [ContactID] = myL3ID

End If

myExit:
Exit Sub
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub

Private Function ADOUpdateLevel(mySP As String, myID As Long,
newVisible As Integer)
On Error GoTo myErr

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandTimeout = 0
cmd.CommandText = mySP
cmd.CommandType = adCmdStoredProc
cmd.Execute , Parameters:=Array(myID, newVisible),
Options:=adExecuteNoRecords

myExit:
On Error Resume Next
Set cmd = Nothing
Exit Function
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Function

Private Sub LabelX_Click()
On Error GoTo myErr

Call CollapseTree

myExit:
Exit Sub
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub

Private Sub LabelY_Click()
On Error GoTo myErr

Call ExpandTree

myExit:
Exit Sub
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub

Private Sub P1_Click()
On Error GoTo myErr

Call CollapseTree

myExit:
Exit Sub
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub

Private Sub P2_Click()
On Error GoTo myErr

Call ExpandTree

myExit:
Exit Sub
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Sub

Private Function CollapseTree()
On Error GoTo myErr

Dim mySP As String

mySP = "dbo.TreeviewExpandCollapse"

Call ADOUpdateLevel(mySP, 0, 0)

'requery the form recordsource:
Me.RecordSource = Me.RecordSource

myExit:
Exit Function
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Function

Private Function ExpandTree()
On Error GoTo myErr

Dim mySP As String

mySP = "dbo.TreeviewExpandCollapse"

Call ADOUpdateLevel(mySP, 0, 1)

'requery the form recordsource:
Me.RecordSource = Me.RecordSource

myExit:
Exit Function
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Function

'END CODE

lauren quantrell

unread,
Sep 11, 2005, 8:51:29 PM9/11/05
to
CORECTION: I left some notes in the previous post that might be
confusing. This posting clears those. Also, I forgot to mention this
example is a three-level (nodes) tree though there's no limit to how
many levels you could include by adding the required
[(fieldname)Visible] columns to the table tblContactTypes. lq
--------------------------


TABLES:

ContactType nvarchar(50)
ContactGroupID int
ContactGroup nvarchar(50)

Populate the table:


1 Fast Food 1 Restaurant
2 Fine Dining 1 Restaurant
3 Flop House 2 Hotel
4 Resort Inn 2 Hotel
__________________________________________________

Table name: tblContactTypesTEMP
Rows:
TempID int PK Identity
ContactTypeID int

ContactType nvarchar(50)
ContactGroupID int
ContactGroup nvarchar(50)
GroupVisible bit
TypeVisible bit

Danny J. Lesandrini

unread,
Sep 12, 2005, 9:14:04 AM9/12/05
to
The AccessUI does incorporate some third party controls that need to be
distributed and registered. This adds to the complexity of the install, but
I've already used it at 3 clients and the process is painless. Now, my clients
have less than 20 users at each site, so it was relatively easy to manage the
installs, etc. I might think twice for an implementation of 100 users.

I too, used to be a "I only like native controls" developer, but I've changed
my mind. As a developer who does product reviews, I must admit that I've
skipped opportunities to review custom controls from several vendors. I
just didn't want the hassle. So, why did I review AccessUI?

The author of AccessUI, Kevin Bell, was the VP of the Denver Area Access
User Group (www.daaug.org) at the time and at one of our labs, he showed
me what he had created. He did the work of ferreting out the bugs and
issues with the 3rd party controls and obfuscated the complexities. Sure,
there are some quirks that have popped up, but they're the kind of issues
(like window resize errors due to screen resolution) that don't affect the
app itself. My clients are so blown away by the UI they can put up with a
few minor issues. Plus, as these things get reported back to Kevin, the UI
will improve with new releases, one of which I just received this weekend.

What can I say ... I'm a convert. I love the "wiz-bang" that comes when I
show my potential clients what their app will look like and how it behaves.
They love the Outlook style band-menu on screen-left and the hierarchal
menus that display their data at a glance. I love the ease with which I can
add new menu items and last week I discovered how to implement security,
hiding options from users who don't have sufficient rights.

If this sounds like a commercial, I apologize, but I'm not promoting this
product for myself. I'm not in business with Kevin and get nothing in
return. I just love the product and wanted to share my experience.
--

"WJA" <WJA...@hotmail.com> wrote ...

Mike Gramelspacher

unread,
Sep 12, 2005, 5:19:53 PM9/12/05
to
I am curious about the problems reported using the Windows Treeview control.
I have Office 2003 Professional and mscomctl.ocx version 6.0.88.62 dated 22
May 2000. Can I assume that any computer with an Access version after that
date probably would have this same version and would not have a
compatibility problem with a Treeview control in my Access program?

A comment about the Access Treeview code posted here. I only use Access
running against Jet on a desktop computer. Code using SQL Server and stored
procedures does not mean a lot to me. I wonder if other people are thinking
the same thing. There have not been any comment about the code.

Mike Gramelspacher
Ferdinand, Indiana


David W. Fenton

unread,
Sep 12, 2005, 8:08:37 PM9/12/05
to
"Danny J. Lesandrini" <dlesa...@hotmail.com> wrote in
news:RLCdne4AzOS...@comcast.com:

> I love the "wiz-bang" that comes when I
> show my potential clients what their app will look like and how it
> behaves. They love the Outlook style band-menu on screen-left

I've never seen the Outlook-style UI as particularly helpful, but if
you wanted to implement it, wouldn't it be darned easy to do so with
a continuous subform displaying image controls populated from a
table with bitmaps stored in OLE fields?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Bri

unread,
Sep 14, 2005, 1:07:50 PM9/14/05
to
Lauren,

Not that I don't appreciate your efforts here, I do, but how can a
'Native Access Treeview' be so dependant on being used with SQL Server
and SPs?

As Mike mentioned, many of us (most?) don't use SQL Server for all
(any?) of our apps so this doesn't help them. Can you (or some other SP
guru) convert these SPs to the 'Native Access' equivelent (VBA and
Queries I assume)?

Thanks,

--
Bri

Gord

unread,
Sep 15, 2005, 11:09:41 AM9/15/05
to
>Can you (or some other SP guru) convert these SPs to the 'Native
>Access' equivelent (VBA and Queries I assume)?

I took the liberty:

http://tinyurl.com/d2968


Gord

lauren quantrell

unread,
Sep 15, 2005, 11:54:48 AM9/15/05
to
I created this method of providing a completely Common Dialog
library-free treeview control for my applications. Since the stored
procedures are only queries, there's no reason you could not simply
copy the SQL Server SQL verbatim into Access queries to accomplish the
same result, and replace the #temptables (used only SQL Server) with
Access physical tables.
lq

lauren quantrell

unread,
Sep 15, 2005, 12:07:13 PM9/15/05
to
Terrific job on that! Looks exactly as I intended.
One suggestion: On control L1X set L1X.BackColor = 16777215 and in
conditional formatting put Expression = [L1X]='x' and select font =
Red
In this way, if the tree is expanded, it's a red icon, otherwise a
green icon. It makes it all look better.
I have also adapted my original SQL Server method to include two,
three, four-level etc. trees with no headache.
lq

Bri

unread,
Sep 15, 2005, 1:10:06 PM9/15/05
to

Excellent! Thank you for spending the time to set this up. And thanks
again to Lauren for the original concept.

--
Bri


Bri

unread,
Sep 15, 2005, 1:15:01 PM9/15/05
to

You are correct. As soon as I saw all of that T-SQL my eyes glazed over
and I didn't really absorb what was actually there. A second look made
much more sense to me.

Thanks,
Bri


Gord

unread,
Sep 16, 2005, 12:26:13 PM9/16/05
to
> In this way, if the tree is expanded, it's a red icon, otherwise a
>green icon. It makes it all look better.

Done. Updated version at

http://tinyurl.com/d2968


Gord

Mike Preston

unread,
Sep 16, 2005, 8:46:23 PM9/16/05
to

Does that file translate easily to A97?

mike

Bob

unread,
Sep 17, 2005, 7:57:47 AM9/17/05
to
Lauren -

Have converted it to A97 and it works really well -- a great display!
I'm a little confused as to its functionality.

Would you provide some guidance as to how the user (in the fully
expanded mode) could click on 'ABC Company' and invoke procedure 'X',
or click on 'GHI Company' and invoke procedure 'Y', etc.. I don't see
either of these happening and admittedly I'm a little dense.

Best wishes,

Bob

Bob

unread,
Sep 17, 2005, 8:01:27 AM9/17/05
to
Lauren -

It's neat and impressive and certainly makes an attractive display.
Guess I'm a little dense here, but could you show its functionality,
i.e., if the user (in the fully expanded mode using your example)
clicked on 'ABC Company', it triggered x, and if the user clicked on
'GHI Company' it triggered y. In other words, how do you use it to
accomplish anything?

Best wishes

Bob

Bob

unread,
Sep 17, 2005, 6:02:08 PM9/17/05
to

sorry about the double-post.

Bob

Gord

unread,
Sep 19, 2005, 6:54:33 AM9/19/05
to
Bob,

>how do you use it to accomplish anything?

You would simply add code to the [frmTreeviewExample] form to perform
whatever action you wish. In the DAO/Jet version that I ported from
Lauren's original, the code behind the contact-level button (i.e., the
bullet beside the Contact name) is

Private Sub cmdL3_Click()
' [no code provided]
End Sub

You could change that to something like

Private Sub cmdL3_Click()
MsgBox "You just clicked on the button for """ & Trim(DisplayText) &
""""
End Sub

as a simple example.


Gord

polite person

unread,
Sep 19, 2005, 6:16:08 PM9/19/05
to
On Mon, 19 Sep 2005 17:04:06 GMT, Bri <n...@here.com> wrote:


>Everything seems to work as described. The Conditional Formating will be
>lost in the Conversion since it did not exist in AC97.

If you mean using different formats for +ve,-ve, 0, null then that is in Access97. At one time
posting how to get different records to be in different colours was the most posted topic
in this group.
Yes, it would be very useful to have the A97 version posted. It is bizarre that in this day and
age (I download 10 gig a month and am not unusual) there is no Access binary group but text
would be fine if you had the time to do it.

Mike Preston

unread,
Sep 19, 2005, 11:41:12 AM9/19/05
to

Gord, did you miss this the first time I posted it?

mike

Bri

unread,
Sep 19, 2005, 1:04:06 PM9/19/05
to

Mike,

I opened the ver 1.0 in AK2K3 and did a Convert to AC97. I removed the
Reference to ActiveX and changed the DAO 3.6 Reference to DAO 3.51.

Everything seems to work as described. The Conditional Formating will be
lost in the Conversion since it did not exist in AC97.

--
Bri


Gord

unread,
Sep 19, 2005, 7:58:51 PM9/19/05
to
>Gord, did you miss this the first time I posted it?

No. Bob had since said that he had converted it to A97 and it "works
really well". I don't support A97 any more so I didn't follow up.

Mike Preston

unread,
Sep 19, 2005, 5:05:33 PM9/19/05
to
On Mon, 19 Sep 2005 17:04:06 GMT, Bri <n...@here.com> wrote:

Can you send it to me or post it somewhere?

Thanks

mike

Bri

unread,
Sep 20, 2005, 12:20:03 PM9/20/05
to

OK,

Keep in mind that this is only a conversion of the 1.0 version that Gord
created. I take no credit for its creation or responsibility for its
functionality.

http://members.shaw.ca/b-r-i/access/SmartTreeDAO.v1.0.97.zip

--
Bri

Bri

unread,
Sep 20, 2005, 12:21:24 PM9/20/05
to

No Bob in this thread. I converted it and have posted it to:
http://members.shaw.ca/b-r-i/access/SmartTreeDAO.v1.0.97.zip

--
Bri

Mike Preston

unread,
Sep 20, 2005, 1:54:57 PM9/20/05
to
On Tue, 20 Sep 2005 16:20:03 GMT, Bri <n...@here.com> wrote:

>Mike Preston wrote:
>> On Mon, 19 Sep 2005 17:04:06 GMT, Bri <n...@here.com> wrote:
>>
>>> Mike Preston wrote:
>>>
>>>>On 16 Sep 2005 09:26:13 -0700, "Gord" <g...@kingston.net> wrote:
>>>>
>>>>
>>>>
>>>>>>In this way, if the tree is expanded, it's a red icon, otherwise a
>>>>>>green icon. It makes it all look better.
>>>>>
>>>>>Done. Updated version at
>>>>>
>>>>>http://tinyurl.com/d2968
>>>>
>>>>
>>>>Does that file translate easily to A97?
>>>>
>>>>mike
>>>
>>>Mike,
>>>
>>>I opened the ver 1.0 in AK2K3 and did a Convert to AC97. I removed the
>>>Reference to ActiveX and changed the DAO 3.6 Reference to DAO 3.51.
>>>Everything seems to work as described. The Conditional Formating will be
>>>lost in the Conversion since it did not exist in AC97.
>>
>>
>> Can you send it to me or post it somewhere?
>>
>> Thanks
>>
>> mike
>
>OK,
>
>Keep in mind that this is only a conversion of the 1.0 version that Gord
>created. I take no credit for its creation or responsibility for its
>functionality.

It appears to work fine. Thanks.

mike


lauren quantrell

unread,
Sep 20, 2005, 11:09:30 PM9/20/05
to
The UniqueID is retained in the recordsource of each level, so by
clicking the dot to the left of ABC Company you could put code in the
OnClick event of the hidden command button that does something like:
DoCmd. OpenForm ... WHERE [ContactID] =
Me!L3ID...
I hope this helps

Mike Preston

unread,
Sep 21, 2005, 1:31:15 PM9/21/05
to
On 20 Sep 2005 20:09:30 -0700, "lauren quantrell"
<laurenq...@hotmail.com> wrote:

Or, you could expand the footer area and put a command button or two
if you need to do more than just one thing.

mike

Lauren Quantrell

unread,
Sep 23, 2005, 7:37:22 PM9/23/05
to
I created this to be as simple as possible but to still afford a lot of
functionality per row, which is why the Unique ID of each level of the
tree is carried through each row.
I have adapted my original two-level SmartTree to three and four level
trees very simply.
The beauty of this is that since you essentially create your own joins
with use of the Temp table, you can create rather arbitrary joins with
your data and create as many levels as you want.
A (rediculous) four level treeview:
Hair Color>Country>Shoe Size>Contact Name

Lauren Quantrell

unread,
Sep 23, 2005, 7:39:32 PM9/23/05
to

Mike Preston

unread,
Sep 24, 2005, 6:55:16 AM9/24/05
to
On 23 Sep 2005 16:39:32 -0700, "Lauren Quantrell"
<laurenq...@hotmail.com> wrote:

Well, I'm at a loss to see how expanding the number of levels can be
done "simply", especially since there are parts of the code that are
specific to the level. My definition of "simple" is that you add a
few records to a table or two and the rest happens automatically!
<g,d&r>

I'm attempting to parameterize it. If I ever get it to the point that
it works (g), I'll post back. I'm starting with the idea that the
levels available have to be at least 12. I think once I get there
that the max limit will mean very little.

I think this is a great tool and I thank you for laying it out the way
you did.

Somebody asked what you can do with the shell. My first use is t
replace Windows Explorer in looking up my client files. I have a
predefined directory structure for every project. I build the
directories at the start of the project, but that means I have a lot
of empty directories (signifying work that needs to get done) for a
long time. The two things that bug me about Windows Explorer is that
the search capability built into the address bar only works on the
beginning of the name and that the icon for an empty directory is the
same as an icon for a directory with something in it.

Using your treeview I can cure both problems. Then if the "action"
associated with double clicking the DisplayText is to hyperlink to the
file name, I can at least use this for file access. Adding a custom
menu which allows delete, copy and paste wouldn't be too hard to do
after that. But that is a ways down the pike.

I'll probably drop the treeview into a SubForm along the left,
intending that a selection of a particular treeview branch opens up a
form in another subform to the right. Sort of the way that The Access
Web (www.mvps.org/access) does it, although their left hand frame
isn't a treeview.

mike

Lauren Quantrell

unread,
Sep 24, 2005, 10:53:55 PM9/24/05
to
>Well, I'm at a loss to see how expanding the number of levels can be
>done "simply", especially since there are parts of the code that are
>specific to the level. My definition of "simple" is that you add a
>few records to a table or two and the rest happens automatically!
<g,d&r>

When I posted SmartTree in SQL Server friendly mode I named columns so
that it would be easy to follow.
In actual use, the xVisible columns should all be renamed L1Visible,
L2Visible, L3Visible, etc. A new column named "HowManyLevels" carries
the instruction (as a parameter) to the sproc how many LxVisible
columns are relevant, depending on how many levels in the current
treeview. The same parameter instructs the form how many cmdX (and Lx)
fields are visible.
In this way, the Treeview can be used with as many levels as the user
desires (the maximum number of levels being the number of cmdX/Lx
fields are copied and pasted into the continuous form.

When I posted this I didn't intend it to be a tool "as is" rather a
simple demonstration of how simple the whole thing is to construct
without ever needing a treeview add-in and all the problems associated
with distributing the Windows component that makes it work.
lq

Reply all
Reply to author
Forward
0 new messages