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 9 4469
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.
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
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
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
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
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
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 & "));"
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 & "));"
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. This discussion thread is closed Replies have been disabled for this discussion. Similar topics
2 posts
views
Thread by owais |
last post: by
|
1 post
views
Thread by Robert |
last post: by
|
3 posts
views
Thread by DataBard007 |
last post: by
|
3 posts
views
Thread by Daniel Tan |
last post: by
|
reply
views
Thread by Carl |
last post: by
|
17 posts
views
Thread by perryche |
last post: by
|
2 posts
views
Thread by kev |
last post: by
| | | | | | | | | | | | | |