Storing the value of a query in a variable

[Davide77]

Hallo,
I need to use the number of records there is in a query. I made a count query so I know the number of record. The problem now is how to store this number in a variable.

I'm using this code:

Dim RecordQuantitySQL As String

RecordQuantitySQL = "SELECT Count(qry_Room.Room) FROM [qry_Room];"

DoCmd.RunSQL RecordQuantitySQL

But I'm facing two problems: first of all my sql sentence seems to not be an sql sentence since the debugger tells me that the RunSQl Action needs a sql sentence.
The second problem is: after I run the query how can I store its value in a variable?

thanks

 

[selavarti]

Use the Dcount function to count the records. Declare a Dim count as integer. Then write count=Dcount..... I have the code which I have written may be it will help u.

Private Sub Text2_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String
If DCount("[tool_barcode]", "qry_empcheckedout", "[tool_barcode]=[forms]![frm_checkout]![text2]") <> 0 Then
MsgBox "Cannot check out because this item has not beem checked in."
End If

End Sub

 

[Davide77]

thanks selavarti