Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Filter out blank info using access 2007

Jun 14, 2010 7:52AM PDT

Hello all,
I'm building a database to send out work orders to my coworkers. I currently have the report based off of a query that I have called "action item by name." The report runs correctly except for cases in which there are no applicable records. What it does in this case is it kicks out a blank report. What I would like for it to do is display a message box stating something to the effect of "you don't have any action itmes" or something along those lines. I also have a macro set up based on this same query that emails the report to the individuals involved. I would like it to behave in the same way when that macro is run. Thanks! Jeremy

Discussion is locked

- Collapse -
Re: bank report
Jun 14, 2010 5:11PM PDT

A report doesn't throw out a message, it makes a report.

So what you should do is to check the number of records in the query for the report BEFORE calling the report. If it's > 0, go ahead. If it's 0: show the message box instead. That's a few lines in a macro and/or in VBA.

Kees

- Collapse -
re: Blank report
Jun 15, 2010 1:12AM PDT

Yes, I'm aware of the fact that a report doesn't throw out a message. I am designing this for users that don't know anything about Access. What I was wondering is if it is possible to add another process (such as a Macro) that would first run the query and then run the report only if records that are applicable exist.

- Collapse -
Surely.
Jun 15, 2010 1:20AM PDT

Either a macro or a VBA-module, that's called from a button (click event the users clicks on. In the VBA use a DCOUNT-function to see if there's something to print.

The pseudocode is
if (dcount(...) > 0) then
do the report
else
msgbox ("Sorry, no data to print.")
end if

- Collapse -
re: blank report
Jun 15, 2010 3:02AM PDT

How would I call it from a macro?

- Collapse -
Re: calling from a macro
Jun 15, 2010 7:39AM PDT

What do you want to call from a macro?

Building a basic macro isn't difficult. It's one page (234) in Microsofts booklet 'Microsoft Office Access 2007 Plain and Simple'. Let me quote it:

1. Click the Create tab
2. Click the top part of the Macro button
3. Click the down arrow in the first action cell.
4. Click the desired action.
5. Fill in the arguments for the action. The arguments you can set will be different for every action.
Repeat steps 3 through 5 on subsequent table rows to add actions to your macro
6. On the Quick Access Toolbar, click the Save button.
7. In the Save As dialog box that appears, type the name of the macro.
8. Click OK.

Kees