Jim DeMarco
Jdemarco at hshhp.org
Tue Jul 8 11:53:59 CDT 2003
Christian,
It's an ADO recordset and there is a State property that evaluates to:
Constant Description
adStateClosed Default. Indicates that the object is closed.
adStateOpen Indicates that the object is open.
adStateConnecting Indicates that the Recordset object is connecting.
adStateExecuting Indicates that the Recordset object is executing a command.
adStateFetching Indicates that the rows of the Recordset object are being fetched.
HTH,
Jim DeMarco
Director of Product Development
HealthSource/Hudson Health Plan
-----Original Message-----
From: Brock, Christian T, PERSCOM
[mailto:Christian.Brock at hoffman.army.mil]
Sent: Tuesday, July 08, 2003 11:10 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [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
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
***********************************************************************************
"This electronic message is intended to be for the use only of the named recipient, and may contain information from Hudson Health Plan (HHP) that is confidential or privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this message is strictly prohibited. If you have received this message in error or are not the named recipient, please notify us immediately, either by contacting the sender at the electronic mail address noted above or calling HHP at (914) 631-1611. If you are not the intended recipient, please do not forward this email to anyone, and delete and destroy all copies of this message. Thank You".
***********************************************************************************