close form if query returns no records

[highway of diamonds]

I have a form based on a query. I would like to generate a msgbox and
close the form should the query return no records. [or not open the form
at all, either would be good]

TIA

R.

PS I would even be happy to lose the form and just do the same with the
query in datasheet view.

[pietlinden]

normally, you'd trap for the NoData event of a report, but alas, this
isn't a report. I did get this to work, but I'd recommend that any
self-respecting Access programmer finish swallowing before reading
futher...

This works...

Private Sub Form_Load()
Dim rs As DAO.Recordset
Dim intRecords As Integer

Set rs = Me.RecordsetClone
'---you shouldn't need these two lines. You'll get 1 if there are
any records.
rs.MoveLast
rs.MoveFirst

If rs.RecordCount =0 Then
rs.Close
Set rs = Nothing
DoCmd.Close acForm, Me.Name, acSaveNo
Else
MsgBox rs.RecordCount & " records."
rs.Close
Set rs = Nothing
End If

End Sub

[Jeff Conrad]

Even shorter:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no records to display at this time." _
, vbInformation, "No Records"
Cancel = True
End If
End Sub

You will of course need to trap for and ignore Error 2501
in the code rountine that opens the form.

--
Jeff Conrad
Access Junkie
Bend, Oregon

<pi********@hotmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

normally, you'd trap for the NoData event of a report, but alas, this
isn't a report. I did get this to work, but I'd recommend that any
self-respecting Access programmer finish swallowing before reading
futher...

This works...

Private Sub Form_Load()
Dim rs As DAO.Recordset
Dim intRecords As Integer

Set rs = Me.RecordsetClone
'---you shouldn't need these two lines. You'll get 1 if there are
any records.
rs.MoveLast
rs.MoveFirst

If rs.RecordCount =0 Then
rs.Close
Set rs = Nothing
DoCmd.Close acForm, Me.Name, acSaveNo
Else
MsgBox rs.RecordCount & " records."
rs.Close
Set rs = Nothing
End If

End Sub

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[Tom Travolta]

"Jeff Conrad" <je***@ernstbrothers.com> wrote in message
news:42**********@127.0.0.1...

Even shorter:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no records to display at this time." _
, vbInformation, "No Records"
Cancel = True
End If
End Sub

You will of course need to trap for and ignore Error 2501
in the code rountine that opens the form.

--
Jeff Conrad

Even shorter:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no..."
Cancel = True
End If
End Sub

[highway of diamonds]

Tom Travolta wrote:

"Jeff Conrad" <je***@ernstbrothers.com> wrote in message
news:42**********@127.0.0.1...

Even shorter:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no records to display at this time." _
, vbInformation, "No Records"
Cancel = True
End If
End Sub

You will of course need to trap for and ignore Error 2501
in the code rountine that opens the form.

--
Jeff Conrad

Even shorter:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no..."
Cancel = True
End If
End Sub

Ok, thanks folks. I'll have to digest and try these. My VBA skills
aren't that good and I was hoping to do it with macro or form properties
if I could.

R.

[highway of diamonds]

highway of diamonds wrote:

Tom Travolta wrote:

"Jeff Conrad" <je***@ernstbrothers.com> wrote in message
news:42**********@127.0.0.1...

Even shorter:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no records to display at this time." _
, vbInformation, "No Records"
Cancel = True
End If
End Sub

You will of course need to trap for and ignore Error 2501
in the code rountine that opens the form.

--
Jeff Conrad

Even shorter:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no..."
Cancel = True
End If
End Sub

Ok, thanks folks. I'll have to digest and try these. My VBA skills
aren't that good and I was hoping to do it with macro or form properties
if I could.

R.

Yes!!! works well, thanks very much.

I don't use Access much these days [or Vis Basic], this was a project I
was asked to do, got it up and running but small things like this make a
lot more user friendly. Need to keep my hand in I think.

R.