Recordset won't open, no specific error

[NXMold]

I am trying to find the highest value in a table and increment it, putting the new value in a text box on a form.

Everything I'm doing works fine on my workstation with full access-2000 installed. It fails on workstations with the access runtime "The expression After Update ... function is not available in expressions in query expression".

I put a buch of message boxes in there so that I could find the line it failed on (since it only fails using the compiled mde on runtime-only machines). The bad line is rs.open.... but I can't see why. I use the same ADO recordsets in other places, so that can't be the problem, which leaves the SQL syntax... which looks ok to me? I created an alternate option using DMax, which ALSO fails with the same error about using left(). The dmax code is cut and paste from a previous implementation on a bound form that worked just fine before.

Am I going nuts here? Whats wrong with my code?

Here is my function:

Function fNextJobNo(Optional G_Job As Boolean = False, Optional use_domain_aggregate As Boolean = False) As String
        
If use_domain_aggregate = True Then GoTo use_domain_aggregate

    Dim rs As New ADODB.Recordset
    Dim strSQL As String
        
    If G_Job = True Then
        'Work with Gxxxx jobs
        strSQL = "SELECT TOP 1 CMEJobList.CMEJobNumber FROM CMEJobList WHERE left(CMEJobNumber,1)='G' ORDER BY CMEJobNumber DESC;"
        'rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly, adCmdText
        rs.Open strSQL, CurrentProject.Connection
        rs.MoveFirst
        fNextJobNo = "G" & Right(rs.Fields(0), 4) + 1
    Else
        'Work with 12xxx jobs
        strSQL = "SELECT TOP 1 CMEJobList.CMEJobNumber FROM CMEJobList WHERE left([cmejobnumber],1)<>'G' AND len([cmejobnumber])=5 ORDER BY CMEJobNumber DESC;"
        rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly, adCmdText
        rs.MoveFirst
        fNextJobNo = rs.Fields(0) + 1
    End If
        
    rs.Close
    Set rs = Nothing
    Exit Function

use_domain_aggregate:
    
    If G_Job = True Then
        fNextJobNo = "G" & DMax("right([cmejobnumber],4)", "cmejoblist", "left([cmejobnumber],1)='G'") + 1
    Else
        fNextJobNo = DMax("[cmejobnumber]", "cmejoblist", "left([cmejobnumber],1)<>'G' AND len([cmejobnumber])=5") + 1
    End If

End Function

Here is another function in the database which work JUST FINE using the same type of recordset:

Dim rs As New ADODB.Recordset
Dim strSQL As String
        
    strSQL = "SELECT TOP 1 tblSessions.SessionID FROM tblSessions ORDER BY SessionID DESC;"
    rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

 

[NXMold]

I realize I've posted this in the wrong place, I'm reposting in the VBA forum.