472,805 Members | 957 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 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 3018
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: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?

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.