RecordCount of current RecordSource (1 Viewer)

[coasterman]

I have a form which filters records by a number of different query. The query is selected from a combo box on the main form and this works fine.

What I would like to do is prevent the recordsource from being changed to a query where there are no matched records.

I've being playing with some code but am not getting to far.

Private Sub cboQueryPicker_AfterUpdate()

    Dim myquery As String

    On Error GoTo Errorhandler
    myquery = Me.cboQueryPicker.Column(1)
    
    

    Me.RecordSource = myquery
    If Not RecordCount > 0 Then
    MsgBox "No records to display"
    End If
   
Exit_Point:
    Exit Sub

    'If user cancels the paramater search then exit the sub at the Exit_Point: above
Errorhandler:
    Select Case Err.Number
    Case 2001, 2501    ' "Cancel" error codes
        Resume Exit_Point
    Case Else
        MsgBox Err.Description, vbExclamation, "Error " & Err.Number
        Resume Exit_Point
    End Select

End Sub

I suspect the method isn't supported. I'm not at all familiar with DAO but would that be the way to do it?

 

[Pat Hartman]

I use the following code in my search forms to warn users when their criteria resulted in no records being returned. The example below is a button on a main form which is why the ".Form" qualifier is used since the RecordSource being manipulated is in a subform. You were close, you just need to use the RecordsetClone.

Private Sub cmdSearch_Click()
Dim rs as DAO.Recordset
    If (Me.txtInvNo & "" = "" And Me.cboBlock & "" = "") And (Me.txtFromDate & "" = "" _
            And Me.txtThruDate & "" = "") Then
        MsgBox "Please enter some search criteria.", vbOKOnly
        Me.txtFromDate.SetFocus
        Exit Sub
    End If
    Me.sfrmBillList.Form.RecordSource = "qBillSearchAll"
    Set rs = Me.sfrmBillList.Form.RecordsetClone
    If rs.RecordCount > 0 Then
    Else
        MsgBox "No records found for search criteria.", vbOKOnly
    End If
End Sub

 

[pbaldy]

You could also test with DCount():

If DCount("*", myquery) > 0 Then

and only set the source if it returns records.

 

[coasterman]

Thank you both. This will be a good entry for me into DAO I think and I have now got the code working with your suggestion Pat.

I had also completely overlooked the DCount function, I'm completely sold on the simplicity of it.

 

[Pat Hartman]

The DCount() will work fine and looks simpler but it requires running another query. The RecordsetClone method simply references an already open recordset and doesn't need to create and run a new query.