TreeView

alastair69

Registered User.
Local time
Today, 08:21
Joined
Dec 21, 2004
Messages
562
Hello All,

I have been looking for away of showing only the contacts for a selected accounts person.

an example would be BBC has 10 people (Contacts) his works fine on the tree view

Alastair has 4 of the contacts for the BBC
John Smith has 6 contacts

How can i work this section, if anybody has any ideas please reply.

Alastair


The code i am using is as follows
--------------------------------------------------------------------------
Public Sub FillTree02()
Dim dbs As Database
Dim nodnew As Node
Dim rstAccountList As Recordset
'Craig Swift - PACE
Dim strSQL As String
Dim intAccountManager As Integer
Dim rsAccount As Recordset
Dim datDate As Date
Dim intLoop As Integer
Dim sCompanyName As String

On Error GoTo ErrorHandler

Set dbs = CurrentDb

'datToday = Date
intAccountManager = [Form_JT Logon].AccountManager

'Set nodnew = TreeView.Nodes.Add(, , "All", "Sales")

'Set nodnew = TreeView.Nodes.Add(, , "All", "Sales")
'Me.txtUser = AccountManager

Set nodnew = TreeView.Nodes.Add("All", tvwChild, "Schedule", "Scheduled Calls")
nodnew.Image = 4
For intLoop = 0 To 7

datDate = DateAdd("d", intLoop, Date)

Set nodnew = TreeView.Nodes.Add("Schedule", tvwChild, Format(datDate, "dd/mm/yyyy"), Format(datDate, "dd/mm/yyyy"))

strSQL = "SELECT CompanyName, 'Scheduled Call' as Prefix, Desciption FROM [tbl Scheduler] where [SalesID] = '" & CStr(intAccountManager) & "' "
strSQL = strSQL & " and [Date] >= #" & Format(datDate, "dd mmm yyyy") & "# "
strSQL = strSQL & " and [Date] < #" & Format(DateAdd("d", 1, datDate), "dd mmm yyyy") & "# ORDER BY companyName"
strSQL = strSQL & " UNION "
strSQL = strSQL & " SELECT ClientLookup as CompanyName, [Calltype] as Prefix, CallDetails as Desciption FROM [tbl Call Datails] where [SalesRepresentative] = '" & CStr(intAccountManager) & "' "
strSQL = strSQL & " and [CallBackDate] >= #" & Format(datDate, "dd mmm yyyy") & "# "
strSQL = strSQL & " and [CallBackDate] < #" & Format(DateAdd("d", 1, datDate), "dd mmm yyyy") & "# ORDER BY CompanyName;"


Set rstAccountList = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
'rstAccountList.MoveFirst
While Not rstAccountList.EOF

On Error GoTo DuplicateKeyError

'Don't add to the tree if the company name is already there, otherwise there's a duplicate key error
If rstAccountList!CompanyName <> sCompanyName Then

sCompanyName = rstAccountList!CompanyName

strSQL = "Select * from [JT Client List] where ClientLookUp = '" & rstAccountList!CompanyName & "'"
Set rsAccount = dbs.OpenRecordset(strSQL)

Set nodnew = TreeView.Nodes.Add(Format(datDate, "dd/mm/yyyy"), tvwChild, rsAccount!ClientID & "%" _
, rstAccountList!CompanyName & " - " & rstAccountList!Prefix & " - " & rstAccountList!Desciption)

If nodnew.Key = Me.ItemKey Then
nodnew.Selected = True
End If

Debug.Print rstAccountList!CompanyName

On Error GoTo 0

nodnew.Image = 2
Call AddContacts2(rstAccountList!CompanyName, CStr(rsAccount!ClientID))

End If

NextRecord:
rstAccountList.MoveNext
Wend

Next

Exit Sub

DuplicateKeyError:
'Ignore this error and move onto the next record
Resume NextRecord

ErrorHandler:

MsgBox "An Error has occured in FillTree02: " & Error, vbCritical + vbOKOnly, "Error"

End Sub
--------------------------------------------------------------------------

--------------------------------------------------------------------------
Public Sub AddContacts2(strCompany As String, strID As String)
Dim strSQL As String
Dim dbs As Database
Dim rst As Recordset
Dim nodnew As Node

strSQL = "SELECT * FROM [JT Contact List] WHERE Company = " & Chr$(34) & strCompany & Chr$(34) & ";"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)

While Not rst.EOF
Set nodnew = TreeView.Nodes.Add(strID & "%", tvwChild, _
strID & "%" & rst!ContactID, rst!ContactName)

nodnew.Image = 4
If nodnew.Key = Me.ItemKey Then
nodnew.Selected = True
End If
rst.MoveNext
Wend


End Sub
--------------------------------------------------------------------------
 
Last edited:
Looks like i have finally stubbed you lot

here is a link to my DB password is the password table will need to be relinked:

http://www.braeburn1.demon.co.uk/Database/Matrix.rar
http://www.braeburn1.demon.co.uk/Database/Data.rar

Once loaded you will see a treeview node called scheduler this is the section i am having difficulties with, it will only show all the contacts not the desired contact. To add an item into the scheduler you will need to find the form "JT Scheduler" to recall the main screen use either "JT Logon" or "JT My Company Lists". You should then beable to see my problem as long as you choose a company with more then one contact.

Any comments on this database would be greatfully recieved and noted.

Alastair
 
try cutting the DB down to what is needed for the problem and clearing the security then maybe someone will be able to get in and look at it for you

Peter
 

Users who are viewing this thread

Back
Top Bottom