[AccessD] Trouble with SQL statement on two difefrent machines

Hi All,

I am havinbg an issue with a function and could use some fresh eyes and
knowledge. Both machines are running Access97 SR2. On my machine the
function works fine, on the users machine it falls over. We are accessing
the database from a central location.

Function as follows:

<code>
Private Function getstrNextAuthorityNumber() As String
'Automatically select the next availaible Authority Number for this type of
Authority
On Error GoTo Failure

Dim dbNextAuthority As Database
Dim rsNextAuthority As Recordset

Dim intNextAuthorityNumber As Integer

Dim strSelect As String


strSelect = "SELECT tblCAuthorityType.AuthorityPrefix,
Max(Right([AuthorityNumber],5)) AS [Number] " & _
                        "FROM tblCAuthorityType RIGHT JOIN tblDAuthority ON
tblCAuthorityType.AuthorityTypeID = tblDAuthority.AuthorityType " & _
                        "GROUP BY tblCAuthorityType.AuthorityPrefix " & _
                        "HAVING
(((tblCAuthorityType.AuthorityPrefix)=getgstrAuthorityPrefix()));"

On Error Resume Next

    Set dbNextAuthority = CurrentDb()
    Set rsNextAuthority = dbNextAuthority.OpenRecordset(strSelect,
dbOpenSnapshot, dbFailOnError)

        If rsNextAuthority.RecordCount < 1 Then 'No records - this will not
actually be used.
            intNextAuthorityNumber = 1 'Initial Authority Number for each
type
        Else
            intNextAuthorityNumber = rsNextAuthority!Number + 1 'Increment
last Authority Number by 1
        End If 'rsNextAuthorityNumber.RecordCount = 0

        getstrNextAuthorityNumber = getgstrAuthorityPrefix() & Right("0000"
& Trim(Str(intNextAuthorityNumber)), 5)

ExitRoutine:
        On Error Resume Next
            rsNextAuthority.Close
            Set rsNextAuthority = Nothing
            dbNextAuthority.Close
            Set dbNextAuthority = Nothing
        Exit Function

Failure:
        Call ErrorHandler(lngErrorNumber:=Err.Number,
strErrorDescription:=Err.Description, strErrorSource:=Err.Source)
        Resume ExitRoutine

End Function
<code>

This fails at the bold line, on my machine it takes the SQL statement in
strSelect and goes to the getgstrAuthorityPrefix() function as defined on
the end, returning a value. On the users machine it goes straight to the
next line and misses the call to getgstrAuthorityPrefix() altogether
returning a Bliock Obejct variable not set.

This is code written by my predecessor. All ideas appreciated.


Connie Kamrowski

Analyst/Programmer
Information Technology
NSW Department of Primary Industries
Orange



This message is intended for the addressee named and may contain
confidential information. If you are not the intended recipient or received
it in error, please delete the message and notify sender. Views expressed
are those of the individual sender and are not necessarily the views of
their organisation.