Looking for the equivalent of "Recordcount"

[EMESES]

Hi all

Here Below code I use to build a requery and open a report. What I want to do is to test whether the result of this requery contains records, If yes I'll open the report else I'll show a message (Kind of: "If rs.recordcount=0 Then")

===================================
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strOffice As String
Dim strDepartment As String
Dim strGender As String
Dim strSQL As String

Set db = CurrentDb
If Not QueryExists("My_Requery") Then
Set qdf = db.CreateQueryDef("My_Requery")
Else
Set qdf = db.QueryDefs("My_Requery")
End If

If IsNull(Me.Field1.Value) Then
strField1 = "01/01/1000"
Else
strField1 = "" & Me.Field1.Value & " "
End If

If IsNull(Me.Field2.Value) Then
strField2 = "01/01/9000"
Else
strField2 = "" & Me.Field2.Value & " "
End If

If IsNull(Me.Field3.Value) Then
strField3 = " Like '*' "
Else
strField3 = "='" & Me.Field3.Value & "' "
End If

If IsNull(Me.Field4.Value) Then
strField4 = " Like '*' "
Else
strField4 = "='" & Me.Field4.Value & "' "
End If

If IsNull(Me.Field5.Value) Then
strField5 = " Like '*' "
Else
strField5 = "='" & Me.Field5.Value & "' "
End If


strSQL = "SELECT My_table.* " & _
" FROM T_evaluation " & _
"WHERE T_evaluation.Field1 BETWEEN #" & Format(strField1, "mm/dd/yyyy") & "# AND #" & Format(strField2, "mm/dd/yyyy") & _
"# AND T_evaluation.Field2 " & strField2 & _
"AND T_evaluation.Field3 " & strField3 & _
"AND T_evaluation.Field4 " & strField4 & _
"ORDER BY T_evaluation.Field1 ;"
qdf.SQL = strSQL
MsgBox qdf.ReturnsRecords
'MsgBox strSQL

DoCmd.Echo False

If Application.SysCmd(acSysCmdGetObjectState, acQuery, "My_Requery") = acObjStateOpen Then
DoCmd.Close acQuery, "My_Requery"
End If

DoCmd.OpenReport "My_report", acViewPreview, "", ""
===================================

Many thanks in advance.

Moony

-------------------------------
Great Starts Make Great Finishes
-------------------------------

 

[Golom]

You would need to actually open the recordset to tell. qdf.SQL = strSQL just saves the SQL you've built but doesn't execute it and qdf.ReturnsRecords just tells you if it is a type of query that can return records ... not if it actually does.

Try something like

Dim rs As DAO.Recordset, ItReturnsRecords As Boolean
Set rs = Currentdb.OpenRecordset(strSQL)
ItReturnsRecords = NOT (rs.EOF AND rs.BOF)
rs.Close

If ItReturnsRecords Then  DoCmd.OpenReport "My_report", acViewPreview, "", ""

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[EMESES]

Many thanks for your prompt reply.
That's exactly what I'm looking for.
Many thanks again.

Moony

-------------------------------
Great Starts Make Great Finishes
-------------------------------