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.
Code: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