SELECT MAX ID from feature

Question

I have some code that calls a sub to get the largest unique number from a attribute.

THIS WORKS GREAT ON MY PC, but as soon as I send it over to my Trimble Juno it erros out on the msgbox....

How can this work on my PC and not in the Juno????? I am besides myself right now....Why is the error occuring on a simple message box?????

I then created 3 more fields in the dataset. I made them (Long integer, Double, Text) none of them worked...
But they work fine on my PC....

UGGGGGGGG

Any thoughts?

PLEASE HELP!!!!!!!

Option Explicit 
        Dim varLargestValue

Sub X
     ' Some code
     Call FindLargestValue

' Show to largest value from the sub being called...
     Msgbox varLargestValue

' Some Code
End Sub

Sub FindLargestValue

Dim strFileName2
 strFileName2 = varAXFFileName_2

'++ open the selected AXF file
       Dim pDS6
       Set pDS6 = OpenAXF(strFileName2)
       If (pDS6 Is Nothing) Then
                       Console.Print "Open DataSource failed"
                       Exit Sub
       End If

' Set up recordset query
 Dim  pRS6
 Set pRS6 = pDS6.Execute("SELECT MAX(UNIQUEID2) AS MaximumID FROM Outfalls")

'++ step through the records and output the first attribute to the Console window
 pRS6.MoveFirst
        
 Do While Not pRS6.EOF

MaxID = (pRS6("MaximumID").value)
  pRs6.MoveNext 
 Loop

varLargestValue = MaxID
Msgbox varLargestValue

' Close Recordset
  pRS6.Close
  Set pRS6 = Nothing
End Sub

JayKappy · Answer

I was able to find a solution....it works for me.....
I dont know what was going on although I do know that I was relating the tables by the UNIQUEID....I went back and added another field UNIQUEID2 and copied all the values from UNIQUEID to UNIQUEID2....In the form I am simply updating both fields when I enter a new feature.  This is working for me...
Option Explicit
Dim varLargestValue

Sub Something

' snip 
  
     ' Get the largest value 
     Dim largestValue
     largestValue = 0
     Call FindLargestValue(largestValue)

varLargestValue = largestValue

' snip

End Sub

Sub FindLargestValue(varlargestValue)

Dim MaxID

Dim strFileName2
 strFileName2 = varAXFFileName_2

'++ open the selected AXF file
       Dim pDS6
       Set pDS6 = OpenAXF(strFileName2)
       If (pDS6 Is Nothing) Then
                    Console.Print "Open DataSource failed"
                    Exit Sub
       End If

' Set up recordset query
 Dim  pRS6
 Set pRS6 = pDS6.Execute("SELECT MAX(UNIQUEID2) AS MaximumID FROM Outfalls")

' Move first
 pRS6.MoveFirst
 ' Loop recordset and get number of records         
 Do While Not pRS6.EOF 
  MaxID = (pRS6("MaximumID").value)
  pRs6.MoveNext 
 Loop

varLargestValue = MaxID

' Clear Variables     
 MaxID = ""

' Close Recordset
 pRS6.Close
 Set pRS6 = Nothing

End Sub

AndrewDuff · Answer

Its definately in that max SQL statement because the following works just fine.

'this will return the next line ID using and AXF and SQL
Public Function ReturnNextLineID_NoMax(strAXFFilePath, strAnimalIDValue)
 'Built on the notion you are working with one animal per day
    'open the selected AXF file
    Dim pDS
    Set pDS = OpenAXF(strAXFFilePath)
    If (pDS Is Nothing) Then
        Application.MessageBox "Open DataSource failed, Line 457 of Triangulation_Tools.vbs", , "WDFW Telemetry Tools"
        Exit Function
    End If

'Create the SQL Statement
 Dim strSQL
 strSQL = "SELECT LINEID FROM [TELEMETRYLINES] WHERE ANIMALID = '" & strAnimalIDValue & "'"

Dim pRS, MaxValue, NextValue, CurrentValue
 Set pRS = pDS.Execute(strSQL)

'Application.MessageBox "SQL SENT =" & CStr(strSQL)
 'Application.MessageBox "Last Global Value =" & CStr(gblLineID)

MaxValue = 0

If (Not pRS Is Nothing) Then
        pRS.MoveFirst  
  Do While Not pRS.EOF
   If (pRS.Fields("LINEID").IsNull) = True then
    Exit Do
   Else
    CurrentValue = CInt(pRS.Fields("LINEID").value)
    'Application.MessageBox "CurrentValue= " & CStr(CurrentValue)
    'Application.MessageBox "MaxValue= " & CStr(MaxValue)
    If CurrentValue > MaxValue Then
     MaxValue=CurrentValue
    End If
       
   End If
   pRs.MoveNext

Loop

NextValue = MaxValue + 1
  'Application.MessageBox "NextValue At End Of Routine (Will Be Used)= " & CStr(NextValue)  
 Else
  Application.Message "An error Occurred Getting the LineID's", , "WDFW Telemetry Tools"
 End If

pRS.Close
    Set pRS = Nothing
 pDS.Close
    Set pDS = Nothing
 ReturnNextLineID_NoMax = NextValue
End Function

AndrewDuff · Answer

I have the same exact problem.  Here is my version of the code that causes the same thing.  I have tried two different pda's (Trimble Juno ST and GETAC PS535F).  This is driving me crazy!! Is this a bug in the software - SQL Server Compact or something?

Andy

Public Function ReturnNextLineID(strAXFFilePath, strAnimalIDValue)
 'Built on the notion you are working with one animal per day
    'open the selected AXF file
    Dim pDS
    Set pDS = OpenAXF(strAXFFilePath)
    If (pDS Is Nothing) Then
        Application.MessageBox "Open DataSource failed, Line 457 of Triangulation_Tools.vbs", , "WDFW Telemetry Tools"
        Exit Function
    End If

'Create the SQL Statement
 Dim strSQL
 strSQL = "SELECT MAX(LINEID) AS MAXLINEID FROM [TELEMETRYLINES] WHERE ANIMALID = '" & strAnimalIDValue & "'"

Dim pRS, MaxValue, NextValue
 Set pRS = pDS.Execute(strSQL)

Application.MessageBox "SQL SENT Value =" & CStr(strSQL)
 
 Application.MessageBox "Last Global Value =" & CStr(gblLineID)

If (Not pRS Is Nothing) Then
        pRS.MoveFirst  
  Do While Not pRS.EOF
   'If IsNull(pRS.Fields("MAXLINEID").value) = True then
   If (pRS.Fields("MAXLINEID").IsNull) = True then
    '
       NextValue = 1
   
   Else
    'Application.MessageBox pRS.Fields("MAXLINEID").value
    'What is returned here on the device is different than on the desktop, WTF?
    MaxValue = CInt(pRS.Fields("MAXLINEID").value)
    'Application.MessageBox MaxValue
    NextValue = MaxValue + 1 
   End If
   pRs.MoveNext

Loop
  
  If  IsNull(pRS.Fields("MAXLINEID").value) = False Then
   Application.MessageBox "MAXLINEID Value =" & CStr(pRS.Fields("MAXLINEID").value)
  Else
   Application.MessageBox "MAXLINEID Value is evaluated to Null"
  End If
  Application.MessageBox "NextValue =" & NextValue
  
 Else
  Application.Message "An error Occurred Getting the LineID's", , "WDFW Telemetry Tools"
 End If

pRS.Close
    Set pRS = Nothing
 pDS.Close
    Set pDS = Nothing
 ReturnNextLineID = NextValue
End Function