alastair69
Registered User.
- Local time
- Today, 05:53
- 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
--------------------------------------------------------------------------
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: