By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,087 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

close form if query returns no records

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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 =----
Nov 13 '05 #3

P: n/a
"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
Nov 13 '05 #4

P: n/a
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.
Nov 13 '05 #5

P: n/a
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.
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.