Electrical Hierarchy

[HelpDesk]

Hey guys I am new to the forums and am trying to make the switch to access to make it easier for my electricians to know what happens when they work on a machine.



So I have a database that has all the machines currently connected to my electrical grid. Each machine has its own bar-code label in order to keep track of it and that is what I am using for the primary key. This is how I have my first table set up as:

Machine Bar-Code||Description||Location||Upstream

The upstream is the machine that is connected before it. Basically what I am trying to do is have a way for a report to be generated for a user with the connections that lead to the machine so if the machine needs to be taken down we know what else has to be taken down to work on it. I had a crude version of what I want in excel using VB code created by someone (before my time) that generates all the machines connected downstream and all the machines connected upstream of the bar-code you search for. Here is the vb code:

Sub findChildren()
    Dim searchString As String 'string we're looking for
    Dim foundString As String 'string we're comparing to the searchString
    Dim searchRow As Integer 'row we're checking for a parent match
    Dim writeRow As Integer 'row we just wrote the most recent parent to
    Dim readRow As Integer 'row containing the "parent" we're looking for
    Dim clearRow As Integer 'row to clear
    
    searchRow = 2
    writeRow = 3
    
    'clear the result rows
    clearRow = 3
    While (Sheets("Form").Range("C" & clearRow) <> "")
        Sheets("Form").Range("C" & clearRow) = ""
        Sheets("Form").Range("D" & clearRow) = ""
        Sheets("Form").Range("E" & clearRow) = ""
        clearRow = clearRow + 1
    Wend
    
    
    
    searchString = Sheets("Form").Range("C" & searchRow)
    While (searchString <> "")
        readRow = 3
        foundString = Sheets("RawData").Range("D" & readRow)
        While (foundString <> "")
            If (foundString = searchString) Then
                Sheets("Form").Range("C" & writeRow) = Sheets("RawData").Range("D" & readRow)
                Sheets("Form").Range("D" & writeRow) = Sheets("RawData").Range("B" & readRow)
                Sheets("Form").Range("E" & writeRow) = Sheets("RawData").Range("C" & readRow)
                writeRow = writeRow + 1
            End If
            
            readRow = readRow + 1
            foundString = Sheets("RawData").Range("D" & readRow)
        Wend
    
        searchRow = searchRow + 1
        searchString = Sheets("Form").Range("D" & searchRow)
    Wend
    
    


End Sub
-------------------------------------------------------------------------------------------------
Sub findParents()
    Dim searchString As String 'string we're looking for
    Dim foundString As String 'string we're comparing to searchString
    Dim searchRow As Integer 'row we're checking for a parent match
    Dim writeRow As Integer 'row we just wrote to
    Dim readRow As Integer 'row containing the child we're looking for the parent for
    Dim parentFound As Boolean 'flag indicating we've found the parent
    Dim parent As String
    Dim clearRow As Integer
    
    'clear the result rows
    Sheets("Form").Range("I" & 2) = ""
    Sheets("Form").Range("J" & 2) = ""
        clearRow = 3
    While (Sheets("Form").Range("H" & clearRow) <> "")
        Sheets("Form").Range("H" & clearRow) = ""
        Sheets("Form").Range("I" & clearRow) = ""
        Sheets("Form").Range("J" & clearRow) = ""
        clearRow = clearRow + 1
    Wend


    
    
    searchRow = 2
    writeRow = 3
    searchString = Sheets("Form").Range("H" & searchRow)
        
    While (searchString <> "")
        parentFound = False
        readRow = 3
        foundString = Sheets("RawData").Range("B" & readRow)
        'first, find the load to identify its parent
        While (foundString <> "" And parentFound = False)
            
            If (foundString = searchString) Then
                parent = Sheets("RawData").Range("D" & readRow)
                Sheets("Form").Range("I" & searchRow) = Sheets("RawData").Range("D" & readRow)
                Sheets("Form").Range("J" & searchRow) = Sheets("RawData").Range("C" & readRow)
                Sheets("Form").Range("H" & writeRow) = Sheets("RawData").Range("D" & readRow)
                parentFound = True
                writeRow = writeRow + 1
                
            End If
            readRow = readRow + 1
            foundString = Sheets("RawData").Range("B" & readRow)
        Wend
        searchRow = searchRow + 1
        searchString = Sheets("Form").Range("H" & searchRow)
    
    Wend
End Sub

My question is how can I implement this VB code to have a report be generated with the same results in Access to make it cleaner. Any ideas?? All help is appreciated!

[ranman256]

Normally, you wouldnt use code. Access works via queries.
The query pulls the data you want.

Learn queries.

[IrogSinta]

Perhaps something like this:

Private Function GetMachineStream(sBarCode As String)
    Dim rs As DAO.Recordset
    Dim sStream As String
    
    Set rs = CurrentDb.OpenRecordset("Select * From TableName")
    
    Do
        sStream = sStream & " - " & sBarCode
        rs.FindFirst ("[Machine Bar-code]='" & sBarCode & "'")
        sBarCode = Nz(rs!Upstream)
    Loop Until IsNull(rs!Upstream)
    
    GetMachineStream = Mid(sStream, 4)
    
End Function

Change TableName to the appropriate name for your table. To get the upstream, try this:
Msgbox GetMachineUpstream("EnterBarcodeHere")

Ron

[HelpDesk]

Normally, you wouldnt use code. Access works via queries.
The query pulls the data you want.

Learn queries.

How can I get queries to loop until it finds no string in the upstream column??

[HelpDesk]

Perhaps something like this:

Private Function GetMachineStream(sBarCode As String)
    Dim rs As DAO.Recordset
    Dim sStream As String
    
    Set rs = CurrentDb.OpenRecordset("Select * From TableName")
    
    Do
        sStream = sStream & " - " & sBarCode
        rs.FindFirst ("[Machine Bar-code]='" & sBarCode & "'")
        sBarCode = Nz(rs!Upstream)
    Loop Until IsNull(rs!Upstream)
    
    GetMachineStream = Mid(sStream, 4)
    
End Function

Change TableName to the appropriate name for your table. To get the upstream, try this:
Msgbox GetMachineUpstream("EnterBarcodeHere")

Ron

Have a little patience with me haha I am not sure where to implement this function. Can I just use a button on a form to call this function and bring up the msgbox?

[IrogSinta]

The function is placed inside a module. It could be a form module or standard module visible to the whole project. I'm not sure of your setup, if you want to get the streams via query so that you could get all the results simultaneously for all machines, you can just add the function there as another field. If you intend to get them individually, then you can do this with a query or on a form.

Ron

[June7]

Sounds like a recursive data structure http://www.tomjewett.com/dbdesign/db...=recursive.php. A query would join the table to itself. Apparently SQLServer has special query structure for this not available in Access https://technet.microsoft.com/en-us/...=sql.105).aspx. How many possible levels 'upstream'? In your situation, VBA might be only solution in Access.

[HelpDesk]

The function is placed inside a module. It could be a form module or standard module visible to the whole project. I'm not sure of your setup, if you want to get the streams via query so that you could get all the results simultaneously for all machines, you can just add the function there as another field. If you intend to get them individually, then you can do this with a query or on a form.

Ron

So I added "=[GetMachineStream]" to the criteria field of a query, ran it, it asks me to enter parameter value, I enter a bar-code from the table and it appears with only that machine not all the machines upstream of that one. Am I supposed to add the function somewhere else? Ideally I want the function to output a query with all the machines associated with that bar-code.

Perhaps it is not clear that the values in the upstream column are values that can be found in the Machine Bar-Code column. So each machine should have a value from the Machine Bar-Code column unless it is the top level. I'm basically trying to list out a branch of a one line wiring diagram based on which machine you are looking at.

[HelpDesk]

Sounds like a recursive data structure http://www.tomjewett.com/dbdesign/db...=recursive.php. A query would join the table to itself. Apparently SQLServer has special query structure for this not available in Access https://technet.microsoft.com/en-us/...=sql.105).aspx. How many possible levels 'upstream'? In your situation, VBA might be only solution in Access.

Yes it is a recursive data structure. That's thing thing the levels of upstream can vary based on the machine, it could be 2 levels or 12 all depends on where the machine is located.

[IrogSinta]

You don't add the function as a criteria. You add it as another field in the query. In a empty column in your QBE, add the following:
Upstream: GetMachineStream([Machine Bar-code])

[June7]

Although might better use an alias name that is not the same as name of field in table.

[IrogSinta]

Oops, I forgot that you had a field already called Upstream. Thanks June. You can call it Upstreams or anything else you feel describes it.

[HelpDesk]

You don't add the function as a criteria. You add it as another field in the query. In a empty column in your QBE, add the following:
Upstream: GetMachineStream([Machine Bar-code])

I get an error saying

"Extra ) in query expression 'Devices.[GetMachineStream([Machine Bar-code])]'."

[June7]

What is Devices - table name? Remove it and the [].

[HelpDesk]

What is Devices - table name? Remove it and the [].

Devices is the table name, and it was not added to QBE field. I copied what IrogSinta gave me to put in the QBE field thats all and had to chose devices as a table or else the GetMachineStream would be undefined.