Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recordset wont open, no useful error given

Status
Not open for further replies.

NXMold

Technical User
Jul 22, 2008
104
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:
Code:
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:
Code:
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
 
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
 
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?
 
Try to compile the MDB on the target machine.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
The target machine(s) do not have ms-access. They only have the ms-access runtime.
 
So, be sure that all YOUR references are available on the target machines.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top