VBA to check if recordset is empty?

[paul1707]

Hi All,

Can someone please tell me if this is the correct syntax for testing a query for records?

if forms!frmName.recordsetclone.eof then


Thank for your time.

 

[chergh]

No not really you can test to see if both recordset.eof and recordset.bof are true personally I would use recordset.recordcount = 0.

 

[paul1707]

Thanks for that, what i am trying to achieve is to have a command button which will open a form based on a query.

If the query returns no records, the open form section can be cancelled out and a msg sent to the user stating nothing to do.

I guess me question is: how do i check the query for content from the on_click command?


Thanks again

 

[ecawilkinson]

hi Paul,

In the Form that you are opening put:

Private Sub Form_Open(Cancel As Integer)
    If Me.Recordset.RecordCount = 0 Then
        Cancel = True
    End If
End Sub

then open the form like normal in the on_click of the command button.

HTH,
Chris

 

[paul1707]

Hi Chris,

Thanks for that, i solved it a little earlier with:

Private Sub Command3_Click()
If DCount("*", "qryBuilderJobsAlloc") > 0 Then
DoCmd.OpenForm "frmBuilderJobsAlloc", windowmode:=acDialog
Exit Sub
Else
MsgBox "No Records To Display"
End If
End Sub

This works a treat as i'm sure yours would also.

Thank you very much.

Regards

 

[buxtripper]

How about this problem.
I used recordset.count and it returned 9 yet I am getting this error
"Either EOF or BOF is True, or the current record has been deleted.Requested operation requires a current record"

in this line
GSTSDB.rsAssessment.MoveLastcurBalance = GSTSDB.rsAssessment.Fields("Balance").Value

kindly help. Thank you