472,344 Members | 2,325 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Do not display form when no records, please help...

Hi all,

I have a form to list records (frmListIssue) which I call from different
other forms. My wish is to display a message when the form is called and
empty; no records to display. I want to use OpenArgs because sometimes I
don't want an empty form and sometime I don't. I tried everything, but
until now without success. I hope that someone can help me out here.

This is an example how I call the form:

Dim stDocName As String
Dim strSQL As String
strSQL = "SELECT * FROM tblIssue INNER JOIN tblIssueException " & _
"ON tblIssue.Issue_ID = tblIssueException.Issue_ID " & _
"WHERE (((tblIssueException.Exception_ID)=" & _
Me.cboException_ID & "));"

stDocName = "frmListIssue"
DoCmd.OpenForm stDocName, , , , , , "CloseIfNoData"

Forms!frmListIssue.RecordSource = strSQL
And this is what I have in the On Open event of the form that lists
records:

If Forms!frmListIssue.RecordsetClone.BOF And _
Forms!frmListIssue.RecordsetClone.EOF And _
Me.OpenArgs = "CloseIfNoData" Then
MsgBox "There were no records to display."
End If

The Msgbox is not displayed when the form is empty.
I appreciate all help!
Thanks!

Sandy

Nov 13 '05 #1
9 4601
Of course, I meant: sometimes I don't want an empty form and somtimes I
do.

Sandy <an*******@usenet.com> wrote in
news:Xn*******************@194.109.133.133:
Hi all,

I have a form to list records (frmListIssue) which I call from
different other forms. My wish is to display a message when the form
is called and empty; no records to display. I want to use OpenArgs
because sometimes I don't want an empty form and sometime I don't. I
tried everything, but until now without success. I hope that someone
can help me out here.


Nov 13 '05 #2
Would it make sense to look at the record source of the target form in the
calling form and make your decision to open based on the results.

You could create a function, let's say, IsIssueData(), and modify your
calling procedure. When you want to open regardless of data, just open,
when you don't

If IsIssueDate = -1 then
do something else
Else
docmd.openform...
End If

hth,
tim

"Sandy" <an*******@usenet.com> wrote in message
news:Xn*******************@194.109.133.133...
Hi all,

I have a form to list records (frmListIssue) which I call from different
other forms. My wish is to display a message when the form is called and
empty; no records to display. I want to use OpenArgs because sometimes I
don't want an empty form and sometime I don't. I tried everything, but
until now without success. I hope that someone can help me out here.

This is an example how I call the form:

Dim stDocName As String
Dim strSQL As String
strSQL = "SELECT * FROM tblIssue INNER JOIN tblIssueException " & _
"ON tblIssue.Issue_ID = tblIssueException.Issue_ID " & _
"WHERE (((tblIssueException.Exception_ID)=" & _
Me.cboException_ID & "));"

stDocName = "frmListIssue"
DoCmd.OpenForm stDocName, , , , , , "CloseIfNoData"

Forms!frmListIssue.RecordSource = strSQL
And this is what I have in the On Open event of the form that lists
records:

If Forms!frmListIssue.RecordsetClone.BOF And _
Forms!frmListIssue.RecordsetClone.EOF And _
Me.OpenArgs = "CloseIfNoData" Then
MsgBox "There were no records to display."
End If

The Msgbox is not displayed when the form is empty.
I appreciate all help!
Thanks!

Sandy

Nov 13 '05 #3
Thanks Tim,
I will try to do something like that, but that still doesn't explain why my
code doesn't work...

Sandy

"Tim Mills-Groninger" <ti***@earthlink.net> wrote in
news:iU*****************@newsread3.news.atl.earthl ink.net:
Would it make sense to look at the record source of the target form in
the calling form and make your decision to open based on the results.

You could create a function, let's say, IsIssueData(), and modify your
calling procedure. When you want to open regardless of data, just
open, when you don't

If IsIssueDate = -1 then
do something else
Else
docmd.openform...
End If

hth,
tim

Nov 13 '05 #4
This drives me crazy, despite the code below, the form is opened empty!
It is ok that it is empty, but not ok that it opens when it is empty.

Even stranger is: if I display the Me.recordsetclone.recordcount, then it
says there are 19 records!!?? But there are none!!

I am lost... Please help...

Thanks, Sandy
Sandy <an*******@usenet.com> wrote in news:Xns965C5CAD8E6B5anony@
194.109.133.133:
Hi all,

I have a form to list records (frmListIssue) which I call from different other forms. My wish is to display a message when the form is called and empty; no records to display. I want to use OpenArgs because sometimes I don't want an empty form and sometime I don't. I tried everything, but
until now without success. I hope that someone can help me out here.

This is an example how I call the form:

Dim stDocName As String
Dim strSQL As String
strSQL = "SELECT * FROM tblIssue INNER JOIN tblIssueException " & _
"ON tblIssue.Issue_ID = tblIssueException.Issue_ID " & _
"WHERE (((tblIssueException.Exception_ID)=" & _
Me.cboException_ID & "));"

stDocName = "frmListIssue"
DoCmd.OpenForm stDocName, , , , , , "CloseIfNoData"

Forms!frmListIssue.RecordSource = strSQL
And this is what I have in the On Open event of the form that lists
records:

If Forms!frmListIssue.RecordsetClone.BOF And _
Forms!frmListIssue.RecordsetClone.EOF And _
Me.OpenArgs = "CloseIfNoData" Then
MsgBox "There were no records to display."
End If

The Msgbox is not displayed when the form is empty.
I appreciate all help!
Thanks!

Sandy


Nov 13 '05 #5
Private Sub Form_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
If rst.BOF And rst.EOF Then
MsgBox "No records to display."
DoCmd.CancelEvent
End If
Set rst = Nothing
End Sub

Nov 13 '05 #6
Thanks Gord,

But your code doesn't work.
The form is opened, while there are no records to display.

I really hope someone can explain this.

"Gord" <gd*@kingston.net> wrote in news:1116685549.588256.133700
@g47g2000cwa.googlegroups.com:
Private Sub Form_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
If rst.BOF And rst.EOF Then
MsgBox "No records to display."
DoCmd.CancelEvent
End If
Set rst = Nothing
End Sub


Nov 13 '05 #7

Usenet wrote:
This drives me crazy, despite the code below, the form is opened empty! It is ok that it is empty, but not ok that it opens when it is empty.

Even stranger is: if I display the Me.recordsetclone.recordcount, then it says there are 19 records!!?? But there are none!!

I am lost... Please help...

Thanks, Sandy


How are you determining that there is no data?
Is Exception_ID an autonumber?
If Exception_ID si NOT an autonumber, could somehow blank records been
created?

Your filter criteria:
"WHERE (((tblIssueException.Exception_ID)=" & _
Me.cboException_ID & "));"

Nov 13 '05 #8
There is no data, I know this because I know the tables and the form
opens empty. But when I include something like

MsgBox rst.RecordCount

It tells there are 19 records (???) and then the form opens empty.

Yes, exception_ID is autonumber.
If I run my select statement as query it seems ok: it doesnot return any
records...

What seems to be wrong here?

Sandy
"rq******@sympatico.ca" <rq******@sympatico.ca> wrote in
news:11*********************@g47g2000cwa.googlegro ups.com:

How are you determining that there is no data?
Is Exception_ID an autonumber?
If Exception_ID si NOT an autonumber, could somehow blank records been
created?

Your filter criteria:
"WHERE (((tblIssueException.Exception_ID)=" & _
Me.cboException_ID & "));"


Nov 13 '05 #9
Sandy <an*******@usenet.com> wrote in
news:Xn*******************@194.109.133.133:
There is no data, I know this because I know the tables and
the form opens empty. But when I include something like

MsgBox rst.RecordCount

It tells there are 19 records (???) and then the form opens
empty.

Yes, exception_ID is autonumber.
If I run my select statement as query it seems ok: it doesnot
return any records...

What seems to be wrong here?

Sandy
A closer look at your original code told me what's wrong.
stDocName = "frmListIssue"
DoCmd.OpenForm stDocName, , , , , , "CloseIfNoData"

Forms!frmListIssue.RecordSource = strSQL
The open event in your called form is firing before the change
of the recordsource.

Possible fix options: 1)move the test to the on load event,
2) call the form with a where clause, if the form's query
differs from your SQL in the Where clause,
3) test your recordset in the calling form, and only open it if
the test returns records.

Q.
"rq******@sympatico.ca" <rq******@sympatico.ca> wrote in
news:11*********************@g47g2000cwa.googlegro ups.com:

How are you determining that there is no data?
Is Exception_ID an autonumber?
If Exception_ID si NOT an autonumber, could somehow blank
records been created?

Your filter criteria:
"WHERE (((tblIssueException.Exception_ID)=" & _
Me.cboException_ID & "));"


--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: owais | last post by:
H I want to display progress bar during execution of windows form. When I execute DTS package the form will idle till the DTS package finishes ...
1
by: Robert | last post by:
Need some help with this please. Is there a way to display more than one record at a time on a popup form? I have a main form that has a button...
3
by: DataBard007 | last post by:
My Access 97 database has a form that contains text boxes that display people's names, addresses, phone numbers, etc. The record source is a...
3
by: Daniel Tan | last post by:
Hi, i got a subform and inside got some records. But i want to press a button and make all those records to a report. It only display all those...
0
by: Carl | last post by:
I have a main form with navigation buttons on it and a label showing for example Record 1 of 15 using recordsetclone on it and eveything works...
17
by: perryche | last post by:
I have 5records, e.g. Rc1, Rc2, Rc3..., when user open up a form, I want it to open to a particular record (say Rc3) then when user chooses the...
2
by: kev | last post by:
Hi Folks, I have created a search query in which it successfully returns correct results. When there are no records returned, instead of giving...
3
by: WiseG1rly | last post by:
Hey everyone! I am completley new and I will start off by saying that I am not a programmer - figuring out this search took so long! I am...
2
by: kurtzky | last post by:
i created a form that should function as follows: i will enter a number in a textbox..then it should query from the database all the records which...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...

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.