Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 56
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815

    Did you put the function code in a general module? Remove the Private keyword or change it to Public.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  2. #17
    HelpDesk is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    42
    Quote Originally Posted by June7 View Post
    Did you put the function code in a general module? Remove the Private keyword or change it to Public.
    Yeah I created 'Module 1' and placed the function in there. Removing the Private and changing it to public crashes my access when I run the query...

  3. #18
    IrogSinta is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Posts
    103
    Can you post the SQL string of your query?

  4. #19
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Well, at least the query is finding the function. Calling custom functions in queries really complicates debugging, especially if the dataset is large. Test function by calling from the VBA immediate window.

    Maybe have the function explicitly close the recordset (although I doubt that is the issue).

    GetMachineStream = Mid(sStream, 4)
    rs.Close
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #20
    HelpDesk is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    42
    Quote Originally Posted by IrogSinta View Post
    Can you post the SQL string of your query?
    Here is the SQL:

    Code:
    SELECT Devices.[Device ID], Devices.Description, Devices.Location, GetMachineStream([Device ID]) AS Upstream
    FROM Devices;

  6. #21
    HelpDesk is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    42
    Quote Originally Posted by June7 View Post
    Well, at least the query is finding the function. Calling custom functions in queries really complicates debugging, especially if the dataset is large. Test function by calling from the VBA immediate window.

    Maybe have the function explicitly close the recordset (although I doubt that is the issue).

    GetMachineStream = Mid(sStream, 4)
    rs.Close
    Luckily I am working with a very small dataset because I'm testing all this stuff out!

    I tried adding the rs.Close but no dice still crashing.

  7. #22
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you test in Immediate Window?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #23
    HelpDesk is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    42
    Quote Originally Posted by June7 View Post
    Did you test in Immediate Window?
    Not sure what you mean?

    I closed Access, edited the module then re-ran the query.

    I can compile all of the code in the VBA window, if that's what you mean.

  9. #24
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Call the function from the VBA Immediate Window. Like:

    ?GetMachineStream("type some device ID - is that a bar code? - here")


    Review link at bottom of my post for debugging guidelines.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #25
    IrogSinta is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Posts
    103
    I assumed your [Machine Bar-code] was the field name you were using. If it is [Device ID] that has to be revised in the code as well. Here's an updated function.
    Code:
    Public Function GetMachineStream(sBarCode As String)
        Dim rs As DAO.Recordset
        Dim sStream As String
        
        Set rs = CurrentDb.OpenRecordset("Select * From Devices")
        
        Do
            sStream = sStream & " - " & sBarCode
            rs.FindFirst ("[Device ID]='" & sBarCode & "'")
            sBarCode = Nz(rs!Upstream)
        Loop Until IsNull(rs!Upstream)
        rs.close
        Set rs = Nothing
    
        GetMachineStream = Mid(sStream, 4)
        
    End Function
    Ron

  11. #26
    HelpDesk is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    42
    Quote Originally Posted by June7 View Post
    Call the function from the VBA Immediate Window. Like:

    ?GetMachineStream("type some device ID - is that a bar code? - here")


    Review link at bottom of my post for debugging guidelines.
    If I run from the immediate window Access crashes again.
    (And yeah sorry I changed the variable names) I did go through and change that in the GetMachineSteam code to this:

    Code:
    Public Function GetMachineStream(sBarCode As String)
        Dim rs As DAO.Recordset
        Dim sStream As String
        
        Set rs = CurrentDb.OpenRecordset("Select * From Devices")
        
        Do
            sStream = sStream & " - " & sBarCode
            rs.FindFirst ("[Device ID]='" & sBarCode & "'")
            sBarCode = Nz(rs!Upstream)
        Loop Until IsNull(rs!Upstream)
        
        GetMachineStream = Mid(sStream, 4)
        rs.Close
    End Function

  12. #27
    HelpDesk is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    42
    Quote Originally Posted by IrogSinta View Post
    I assumed your [Machine Bar-code] was the field name you were using. If it is [Device ID] that has to be revised in the code as well. Here's an updated function.
    Code:
    Public Function GetMachineStream(sBarCode As String)
        Dim rs As DAO.Recordset
        Dim sStream As String
        
        Set rs = CurrentDb.OpenRecordset("Select * From Devices")
        
        Do
            sStream = sStream & " - " & sBarCode
            rs.FindFirst ("[Device ID]='" & sBarCode & "'")
            sBarCode = Nz(rs!Upstream)
        Loop Until IsNull(rs!Upstream)
        rs.close
        Set rs = Nothing
    
        GetMachineStream = Mid(sStream, 4)
        
    End Function
    Ron
    I did update the code and I also copied yours and ran it and still the same thing, Access freezes and crashes.

  13. #28
    IrogSinta is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Posts
    103
    I'm wondering if you have a Device upstream that doesn't end. It just keeps looping.

  14. #29
    IrogSinta is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Posts
    103
    You can try adding Debug.Print sStream inside the loop and keep your immediate window visible as you run the query. This may give you a clue on if its looping and on what device.

  15. #30
    HelpDesk is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    42
    Thanks for both of your guys is help so far I figured out a few things by playing with it.

    1. I needed to add rs.MoveNext at the end of the loop because it was stuck on the same line looping infinitely as well as an exit once it detects a null because it was just moving to the next line. Code is now:

    Code:
    Public Function GetMachineStream(sBarCode As String)
        Dim rs As DAO.Recordset
        Dim sStream As String
        
        Set rs = CurrentDb.OpenRecordset("Select * From Devices")
        
        Do
            sStream = sStream & " - " & sBarCode
            rs.FindFirst ("[Device ID]='" & sBarCode & "'")
            sBarCode = Nz(rs!Upstream)
            If sBarCode = "" Then Exit Do
            rs.MoveNext
        Loop Until IsNull(rs!Upstream)
        rs.Close
        Set rs = Nothing
    
    
        GetMachineStream = Mid(sStream, 4)
        
    End Function
    2. I did have an upstream value that was not on the device id list and was causing it to loop infinitely.

    3. I can now get it to output the strings as such:
    Click image for larger version. 

Name:	Capture.PNG 
Views:	8 
Size:	37.7 KB 
ID:	21086

    So now I want to know how I can get this output to look better, have create a table to neatly pull all those machines into an organized list? Any ideas?

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Cascading combo box hierarchy help
    By LonnieCAR in forum Access
    Replies: 7
    Last Post: 07-25-2014, 02:38 PM
  2. Creating Hierarchy
    By Just_Some_Guy in forum Access
    Replies: 17
    Last Post: 07-24-2013, 07:06 AM
  3. Using a hierarchy table
    By younggunnaz69 in forum Queries
    Replies: 3
    Last Post: 07-18-2012, 10:11 PM
  4. Hierarchy summing
    By dskysmine in forum Queries
    Replies: 17
    Last Post: 06-19-2012, 04:08 AM
  5. product hierarchy
    By Lata in forum Access
    Replies: 13
    Last Post: 08-03-2011, 02:56 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums