ACCESS FEED: RE: [MS_AccessPros] Find the Active Agent in a Hierarchy MSAccess 2010/2013

Jumat, 24 Juli 2015

RE: [MS_AccessPros] Find the Active Agent in a Hierarchy MSAccess 2010/2013

Duane,

Your approach works fine for this example, I was sending a simple sample however in reality is more complicated. I created a function which is working fine ( see below).

Please let me know if you can suggest an easier way via SQL.

Public Function GetActiveAgent(strAgent As String) As String
Dim sResult As String
Dim strAgentCode As String
Dim strAgentStatus As String
Dim strSupCode As String
Dim dbsDB As Database
Dim rstRS As Recordset
Dim strSQL As String

Set dbsDB = CurrentDb
strAgentCode = strAgent

Do Until Len(sResult) > 0
    strSQL = "SELECT * FROM QS_AgentActive WHERE AgentCode='" & strAgentCode & "'"
    Set rstRS = dbsDB.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    Debug.Print strAgentCode & "-" & rstRS!AgentName & "- Agent Status=" & rstRS!StatusOfAgent & " - Contract Status= " & rstRS!StatusOfContract & " - email: " & rstRS!EMailAddr
    If rstRS!StatusOfAgent = "A" Then
         sResult = strAgentCode
    Else
         strAgentCode = rstRS!SupervisorAgentCode
         If rstRS!AgentCode = rstRS!SupervisorAgentCode Then
            Exit Do
         End If
    End If
Loop
GetActiveAgent = sResult
End Function

Function call from inmediate window:
getActiveAgent("904909")
904909-Agent Status=T - Contract Status= T
904722- Agent Status=T - Contract Status= T
904772- Agent Status=T - Contract Status= T
902170- Agent Status=T - Contract Status= T
900001- Agent Status=T - Contract Status= T
903481- Agent Status=A - Contract Status= T