Electrical Hierarchy

[HelpDesk]

Another alternative to temp table for display of single stream might be code builds a string of the device ID values and applies to filter of form or report with IN operator. Something like:

strWHERE = "'FMB892 MTB','FMB602'"

DoCmd.OpenForm "form/report name", , , "DeviceID IN (" & strWHERE & ")"

Well currently I have it setup to where the DevicesTemp table is displayed in a list box in a form. And the DevicesTemp table is cleared and new results added each time the GetMachineStream function is run and that seems to be working out the way I need it to.



My next issue is using a clone of the current function I need to be able to trace the downstream as well. The problem with doing this however is:

1. One machine may have multiple downstream devices, currently the function will only pull the first downstream machine and follow that line. I'm trying to figure out a way to recognize that the device is listed multiple times in the upstream column and follow each line down to their ends either maybe using some kind of if loop.

2. Once it reaches the bottom there is no definitive end when following the machines downstream, with the upstream we eventually reach a null/zero length string that allowed us to exit the loop, but following it downstream the only indication that the stream has ended is the device ID does not show up in the upstream column. While running the code once it reaches the bottom it just jumps up to the first record in the database and stays looped there.

The code I changed around to try and accomplish this is below, it doesn't run in its current state because rs.GetRows returns a data type conversion error, was trying to use an array to accomplish my goal but am having no luck.

Public Function GetMachineStream2(sBarCode As String)
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim sSteam As String
    Dim sBarCode2 As Variant
    
    Set rs = CurrentDb.OpenRecordset("Select * From Devices")
    Set rs2 = CurrentDb.OpenRecordset("Select * From DevicesTemp")
    
    Do
        sStream = sStream & " - " & sBarCode
        rs.GetRows ("[Upstream]='" & sBarCode2 & "'")
        
        rs2.AddNew
            rs2!DeviceID = sBarCode
            rs2!Description = rs!Description
            rs2!Location = rs!Location
        rs2.Update
        
        sBarCode = Nz(rs![Device ID])
    Loop Until sBarCode = ""
    rs.Close
    rs2.Close
    Set rs = Nothing
    Set rs2 = Nothing
    
    GetMachineStream2 = Mid(sStream, 4)
    
End Function

[IrogSinta]

Since it can have multiple paths, you need to create a function that is recursive. In other words, it calls itself whenever it finds a fork in the path and exits that instance of the function when it reaches the end of the downstream. Once it exits, it's back at the fork and should now try the other path. If it meets another fork within that fork, it will call itself again to repeat the process.

Ron

[HelpDesk]

Since it can have multiple paths, you need to create a function that is recursive. In other words, it calls itself whenever it finds a fork in the path and exits that instance of the function when it reaches the end of the downstream. Once it exits, it's back at the fork and should now try the other path. If it meets another fork within that fork, it will call itself again to repeat the process.

Ron

Exactly, but how can I identify forks in the path? What can used as a check to initiate the function again?

[HelpDesk]

Since it can have multiple paths, you need to create a function that is recursive. In other words, it calls itself whenever it finds a fork in the path and exits that instance of the function when it reaches the end of the downstream. Once it exits, it's back at the fork and should now try the other path. If it meets another fork within that fork, it will call itself again to repeat the process.

Ron

Something like this maybe?

Public Function GetMachineStream2(sBarCode As String)
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim sSteam As String
    Dim sCounter As Integer
    
    Set rs = CurrentDb.OpenRecordset("Select * From Devices")
    Set rs2 = CurrentDb.OpenRecordset("Select * From DevicesTemp")
    
   
    Do
        sStream = sStream & " - " & sBarCode
        rs.FindFirst ("[Upstream]='" & sBarCode & "'")
        
        'Find number of upstreams that match search criteria
        sCounter = Sum(IIf(Upstream = sBarCode))
        
        'Run new loop calling function
        Do
            Call GetMachineStream2(sBarCode)
            sCounter = sCounter - 1
            
        'Continue until all are accounted for
        Loop Until sCounter = 0
        
        rs2.AddNew
            rs2!DeviceID = sBarCode
            rs2!Description = rs!Description
            rs2!Location = rs!Location
        rs2.Update
        
        sBarCode = Nz(rs![Device ID])
    Loop Until sBarCode = ""
    rs.Close
    rs2.Close
    Set rs = Nothing
    Set rs2 = Nothing
    
    GetMachineStream2 = Mid(sStream, 4)
    
End Function

[IrogSinta]

It should be something like this:

Public Function GetMachineStream2(sBarCode As String)
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("Select * From Devices Where DeviceID = '" & sBarCode & "'")
    Set rs2 = CurrentDb.OpenRecordset("Select * From DevicesTemp")
    
    Do While Not rs.EOF
                
        rs2.AddNew
            rs2!DeviceID = rs!DeviceID
            rs2!Description = rs!Description
            rs2!Location = rs!Location
        rs2.Update
        
        sBarCode = nz(rs!Upstream)
        If sBarCode<>"" Then Call GetMachineStream2(sBarCode)

    Loop 
    rs.Close
    rs2.Close
    Set rs = Nothing
    Set rs2 = Nothing
    
End Function

Ron

[HelpDesk]

It should be something like this:

Public Function GetMachineStream2(sBarCode As String)
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("Select * From Devices Where DeviceID = '" & sBarCode & "'")
    Set rs2 = CurrentDb.OpenRecordset("Select * From DevicesTemp")
    
    Do While Not rs.EOF
                
        rs2.AddNew
            rs2!DeviceID = rs!DeviceID
            rs2!Description = rs!Description
            rs2!Location = rs!Location
        rs2.Update
        
        sBarCode = nz(rs!Upstream)
        If sBarCode<>"" Then Call GetMachineStream2(sBarCode)

    Loop 
    rs.Close
    rs2.Close
    Set rs = Nothing
    Set rs2 = Nothing
    
End Function

Ron

So I'm not sure what "Do While Not rs.EOF" is actually doing? Does that go through and search the database?

Why did you declare sBarCode at the top and not have it in the loop? The value isn't changing

And does "If sBarCode <> "" Then Call GetMachineStream2(sBarCode)" Check to see if sBarCode is blank?

[IrogSinta]

The Do Loop goes through the recordset of devices that match the barcode. So if there are 3 matching devices, that is a fork with 3 paths. So it goes through each path and calls itself again if there is an upstream.

I have sBarcode in the criteria for the rs recordset.

Did you test this and are you coming out with an error?

Ron

[HelpDesk]

The Do Loop goes through the recordset of devices that match the barcode. So if there are 3 matching devices, that is a fork with 3 paths. So it goes through each path and calls itself again if there is an upstream.

I have sBarcode in the criteria for the rs recordset.

Did you test this and are you coming out with an error?

Ron

I tested it and it is looping infinitely on the same record that I searched for. It just writes that same record over and over.

[IrogSinta]

Well I did not really try out what I gave you. I just typed it in, hoping it would be good enough to get you there. I just tried it out now and I see three errors I made. Here's the corrected version:

    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("Select * From Devices Where [Upstream] = '" & sBarCode & "'")
    Set rs2 = CurrentDb.OpenRecordset("Select * From DevicesTemp")
    
    Do While Not rs.EOF
                
        rs2.AddNew
            rs2!DeviceID = rs!DeviceID
            rs2!Description = rs!Description
            rs2!Location = rs!Location
        rs2.Update
        
        sBarCode = Nz(rs!DeviceID)
        If sBarCode <> "" Then Call GetMachineStream2(sBarCode)
        rs.MoveNext
    Loop
    rs.Close
    rs2.Close
    Set rs = Nothing
    Set rs2 = Nothing

You need to empty your temp table before calling this function:

CurrentDb.Execute "Delete * From DevicesTemp", dbFailOnError
Call GetMachineStream2("YourBarcode")

Ron

[HelpDesk]

Well I did not really try out what I gave you. I just typed it in, hoping it would be good enough to get you there. I just tried it out now and I see three errors I made. Here's the corrected version:

    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("Select * From Devices Where [Upstream] = '" & sBarCode & "'")
    Set rs2 = CurrentDb.OpenRecordset("Select * From DevicesTemp")
    
    Do While Not rs.EOF
                
        rs2.AddNew
            rs2!DeviceID = rs!DeviceID
            rs2!Description = rs!Description
            rs2!Location = rs!Location
        rs2.Update
        
        sBarCode = Nz(rs!DeviceID)
        If sBarCode <> "" Then Call GetMachineStream2(sBarCode)
        rs.MoveNext
    Loop
    rs.Close
    rs2.Close
    Set rs = Nothing
    Set rs2 = Nothing

You need to empty your temp table before calling this function:

CurrentDb.Execute "Delete * From DevicesTemp", dbFailOnError
Call GetMachineStream2("YourBarcode")

Ron

It is preforming exactly as I needed it to Thank You!!! I'm gonna go ahead and mark this query solved because this was the thing I was struggling with hopefully I can figure out the rest! Again Thank You very much!

[IrogSinta]

You're welcome. Glad I could help. I'm relatively new to this forum and still discovering how it works. Apparently there's a reputation button that looks like a 6 sided star at the bottom of each post. I assume that this is how members are supposed to indicate whether or not they like an answer and doing so assigns reputation points to the poster. I don't know why it's not made more obvious. It should at least have the caption "Reputation" beside it so that it may get used more often.

Ron