473,326 Members | 2,148 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

close form if query returns no records

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
5 3066
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
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
"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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Sans Spam | last post by:
Greetings! I have a table that contains all of the function permissions within a given application. These functions are different sections of a site and each has its own permissions (READ, WRITE,...
3
by: Steve | last post by:
Form FrmRestock's recordsource is QryFrmRestock. The TransactionDate field's criteria is set ats: Forms!FrmRestock!LastXDays. LastXDays on the form is a combobox where the selections are 30, 60...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
1
by: longtim | last post by:
I have been having endless difficulty creating reports/queries that set any relevent parameters from controls in forms. I am creating an application under access 2003 but will target access...
1
by: piet | last post by:
I have created a continues form, based on a query. In the header, there are some comboboxes that gives the user the possibility to make certain selections. Everytime a selection is made on a...
6
by: fumanchu | last post by:
I've got to let end users (really just one person) load billing batch files into a third party app table. They need to specify the billing cycle name, the batch name, and the input file name and...
6
by: Dave | last post by:
On my form I have combo boxes. These combo boxes, after updating them, populate respective listboxes that are located below the combo boxes on the same form. I am trying to use a "generate...
4
by: JPG4 | last post by:
I have tried just about everything that I can think of, so now I turn to you all for help! I have created a form (titled "OAG") with an unbound textbox titled "DOW". I also have a Query that I...
4
by: dizzydangler | last post by:
Hi all, I am a new Access user and just starting to get my head around some of the basic concepts, so please take it easy on me :) My company has been managing client records on excel, and I’m in...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.