Recordset wont open, no useful error given

[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
        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
        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

 

[PHV]

same error about using left()
This is often a References issue.
Anyway you don't need the Left() function in your posted code as you can simply use the LIKE operator.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NXMold]

I was suspecting references, but was unsure what specifically the problem would be (what specific reference I needed).

Anyway, the LIKE variation works great (for one option)! The other option uses len(fld)=5 and I don't think there is a way to use LIKE for that.

Being a text field using NOT LIKE 'G*' returns the highest value of 9999 instead of 12839, which is why I'm filtering for 5-character values.

Anyway, I really need to get this operational ASAP... what references do I need?

 

[PHV]

Try to compile the MDB on the target machine.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NXMold]

The target machine(s) do not have ms-access. They only have the ms-access runtime.

 

[PHV]

So, be sure that all YOUR references are available on the target machines.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NXMold]

Ok, looks like I'm screwed.

Other queries with left() dmax() mid() etc continue work fine. Only NEW queries with functions/expressions fail. There seems to be corruption afoot. A /decompile and compact/repair has no effect.

I have no idea what references I need or HOW to check for them on a runtime-only computer. These things have been working, it BROKE friday when I was making a load of revisions.