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:
Here is another function in the database which work JUST FINE using the same type of recordset:
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