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.

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

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

<pietlinden@hotmail.com> wrote in message
news:1113623069.185302.163790@l41g2000cwc.googlegr oups.com...
[color=blue]
> 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[/color]



----== 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 =----

"Jeff Conrad" <jeffc@ernstbrothers.com> wrote in message
news:42608d09$1_2@127.0.0.1...[color=blue]
> 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[/color]


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

Tom Travolta wrote:[color=blue]
> "Jeff Conrad" <jeffc@ernstbrothers.com> wrote in message
> news:42608d09$1_2@127.0.0.1...[color=green]
>> 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[/color]
>
>
> 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
>
>[/color]
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.

highway of diamonds wrote:[color=blue]
> Tom Travolta wrote:[color=green]
>> "Jeff Conrad" <jeffc@ernstbrothers.com> wrote in message
>> news:42608d09$1_2@127.0.0.1...[color=darkred]
>>> 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[/color]
>>
>>
>> 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
>>
>>[/color]
> 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.[/color]
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.