Queries with no records

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a query that has certain criteria. The results are displayed in a
form. If there are no records I want a message box to appear saying so
rather than the form. I have done this with a query that runs a report
using this code behind the OnNoData property
Private Sub cmdViewReport_Click()
On Error GoTo HandleErr
Me.Visible = False
Dim stDocName As String

stDocName = "rptDocbyDate"
If IsNull(Me.StartDatetxt) Then MsgBox "Please enter Start Date",
vbExclamation, "Enter Start Date"
Me.StartDatetxt.SetFocus
Cancel = True
If IsNull(Me.EndDatetxt) Then MsgBox "Please enter End Date",
vbExclamation, "Enter End Date"
Me.EndDatetxt.SetFocus
Cancel = True
If Not IsNull(StartDatetxt) And Not IsNull(EndDatetxt) And Not
IsNull(finddocnametxt) Then
DoCmd.Minimize
DoCmd.OpenReport stDocName, acPreview

End If
Exit_cmbViewReport_Click:
Exit Sub
HandleErr:
Select Case Err.Number
Case 2501
'The OpenReport action was cancelled.
'There no rows. So do nothing.
Case Else
MsgBox Err.Number & ": " & Err.Description
End Select
Resume Exit_cmbViewReport_Click

End Sub

How can I use this code to get the same effect for the query that creates a
form?

TIA
Tony Williams
PS I'm a relative newbie so would appreciate answers not too technical
please
 
Tony-

As you have discovered, there is no "No Data" event for a form. You can
solve the problem one of two ways:

1) Open the form Hidden from your code, check its recordset, and set Visible
= True if there are records or close if not.

DoCmd.OpenForm stDocName, WindowMode:=acHidden
If Forms(stDocName).RecordsetClone.RecordCount = 0 Then
MsgBox "No records."
DoCmd.Close acForm, stDocName
Else
Forms(stDocName).Visible = True
End If

2) Use the Open event of the form to test for records and set Cancel = True
much like you do in Report No Data:

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

The second technique does not work for a Report (which is why they invented
the NoData event) because a Report doesn't start to resolve its recordset
until after Open is done. This is why you can modify the report recordset
in Report Open or pop open a dialog form to resolve parameters successfully.
A form, however, has already started to build its recordset by the time Open
fires - you cannot modify the recordset or open a separate dialog at this
point because Access has already parsed the record source and is running it.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
John thanks a lot that is most useful
Tony
John Viescas said:
Tony-

As you have discovered, there is no "No Data" event for a form. You can
solve the problem one of two ways:

1) Open the form Hidden from your code, check its recordset, and set Visible
= True if there are records or close if not.

DoCmd.OpenForm stDocName, WindowMode:=acHidden
If Forms(stDocName).RecordsetClone.RecordCount = 0 Then
MsgBox "No records."
DoCmd.Close acForm, stDocName
Else
Forms(stDocName).Visible = True
End If

2) Use the Open event of the form to test for records and set Cancel = True
much like you do in Report No Data:

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

The second technique does not work for a Report (which is why they invented
the NoData event) because a Report doesn't start to resolve its recordset
until after Open is done. This is why you can modify the report recordset
in Report Open or pop open a dialog form to resolve parameters successfully.
A form, however, has already started to build its recordset by the time Open
fires - you cannot modify the recordset or open a separate dialog at this
point because Access has already parsed the record source and is running it.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
creates
Click to expand...
 
Back
Top