Error 3075 and Left() Function

[Kevin_S]

I am banging my head on this one - need some help badly!

I have some code that I am using to create a unique ID based on two parameters (UNITID and FEATURE_TYPE). I am using a query that parses a field to get the first character and then looks at two variables to see how what the value should be: Here is the code:

On Error GoTo Command145_Click_Error

Dim rst As Recordset
Dim strSQL As String
Dim Numrec As Long

If IsNull(Me.UNITID) Then
Exit Sub
ElseIf IsNull(Me.GPSSURVEY_ID) Then
    If MsgBox("Confirm Create New GPS Survey ID?", vbQuestion + vbYesNo, "Confirm Add GPS ID") = vbYes Then
            Set rst = New ADODB.Recordset
            strSQL = "SELECT Max(dbo_PARKINVT_INVENTORY.GPSSURVEY_NO) AS MaxOfGPSSURVEY_NO FROM dbo_PARKINVT_INVENTORY GROUP BY dbo_PARKINVT_INVENTORY.UNITID, Left([GPSSURVEY_ID],1) HAVING (((dbo_PARKINVT_INVENTORY.UNITID)=" & Me.Combo6 & " And ((Left([GPSSURVEY_ID],1))='" & Me.cboGPSF & "')));"
            rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
            If rst.RecordCount = 1 Then
            Numrec = rst!MaxOfGPSSURVEY_NO
            Me.GPSSURVEY_NO = Numrec + 1
            Me.GPSSURVEY_ID = Me.cboGPSF & "-" & Me.UNITID & "-" & Me.GPSSURVEY_NO
            ElseIf rst.RecordCount = 0 Then
            Numrec = 1
            Me.GPSSURVEY_NO = Numrec
            Me.GPSSURVEY_ID = Me.cboGPSF & "-" & Me.UNITID & "-" & Me.GPSSURVEY_NO
            Else
            MsgBox "A duplicate key has been entered into the field.  Contact Field Coordinator for assistance", vbCritical + vbOKOnly, "DUPLICATE SURVEY ID WARNING!"
            End If
            rst.Close
            Set rst = Nothing
    Else
    End If
End If

   On Error GoTo 0
   Exit Sub

Command145_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Command145_Click of VBA Document Form_frmSAMPLE_INTERFACE"

Now this works perfectly fine on my pc (Win 2000) HOWEVER - when I transfer the .mde to the device (Tablet PC running XP Tablet OS w/ MS Access 2002 Runtime) I get the following error:

Error 3075 (Function is not available in expression in query expression 'Left([GPSSURVEY_ID],1)' in procedure Command145_Click...

Can someone help me figure a way to get this to work? I'm not sure what the problem is exactly as this runs fine on my machine but doesn't work on the tablet?!?!??

Thanks,
Kev

 

[Rich]

Missing Reference ?

 

[Kevin_S]

Missing Reference ?

I'm thinking this is probably the case but how to I check a mde on the runtime edt. of MS Access 2002 for a broken/missing reference?

Thanks,
Kev

 

[WayneRyan]

Kevin,

When dealing with .MDE files, I believe that the libraries referenced must
be the SAME versions, in the SAME locations as the parent .MDB file. I
don't think that there is a way around it. Maybe someone will see this
that knows otherwise. I hope so because that sounds hard to work with.

Wayne