Message Box when query return is empty.

  • Thread starter Thread starter pipeline
  • Start date Start date
P

pipeline

Guest
I am relatively new to Access (and VBA). I have built an application complete with a series of nested switchboards, to track general Project Information, Notes, Issues, Deliverable Information, etc. For my deliverable information, I use a form that is bound(?) to a query to display the information for a project. The query asks for the project number and then the results are displayed on the form. So far so good. Very often in the inital stages of a project, there are no deliverables in preparation (e.g. data gathering phase, etc.) Right now, when I query a project's deliverables, and none exist, I get a blank form returned. What I would like to be able to do is, if there are no deliverables for a project, I would like to have a message box returned with the Message "No deliverables exist for this project.", rather than have the blank form displayed on the screen. Clicking on (OKAY) on the message box would return to the previous menu. I am hoping this is relatively simple to do. Any help, insight on how to make this happen would be greatly appreciated. Thanks.
 
Use the DCount function to count the number of records returned by the query. If the query comes up empty, a message box pops up saying no records found (or whatever you want it to say). Here's some simple code that you can put in before you open the report:
========================================
Dim strWhat as String, inCnt as Integer
DoCmd.Hourglass True
strWhat = "(([SQL criteria]))"
intCnt = DCount("[Key Field]", "[Table Name]", strWhat)
If intCnt = 0 Then
MsgBox "There are no records that match this query.", 64, "Search Results"
DoCmd.Hourglass False
Exit Sub
End If
DoCmd.Hourglass False

========================================

For [SQL criteria] insert the SQL statement from your query, minus the "SELECT"
For [Key Field] insert the name of the key index field from your table
For [Table Name] insert the name of the name that's being queried
 
Thanks for this code I was able to use it without the strWhat string and linked it directly to my query as follows:

Dim inCnt as Integer
DoCmd.Hourglass True
intCnt = DCount("[Key Field]", "[Query Name]")
If intCnt = 0 Then
MsgBox "There are no reports for this year.", 64, "Report Status"
DoCmd.Hourglass False
Exit Sub
End If
DoCmd.Hourglass False

Cheers!
 
Last edited:
FYI, imperceptibly more efficient:

DCount("*", "[Query Name]")

presuming you don't care about nulls in the field being considered in the count.
 
FYI, imperceptibly more efficient:

DCount("*", "[Query Name]")

presuming you don't care about nulls in the field being considered in the count.

Yes I did that and also added criteria back in.

Thanks.
 
The criteria wouldn't be required to get a count of records returned by the query, only a count of a filtered subset of those records. Presumably that's what you need, just clarifying for later readers.
 
The criteria wouldn't be required to get a count of records returned by the query, only a count of a filtered subset of those records. Presumably that's what you need, just clarifying for later readers.

One of my Reports applies a filter from a form hence it is essential to have a criteria:
DoCmd.OpenReport "rptSummaryReport", acViewPreview, , Criteria

and use this to check for no results
DCount("*", "[qrySummaryReport]", Criteria)

Others don't require a criteria:
DoCmd.OpenReport "rptSummaryReport", acViewPreview

and use the code you provided :)
DCount("*", "[qrySummaryReport]")

Cheers!
 
As an FYI, you can use the no data event of the report as an alternative to testing first. It would be a little more dynamic.
 
As an FYI, you can use the no data event of the report as an alternative to testing first. It would be a little more dynamic.

This seems like a better option as the way I've done it runs the query twice. One for the "No Record check" and another for the Report. I will investigate :)

EDITED: I just tried it and it still loads the Report after I click "Ok" on the message box. Is there a way to stop it from loading the Report?
 
Last edited:
Did you add

Cancel = True

to the no data event?
 
Oh, and you need to trap for the error cancelling the report causes:

http://www.baldyweb.com/ErrorTrap.htm

Not a big deal since we usually have error handling though. ;)


Nice thanks, I applied the same solution:

In Report
Code:
Private Sub Report_NoData(Cancel As Integer)
    MsgBox "Selected criteria unable to generate a report. please select another criteria.", VBOKOnly, "Report Status"
    Cancel = True
End Sub
In form
Code:
Private Sub TestNoData_Click()
    On Error Resume Next
    DoCmd.OpenReport "rptSummaryReport", acViewPreview, Criteria
    If Err = 2501 Then Err.Clear
End Sub
from here:
http://access.mvps.org/access/reports/rpt0006.htm

Thank you for bringing this method to my attention :)
 

Users who are viewing this thread

Back
Top Bottom