[AccessD] return query result into variable <SOLVED>

Hahaha. 
He Bruce, I just dropped out of hyperwarp. You're
correct I used a function. I know it wasn't as
sophisticated as youre's but it did the trick.

Thank you very much! 

Regards,

Sander

PS: now i got a new problem. I want to know HOW I did
it...
--- "Brock, Christian T, PERSCOM"
<Christian.Brock at hoffman.army.mil> wrote:
> 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


__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com