Closing a form automatically if no records

Hi all,
Im opening a bound form, targetForm from other forms, but want close it if
there's no records in it. Would be good if I could do this from within the
targetForm, since I open it from various activities.

I have already tried to close it from the onOpen event of targetForm but
without success.
Any advice welcome.

Thanks,

Gerry Abbott,

re: Closing a form automatically if no records

Try something like this:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
MsgBox "Nobody home."
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gerry Abbott" <please@ask.ie> wrote in message
news:ox0Gf.5632$j7.211856@news.indigo.ie...[color=blue]
> Hi all,
> Im opening a bound form, targetForm from other forms, but want close it if
> there's no records in it. Would be good if I could do this from within the
> targetForm, since I open it from various activities.
>
> I have already tried to close it from the onOpen event of targetForm but
> without success.
> Any advice welcome.[/color]

re: Closing a form automatically if no records

On Tue, 7 Feb 2006 12:38:47 -0000, "Gerry Abbott" <please@ask.ie> wrote:
[color=blue]
>Hi all,
>Im opening a bound form, targetForm from other forms, but want close it if
>there's no records in it. Would be good if I could do this from within the
>targetForm, since I open it from various activities.
>
>I have already tried to close it from the onOpen event of targetForm but
>without success.
>Any advice welcome.
>
>Thanks,
>
>Gerry Abbott,
>
>
>[/color]

Private Sub Form_Open(Cancel As Integer)

With Me.RecordsetClone
If .RecordCount = 0 Then
Beep
MsgBox "There are no records to view", vbInformation + vbOKOnly
Cancel = True
End If
End With

End Sub
Wayne Gillespie
Gosford NSW Australia

re: Closing a form automatically if no records

Two ways AFAIK,

A cancel on form open.
But: When you are opening your form with code you will have to trap error 2501

Or:
Private Sub Form_Load()
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records.."
DoCmd.Close
End If
End Sub


Arno R

"Gerry Abbott" <please@ask.ie> schreef in bericht news:ox0Gf.5632$j7.211856@news.indigo.ie...[color=blue]
> Hi all,
> Im opening a bound form, targetForm from other forms, but want close it if
> there's no records in it. Would be good if I could do this from within the
> targetForm, since I open it from various activities.
>
> I have already tried to close it from the onOpen event of targetForm but
> without success.
> Any advice welcome.
>
> Thanks,
>
> Gerry Abbott,
>
>
>
>[/color]

re: Closing a form automatically if no records

Arno R <arraNOcomSPAM@tiscali.nl> wrote:
: Private Sub Form_Load()
: If Me.RecordsetClone.RecordCount = 0 Then
^^^^^
why isn't Recordset itself good enough here?
--thelma
: MsgBox "No records.."
: DoCmd.Close
: End If
: End Sub

: Arno R

re: Closing a form automatically if no records

"Thelma Lubkin" <thelma@alpha2.csd.uwm.edu> schreef in bericht news:dsacbl$79i$1@uwm.edu...[color=blue]
> Arno R <arraNOcomSPAM@tiscali.nl> wrote:
> : Private Sub Form_Load()
> : If Me.RecordsetClone.RecordCount = 0 Then
> ^^^^^
> why isn't Recordset itself good enough here?
> --thelma
> : MsgBox "No records.."
> : DoCmd.Close
> : End If
> : End Sub
>
> : Arno R[/color]

I guess one an use Me.Recordset also, but I see this property is only available with Access2k or better.
I am used to use Recordsetclone, but it was in the Access 2.0 days that I learned about that ...

Arno R

re: Closing a form automatically if no records

Thelma Lubkin <thelma@alpha2.csd.uwm.edu> wrote in
news:dsacbl$79i$1@uwm.edu:
[color=blue]
> Arno R <arraNOcomSPAM@tiscali.nl> wrote:
>: Private Sub Form_Load()
>: If Me.RecordsetClone.RecordCount = 0 Then
> ^^^^^
> why isn't Recordset itself good enough here?
> --thelma
>: MsgBox "No records.."
>: DoCmd.Close
>: End If
>: End Sub[/color]

For one, Me.Recordset did not exist until Access 2000.

Secondly, it's not the same thing as the RecordsetClone.

Try it and see if there's a difference. My bet is that there *is* a
difference, but not necessarily for this particular task.

You might also want to check out Michael Kaplan's article on
Recordset vs. RecordsetClone:

http://trigeminal.com/usenet/usenet022.asp?1033

Personally, I don't see any utility in the Recordset object of a
form unless you are assigning a recordset to the form in code,
rather than using the form's Recordsource to define the content of
the form.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/