Did you put the function code in a general module? Remove the Private keyword or change it to Public.
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.
Can you post the SQL string of your query?
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.
Luckily I am working with a very small dataset because I'm testing all this stuff out!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
I tried adding the rs.Close but no dice still crashing.
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.
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.
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.
RonCode: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
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
I did update the code and I also copied yours and ran it and still the same thing, Access freezes and crashes.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.
RonCode: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
I'm wondering if you have a Device upstream that doesn't end. It just keeps looping.
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.
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:
2. I did have an upstream value that was not on the device id list and was causing it to loop infinitely.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
3. I can now get it to output the strings as such:
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?