[AccessD] return query result into variable

Do you have to have some particular reference turned on to use the State
property for recordsets?  I am using Access XP and do not see that as a
property.

Christian Brock

-----Original Message-----
From: Bruce Bruen [mailto:bbruen at bigpond.com] 
Sent: Tuesday, 08 July 2003 05:51
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] return query result into variable


Hoowee, you're about 2 light years away.

Your query does not return one number, it returns a recordset with a single
row containing a single field that is a number.

Docmd.runquery?  Perhaps docmd.openquery, but I doubt that's what you want
either.

I think you need a module containing something along the lines of the
following.

<CODE>

#Const DEVEL = False        'turns development level error handling on
and off

Public Function GetCount(qryName As String) As Long
'-----------------------------------------------------------------------
----------------
'
'   Description / Purpose :-
'       GetCount executes a stored database query that returns a count
of a specific type
'       The query MUST return only one row, and only one field
"qryCount".
'
'   Parameters:-
'       PARAMETER   TYPE    COMMENTS
'       qryName     string  The name of the select query to be run
'
'   Return Value:-
'       (Long) the value of the count as returned by the query.  If the
action fails the
'       returned value is set to -1.
'
'   Notes:-
'
'-----------------------------------------------------------------------
----------------
'   Version     Dated       Author  Comment
'     1.0       28/06/2003  bruce   Original.
'=======================================================================
================

    Dim lngRtn As Long          'internal store for interim result
    Dim conn As Connection      'db connection
    Dim rst As Recordset        'recordset returned by the query

    On Error GoTo GetCount_ERR
    
    lngRtn = -1
    Set conn = CurrentProject.Connection
    Set rst = New Recordset
    rst.Open qryName, conn, adOpenStatic, adLockReadOnly
    
    If rst.RecordCount <> 1 Then
        Err.Raise 4011, "GetCount" ', "GetCount Query error"
    Else
        lngRtn = rst!qryCount
    End If
    
    GetCount = lngRtn

GetCount_CLEANUP:
    If Not rst Is Nothing Then
        If rst.State = adStateOpen Then
            rst.Close
        End If
        Set rst = Nothing
    End If

GetCount_EXIT:
   On Error GoTo 0
   Exit Function

GetCount_ERR:
    Select Case Err.Number
        Case 3265
            Call MsgBox("Program Error:The query passed to the GetCount
procedure " _
                        & vbCrLf & "does not contain the correct field for
returning the count." _
                        & vbCrLf & "This error is a serious program fault."
_
                        & vbCrLf & "" _
                        & vbCrLf & "Please inform the System Administrator
of this error." _
                        , vbCritical + vbSystemModal, Application.Name)
        Case 4011
            Call MsgBox("Program Error: The query supplied to this procedure
returned no rows or " _
                        & vbCrLf & "more than one row.   The procedure
can only use a query that returns" _
                        & vbCrLf & "exactly one row.  This is a serious
program error." _
                        & vbCrLf & "" _
                        & vbCrLf & "Please inform the System Administrator
of this error." _
                        , vbCritical + vbSystemModal, Application.Name)
        Case -2147217900
            Call MsgBox("Program Error:  An invalid query name was passed as
a " _
                        & vbCrLf & "parameter to the GetCount function. This
is a serious " _
                        & vbCrLf & "programming error." _
                        & vbCrLf & "" _
                        & vbCrLf & "Please inform the System Administrator
of this error" _
                        , vbCritical + vbSystemModal, Application.Name)
            Set rst.ActiveConnection = Nothing 'needed as the open call
failed
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure GetCount of Module Module1"
    End Select
#If Not DEVEL Then
    Resume GetCount_CLEANUP
#Else
    Stop
    Resume
#End If

End Function
  
</CODE>

Then you can call the beast as a function, like:

 intNewProjectID=GetCount("qryNewProjectID")


Hth
Bruce
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Sad Der
Sent: Tuesday, 8 July 2003 6:48 PM
To: Acces User Group
Subject: [AccessD] return query result into variable