Bound Form's Recordcount

  • Thread starter Thread starter Chuck Minarik
  • Start date Start date
C

Chuck Minarik

I am opening a display form in VBA using the OpenForm
method of the DoCmd command with a WHERE clause to
restrict the records being displayed. If the WHERE clause
generates a recordset with no records, I'd like to display
a MsgBox and cancel the OpenForm Event. How can I
determine if the underlying recordset of the bound form
contains zero records? The Report object has a NoData
Event, but the Form object doesn't. How do I mimic this
function on a form? Thanks.
 
I am opening a display form in VBA using the OpenForm
method of the DoCmd command with a WHERE clause to
restrict the records being displayed. If the WHERE clause
generates a recordset with no records, I'd like to display
a MsgBox and cancel the OpenForm Event. How can I
determine if the underlying recordset of the bound form
contains zero records? The Report object has a NoData
Event, but the Form object doesn't. How do I mimic this
function on a form? Thanks.
Click to expand...

You can do this in the Open event of the form that is being opened.
Code the OPEN event of the second form:

If Me.RecordsetClone.RecordCount <1 Then
MsgBox "There are no records."
Cancel = true
End If
 
-----Original Message-----


You can do this in the Open event of the form that is being opened.
Code the OPEN event of the second form:

If Me.RecordsetClone.RecordCount <1 Then
MsgBox "There are no records."
Cancel = true
End If


--
Fred
Please respond only to this newsgroup.
I do not reply to personal email
.
Fred, Thanks for the reply...I had tried this with
Click to expand...
RecordSet, but not RecordSetClone. But RecordSetClone
doesn't work either. Me.RecordsetClone.RecordCount is
returning a value of 1. Any other ideas?
 
RecordSet, but not RecordSetClone. But RecordSetClone
doesn't work either. Me.RecordsetClone.RecordCount is
returning a value of 1. Any other ideas?
Click to expand...

So? If you have at least 1 record, you have returned more than zero
records and the form will open. That's what you asked for.

When you open a form it will return a recordcount of 1, even if there
are more records, until you navigate to the next record. Then the
total count is available. For the purpose of your question of not
opening the form if there are no records, 1 is as good as 10000.

If you want the full number of records returned when you first open
the form, you need to change the code:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Handler
' The next line will generate error 3021 if there
' are no records

Me.RecordsetClone.MoveLast

' Me.RecordSetClone.RecordCount now will show the full number of
' records, but we don't care what it is, as long as it's at least 1
' record. If there are no records, the error occurs,
' which is trapped in the Err_Handler.
' If you wish to display the number of records, if there are any, use:
' MsgBox "There are " & Me.RecordSetClone.RecordCount & " records."

Exit_Form_Open:
Exit Sub

Err_Handler:
If Err = 3021 Then
MsgBox "No Records"
Cancel = True
End If
Resume Exit_Form_Open

End Sub

The Recordset will now show the full number of records, if there are
any. If there are no records a message box will appear and the form
will not open.
What have you gained from the previous post?
 
As well as fredg's good info, you may wish to close the called form like
this:

docmd.close acform, me.name
exit sub

instead of setting Cancel=true. The manual close method will not upset the
calling code. Setting Cancel will cause a runtime error in the calling code,
so you'd need to add an On Error Resumt Next to suppress that error.

HTH,
TC
 
-----Original Message-----


So? If you have at least 1 record, you have returned more than zero
records and the form will open. That's what you asked for.

When you open a form it will return a recordcount of 1, even if there
are more records, until you navigate to the next record. Then the
total count is available. For the purpose of your question of not
opening the form if there are no records, 1 is as good as 10000.

If you want the full number of records returned when you first open
the form, you need to change the code:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Handler
' The next line will generate error 3021 if there
' are no records

Me.RecordsetClone.MoveLast

' Me.RecordSetClone.RecordCount now will show the full number of
' records, but we don't care what it is, as long as it's at least 1
' record. If there are no records, the error occurs,
' which is trapped in the Err_Handler.
' If you wish to display the number of records, if there are any, use:
' MsgBox "There are " & Me.RecordSetClone.RecordCount & " records."

Exit_Form_Open:
Exit Sub

Err_Handler:
If Err = 3021 Then
MsgBox "No Records"
Cancel = True
End If
Resume Exit_Form_Open

End Sub

The Recordset will now show the full number of records, if there are
any. If there are no records a message box will appear and the form
will not open.
What have you gained from the previous post?

--
Fred
Please respond only to this newsgroup.
I do not reply to personal email
.
Click to expand...
Thanks Fred...I was getting some flaky results yesterday,
but I've got your solution working now. Thanks again.
 
Back
Top