Electrical Hierarchy

[June7]

What do you mean by organized list? What do you want it to look like?

Why does FMB607_ have underscore? Is that a valid ID?

[IrogSinta]

So apparently you allow zero length strings in your field Upstream. You can revise your code this way:

    Do
        sStream = sStream & " - " & sBarCode
        rs.FindFirst ("[Device ID]='" & sBarCode & "'")
        sBarCode = Nz(rs!Upstream)
    Loop Until sBarCode = "" 

[HelpDesk]

What do you mean by organized list? What do you want it to look like?

Why does FMB607_ have underscore? Is that a valid ID?

So the way the code is setup right now GetMachineStream will output a string "asdasd - asdasdas - asdasd - asdasd...." Well this isn't what I'm looking for.
I want it to pull those records as it finds them and generate a query table with the information of those upstream machines. Such as

Device ID|| Description|| Location

In the order that the loop pulls them and display that table to the user. The idea here is that the user searches for a machine bar-code and gets all the information of the machines upstream so they know what they are and where they are. Is that a little more clear?

Also yes FMB607_ is a valid ID because the machine has two outgoing connections but only one bar-code but again this is just a test database just so I can understand how this would work.

[HelpDesk]

So apparently you allow zero length strings in your field Upstream. You can revise your code this way:

    Do
        sStream = sStream & " - " & sBarCode
        rs.FindFirst ("[Device ID]='" & sBarCode & "'")
        sBarCode = Nz(rs!Upstream)
    Loop Until sBarCode = "" 

The zero length string in the upstream field shows that that machine is at the top yes.

[IrogSinta]

The zero length string in the upstream field shows that that machine is at the top yes.

What I meant is that you use zero length string and not just NULL strings to indicate that the machine is at the top. While those 2 may look alike, a zero length string is not the same as a NULL string which is what the original Do Loop was watching out for.

Ron

[HelpDesk]

What I meant is that you use zero length string and not just NULL strings to indicate that the machine is at the top. While those 2 may look alike, a zero length string is not the same as a NULL string which is what the original Do Loop was watching out for.

Ron

Oh gotcha...

[HelpDesk]

What do you mean by organized list? What do you want it to look like?

Why does FMB607_ have underscore? Is that a valid ID?

I'm trying to do something like this, obviously this isn't working code but hopefully it will give you a better idea.

Public Function GetMachineStream2(sBarCode As String)
    Dim rs As DAO.Recordset
    Dim sStream As String
    
    Set rs = CurrentDb.OpenRecordset("Select * From Devices")
    
    Do
        rs.AddNew
        rs.Fields("Device ID") = sBarCode
        rs.Fields("Description") = rs!Description
        rs.Fields("Location") = rs!Location
        rs.FindFirst ("[Device ID]='" & sBarCode & "'")
        sBarCode = Nz(rs!Upstream)
    Loop Until sBarCode = ""
    rs.Close
    Set rs = Nothing
    
End Function

[IrogSinta]

So did you really just want to see the stream of a single machine entered by the user? Or where you looking to display all the streams of all the machines in a report? If the former, does it matter how it is displayed? Would you be running any queries against the results? If so, then you would need to update a table with the results of a single machine stream. If not, you could just update a textbox on your form with the results of the stream along with tabs and carriage returns to make it more organized. Either way, this could all be done right within the loop.

Ron

[HelpDesk]

So did you really just want to see the stream of a single machine entered by the user? Or where you looking to display all the streams of all the machines in a report? If the former, does it matter how it is displayed? Would you be running any queries against the results? If so, then you would need to update a table with the results of a single machine stream. If not, you could just update a textbox on your form with the results of the stream along with tabs and carriage returns to make it more organized. Either way, this could all be done right within the loop.

Ron

I am looking for something like this to be displayed to the user after they search for a single machine:



A small table with the machine bar-codes, description and location from the stream. That string that was being output by GetMachineStream showed ME that the loop was properly pulling the upstream machines but I want the user to see something like the table above.

[IrogSinta]

Looks like we crossed posts. I'm assuming then that you want to update a temp table based on the stream result of a single machine. Your code should be similar to this:

Public Function GetMachineStream(sBarCode As String)
    Dim rs As DAO.Recordset
    Dim rs2 as DAO.Recordset
    Dim sStream As String
    
    DoCmd.RunSql "Delete * From DevicesTemp"
    Set rs = CurrentDb.OpenRecordset("Select * From Devices")
    Set rs2= CurrentDb.OpenRecordset("Select * From DevicesTemp")

    Do
        rs.FindFirst ("[Device ID]='" & sBarCode & "'")

        rs2.AddNew
            rs2!Device ID = sBarCode
            rs2!Description = rs!Description
            rs2!Location = rs!Location
        rs2.Update

        sBarCode = Nz(rs!Upstream)
    Loop Until sBarCode=""
    rs.close
    rs2.close
    Set rs = Nothing
    Set rs2=Nothing

    GetMachineStream = Mid(sStream, 4)
    
End Function

[HelpDesk]

Looks like we crossed posts. I'm assuming then that you want to update a temp table based on the stream result of a single machine. Your code should be similar to this:

Public Function GetMachineStream(sBarCode As String)
    Dim rs As DAO.Recordset
    Dim rs2 as DAO.Recordset
    Dim sStream As String
    
    DoCmd.RunSql "Delete * From DevicesTemp"
    Set rs = CurrentDb.OpenRecordset("Select * From Devices")
    Set rs2= CurrentDb.OpenRecordset("Select * From DevicesTemp")

    Do
        rs.FindFirst ("[Device ID]='" & sBarCode & "'")

        rs2.AddNew
            rs2!Device ID = sBarCode
            rs2!Description = rs!Description
            rs2!Location = rs!Location
        rs2.Update

        sBarCode = Nz(rs!Upstream)
    Loop Until sBarCode=""
    rs.close
    rs2.close
    Set rs = Nothing
    Set rs2=Nothing

    GetMachineStream = Mid(sStream, 4)
    
End Function

Yes! haha this is exactly what I'm looking for! My only issue now is when I run it in the immediate window I get an error with the line

rs2!Device ID = sBarCode

Because I apparently can't have a space there so I changed it to DeviceID in the code and table.

After that I now get an error:

Run-time error '3421':

Data type conversion error.

EDIT: NEVERMIND! Had to change the value type in the table!

[HelpDesk]

I got it working and inputting values into the DeviceTemp table!!!!! Thank you guys very much I will probably be back tomorrow with some more help but the big leg work you guys did just now helped me a lot!!

[IrogSinta]

Yes with spaces, it should have been rs2![Device ID] = sBarCode

[June7]

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 & ")"

[IrogSinta]

Excellent idea, June7.