[AccessD] Trouble with SQL statement on two difefrent machines

Could it be an MDAC issue?  I notice you don't specifically reference
whether you are using DAO or ADO when you declare your databases and
recordsets.  Your computer may have the latest version but theirs may not.

Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
connie.kamrowski at agric.nsw.gov.au
Sent: Saturday, March 26, 2005 10:27 PM
To: accessd at databaseadvisors.com
Subject: [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.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com