Msg box when you get no records in a query

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

From a command button of o form, i display a report based on a query wich
returns the operations made in a period of time.
If I have no records, I get a blank report.
Is possible to get instead of it a MSG BOX with a text like "There are no
operations in the period 'fromDate' - 'toDate' !" (where 'fromDate' &
'toDate' are the dates of the begining and the end of the period taken from
the form)

I would apreciate any ...HEEEELP!!!!
 
Chris

you can put your MsgBox in the NoData event of the report. Then on the next
line: Cancel = True.

HTH,
Jeff
 
Pls try the Reort Nodata event. The script is below.


Private Sub Report_NoData(Cancel As Integer)

msgbox "There are no operations in the period "+cstr(fromDate)+" - "+
cstr(toDate) +"!"

End Sub
 
Pls try the Reort Nodata event. The script is below.


Private Sub Report_NoData(Cancel As Integer)

msgbox "There are no operations in the period "+cstr(fromDate)+" - "+
cstr(toDate) +"!"

End Sub
Click to expand...

In addition to the other replies you have received regarding placing
code in the report's OnNoData event, you should also trap the
resulting error to avoid the "You have cancelled " etc. message that
will occur.

In the command button click event that opens the report, add error
handling:
On Error GoTo Err_Handler
DoCmd.OpenReport "ReportName" etc.
Exit_ThisSub:
Exit Sub
Err_Handler:
If Err = 2501 Then
Else
MsgBox "Error #: " & err.Number & " " & err.Description
End If
Resume Exit_ThisSub
 
Thank you!
But, what if I need to display the results of the query in a form instead
of a report ? The request is now to display the results of the query in a
form and to put a cmd button in that form to open thr report when I have
records to display.
Thank tou, again,
Chris
 
Thank you!
But, what if I need to display the results of the query in a form
instead
of a report ? The request is now to display the results of the query in a
form and to put a cmd button in that form to open thr report when I have
NO records to display.
Thank tou, again,
Chris
 
In the Open event procedure of the form, check whether the query returns any
records. If not, display an appropriate message and set the Cancel argument
of the Open event procedure to True to cancel the opening of the form. For
example ...

'requires a reference to the Microsoft DAO 3.x Object Library (where x is
..51 for Access 97, 6 for later versions of Access)

Dim db As DAO.Database
dim rst As DAO.Recordset
dim boolFound As Boolean

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Count(*) AS TheCount FROM
YourQueryNameHere")
boolFound = rst.Fields("TheCount") <> 0
rst.Close
If Not boolFound Then
MsgBox "There are no records to display"
Cancel = True
End If
 
I tried to use that code, but, I am getting the error :

Compile error: User defined type not defined (related to "Dim db as
DAO.Database":)
How can it be fixed?
Is there another method?
 
That's why I included the comment about the code needing a reference to the
DAO object library! :-)

Alternatively, you could, if you prefer, use ADO (Access 2000 and later add
the ADO reference by default to new MDBs).

Dim rst As ADODB.Recordset
Dim boolFound As Boolean

Set rst = CurrentProject.Connection.Execute("SELECT Count(*) AS TheCount
FROM YourQueryNameHere", , adCmdText)

The rest of the code remains the same.
 
Run-time error ' -2147217904(800040e10)': No value given for one or more
required parameters (at the line Set rst =
CurrentProject.Connection.Execute("SELECT Count(*) AS TheCount FROM
intrariladata", , adCmdText) )
 
OK, looking back over the previous posts in this thread, I understand the
query includes references to form controls, yes? Something like "SELECT *
FROM SomeTable WHERE SomeField = Forms!SomeForm!SomeControl"? To open a
recordset on a parameter query like this, you must assign values to the
parameters before attempting to open the recordset. DAO and ADO examples
below, or alternativly, it might be simpler not to use the saved query at
all and just build the complete SQL string in code, e.g. "SELECT Count(*) AS
TheCount FROM SomeTable WHERE SomeField = " & Forms!SomeForm!SomeControl".
In other words, build the same criteria into the SQL statement as in the
saved query. (Remember that text values in criteria need to be delmited with
single quotes, and dates need to be delimited with '#' characters and in US
mm/dd/yyyy format). In fact, the more I think about it, the more I think
that's probably the simplest solution. But I'll include the DAO and ADO
examples anyway for completeness ...

Public Sub TestDAO()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim boolFound As Boolean

Set db = CurrentDb
Set qdf = db.QueryDefs("qryTest")
qdf.Parameters("Forms!frmTest!txtTest") = Forms!frmTest!txtTest
Set rst = qdf.OpenRecordset
If Not (rst.BOF And rst.EOF) Then
boolFound = True
End If
rst.Close

End Sub

Public Sub TestADO()

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rst As ADODB.Recordset
Dim boolFound As Boolean

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "SELECT Count(*) AS TheCount FROM qryTest"
.CommandType = adCmdText
Set prm = .CreateParameter("Forms!frmTest!txtTest", adVarChar,
adParamInput, 50, Forms!frmTest!txtTest)
.Parameters.Append prm
End With
Set rst = cmd.Execute
boolFound = rst.Fields("TheCount") <> 0

End Sub
 
Back
Top