Access Treeview - Is it Safe Yet?
lauren quantrell
recently. Is it safe to swim there yet with Access 2000-Access 2003?
Rick Brandt
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
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
> 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
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
and it looks terrific. No problems. Plus it looks great as well.
Larry Linson
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
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
Are you just teasing us or are you gonna post some code?
It sounds great. Please share.
Bob
lauren quantrell
until then.
Larry Linson
"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
that must be distributed with the app, potentially causing the problems
outlined above?
lauren quantrell
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
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
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
dlesa...@hotmail.com
http://amazecreations.com/datafast/
"WJA" <WJA...@hotmail.com> wrote ...
Mike Gramelspacher
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
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
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
>Access' equivelent (VBA and Queries I assume)?
I took the liberty:
Gord
lauren quantrell
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
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
Excellent! Thank you for spending the time to set this up. And thanks
again to Lauren for the original concept.
--
Bri
Bri
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
>green icon. It makes it all look better.
Done. Updated version at
Gord
Mike Preston
Does that file translate easily to A97?
mike
Bob
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
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
sorry about the double-post.
Bob
Gord
>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
>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
Gord, did you miss this the first time I posted it?
mike
Bri
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
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
Can you send it to me or post it somewhere?
Thanks
mike
Bri
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
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
>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
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
<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
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
Mike Preston
<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
>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