Error in expression when report returns no record

[John]

Hi

In a report when I use a bound field in an expression in Header_Format event
I get the 'You entered an expression that has no value' error when record
source query returns no records. How can I check for no value in this case?

Thanks

Regards

 

[Tom Wickerath]

Hi John,

Cancel the report's Open event if the recordsource contains no records. The
following code works in Access 2000 and later versions. This code goes into a
class module associated with the report:

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ProcError

MsgBox "There is no data for the selected criteria.", _
vbInformation, "No Data Available..."
Cancel = True

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Report_NoData..."
Resume ExitProc
End Sub


If you are opening your report via a command button on a switchboard, you
will want to trap for the resulting Error 2501 that will be generated when
the above code fires. Here is an example of doing this:

Private Sub cmdPreviewAllItemsbyGroup_Click()
On Error GoTo ProcError

DoCmd.OpenReport "rptAllItemsbyGroup", View:=acViewPreview

ExitProc:
Exit Sub
ProcError:
Select Case Err.Number
Case 2501 'Report canceled.
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in cmdPreviewAllItemsbyGroup_Click event procedure..."
End Select
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[James A. Fortune]

Hi

In a report when I use a bound field in an expression in Header_Format event
I get the 'You entered an expression that has no value' error when record
source query returns no records. How can I check for no value in this case?

Thanks

Regards

I like to see if the query result returns no records before opening the
report. Besides, it all might be faster than going through the internal
code to raise an error, especially if the table contains a primary key .

Public Function DNoRecords(strSQL As String) As Boolean
Dim MyDB As Database
Dim CountRS As Recordset

DNoRecords = True
Set MyDB = CurrentDb
Set CountRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If CountRS.RecordCount > 0 Then
DNoRecords = False
End If
CountRS.Close
Set CountRS = Nothing
Set MyDB = Nothing
End Function

Sample:

Dim strSQL As String

strSQL = "SELECT * FROM MyTable;"
If DNoRecords(strSQL) = False Then
OpenReport "rptMyReport"
Else
MsgBox("The report's recordset does not return any records.")
End If

Note: A querydef can also be used to obtain the SQL used for a report's
RecordSource if it is based on a predefined query.

James A. Fortune
(e-mail address removed)

"This could potentially displace other sources of electrical production
that produce greenhouse gases, such as coal," Sargent said.

In Japan, the world's largest solar-power market, the government expects
that 50 percent of residential power supply will come from solar power
by 2030, up from a fraction of a percent today.

The biggest hurdle facing solar power is cost-effectiveness.

At a current cost of 25 to 50 cents per kilowatt-hour, solar power is
significantly more expensive than conventional electrical power for
residences. Average U.S. residential power prices are less than ten
cents per kilowatt-hour, according to experts.

But that could change with the new material. --
http://news.nationalgeographic.com/news/2005/01/0114_050114_solarplastic.html

If solar power becomes so inexpensive in a few years that oil prices
become threatened, then the U.S. government will find a way to tax it to
make up for lost gasoline taxes. -- Robert Trapp