Finding value from a range and if not match then find next using data from found row

[annux3]

Hello,

I have a table that contains passive and active devices. I want to find correct active device to which passive device is connected. However passive device can be connected to another passive device and after that to an active device - could be endless times a passive device is connected to passive device and then in the end to an active device.

I will include excel tables with data and wanted result.

The criteria is i think something like this:

TYPE IS D3P AND Mark DOES NOT CONTAIN "ZUUM" AND Sort IS "Passive"



I got the result via doing excel function index-match for like 5 times and excel stalling.
IN the previous thread i got to know that it probably is doable via VBA.

Any ideas? or questions?

Thank you,

A.

[ranman256]

Here's some code...you may need to tweek it to match your table name. (mine is: TBL = "tDevices" )
in vbe run : FineActiveDevice
it will scan the table plotting the path to the active device.

Private Const kTBL = "tDevices"


Public Sub FineActiveDevice()
Dim rst
Dim vID, vNext, vPrev, vName
Dim vStatus
Dim sSql As String


sSql = "select * from " & kTBL & " ORDER BY [ID]"


Set rst = CurrentDb.OpenRecordset(kTBL)
With rst
   While Not .EOF
       vID = .Fields("ID").Value & ""
       vNext = .Fields("Device_ID").Value & ""
       vName = .Fields("Device").Value & ""
       vStatus = Left(.Fields("Sort").Value & "", 1)
       
       If vStatus <> "A" Then
          FindStatus vNext
       Else
          PostTrail vID, vName, vStatus, "--end"
       End If
       
       .MoveNext
   Wend
End With
Set rst = Nothing
End Sub


Public Sub FindStatus(ByVal pvID)
Dim rst
Dim vID, vNext, vPrev
Dim vStatus
Dim sSql As String


On Error GoTo endIt
sSql = "select * from " & kTBL & " where [ID]='" & pvID & "'"
Set rst = CurrentDb.OpenRecordset(sSql)
With rst
    vStatus = Left(.Fields("Sort").Value & "", 1)
    vName = .Fields("Device").Value & ""
    vNext = .Fields("Device_ID").Value & ""
    
    PostTrail pvID, vName, vStatus, vNext
    If vStatus = "A" Then
       PostTrail "", vName, vStatus, "--end"
    Else
       FindStatus vNext
    End If
End With
endIt:
Set rst = Nothing
End Sub


Public Sub PostTrail(pvID, pvName, pvStatus, pvNext)
'write to table
sSql = "insert into table ([ID],["
'or
'write to debug window
Debug.Print pvID, pvName, pvStatus, pvNext
End Sub

[annux3]

OK, it does something.

As I understand then this code does not consider the criteria

SELECT * FROM tablename WHERE [TYPE] = "D3P" AND NOT [Mark] LIKE "*ZUUM*" AND [Sort] = "Passive"

Can the output also include source device and found device?