By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,659 Members | 910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,659 IT Pros & Developers. It's quick & easy.

Access Treeview - Is it Safe Yet?

P: n/a
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?

Nov 13 '05 #1
Share this Question
Share on Google+
42 Replies


P: n/a
"lauren quantrell" <la*************@hotmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.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



Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a
"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
Nov 13 '05 #4

P: n/a
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/featu...le.php/3519961

--

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

"Larry Linson" <bo*****@localhost.not> wrote in message news:hdkUe.235$b37.190@trnddc04...
"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

Nov 13 '05 #5

P: n/a
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.

Nov 13 '05 #6

P: n/a
"lauren quantrell" wrote
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.


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..

Larry Linson
Microsoft Access MVP
Nov 13 '05 #7

P: n/a
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.

Nov 13 '05 #8

P: n/a
Bob
Lauren -

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

It sounds great. Please share.

Bob

Nov 13 '05 #9

P: n/a
I hope to do so by Monday morning. It's cramming on a deadline issue
until then.

Nov 13 '05 #10

P: n/a

"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. :-)

Larry Linson
Microsoft Access MVP
Nov 13 '05 #11

P: n/a
WJA
Pardon my ignorance folks, but is the AccessUI just another control
that must be distributed with the app, potentially causing the problems
outlined above?

Nov 13 '05 #12

P: n/a
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

Nov 13 '05 #13

P: n/a
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
--------------------------
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
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
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

Nov 13 '05 #14

P: n/a
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.
--

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

"WJA" <WJ****@hotmail.com> wrote ...
Pardon my ignorance folks, but is the AccessUI just another control
that must be distributed with the app, potentially causing the problems
outlined above?

Nov 13 '05 #15

P: n/a
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
Nov 13 '05 #16

P: n/a
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in
news:RL********************@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
Nov 13 '05 #17

P: n/a
Bri
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

Nov 13 '05 #18

P: n/a
>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

Nov 13 '05 #19

P: n/a
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

Nov 13 '05 #20

P: n/a
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

Nov 13 '05 #21

P: n/a
Bri
Gord wrote:
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


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

--
Bri
Nov 13 '05 #22

P: n/a
Bri
lauren quantrell wrote:
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


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
Nov 13 '05 #23

P: n/a
> 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

Nov 13 '05 #24

P: n/a
On 16 Sep 2005 09:26:13 -0700, "Gord" <gd*@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
Nov 13 '05 #25

P: n/a
Bob
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

Nov 13 '05 #26

P: n/a
Bob
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

Nov 13 '05 #27

P: n/a
Bob

sorry about the double-post.

Bob

Nov 13 '05 #28

P: n/a
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

Nov 13 '05 #29

P: n/a
On Sat, 17 Sep 2005 00:46:23 GMT, mb******@pacbell.net.invalid (Mike
Preston) wrote:
On 16 Sep 2005 09:26:13 -0700, "Gord" <gd*@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?


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

mike
Nov 13 '05 #30

P: n/a
Bri
Mike Preston wrote:
On 16 Sep 2005 09:26:13 -0700, "Gord" <gd*@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.

--
Bri
Nov 13 '05 #31

P: n/a
On Mon, 19 Sep 2005 17:04:06 GMT, Bri <no*@here.com> wrote:
Mike Preston wrote:
On 16 Sep 2005 09:26:13 -0700, "Gord" <gd*@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
Nov 13 '05 #32

P: n/a
On Mon, 19 Sep 2005 17:04:06 GMT, Bri <no*@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.

Nov 13 '05 #33

P: n/a
>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.

Nov 13 '05 #34

P: n/a
Bri


Mike Preston wrote:
On Mon, 19 Sep 2005 17:04:06 GMT, Bri <no*@here.com> wrote:

Mike Preston wrote:
On 16 Sep 2005 09:26:13 -0700, "Gord" <gd*@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.

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

--
Bri

Nov 13 '05 #35

P: n/a
Bri

Gord wrote:
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.


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

--
Bri

Nov 13 '05 #36

P: n/a
On Tue, 20 Sep 2005 16:20:03 GMT, Bri <no*@here.com> wrote:
Mike Preston wrote:
On Mon, 19 Sep 2005 17:04:06 GMT, Bri <no*@here.com> wrote:
Mike Preston wrote:

On 16 Sep 2005 09:26:13 -0700, "Gord" <gd*@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
Nov 13 '05 #37

P: n/a
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

Nov 13 '05 #38

P: n/a
On 20 Sep 2005 20:09:30 -0700, "lauren quantrell"
<la*************@hotmail.com> wrote:
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


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
Nov 13 '05 #39

P: n/a
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

Nov 13 '05 #40

P: n/a
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

Nov 13 '05 #41

P: n/a
On 23 Sep 2005 16:39:32 -0700, "Lauren Quantrell"
<la*************@hotmail.com> wrote:
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


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

Nov 13 '05 #42

P: n/a
>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

Nov 13 '05 #43

This discussion thread is closed

Replies have been disabled for this discussion.