Cancel Report when Value Is Null

[ijswalker]

Hi,

Does anyone know of a simple way to stop a report in Access from printing if the underlying table has a Null Value in it?

I have a report that references a table with six columns. First two columns are text and the next four have revenue figures. The 4 columns each refer to an entity. I want to be able to stop a report printing if any of the 4 columns contain a Null value.

Thanks

Ian

 

[ijswalker]

Hi Uncle Gizmo,

I have tried what you suggested and I do like this idea. Here is the Function that seems to work when I run it in the Immediate Window.

Function addfournumbers(Indy As Integer, Carmel As Integer, Stress As Integer, womens As Integer)
addfournumbers = Null + 2 + 3 + 4
End Function

I then have an Report OnOpen event (see below) that references the function but I seem to be having issues getting this to work. No idea what I'm doing wrong.

Private Sub Report_Open(Cancel As Integer)
If addfournumbers = Null Then
DoCmd.Quit
Else
DoCmd.OpenReport "Report_rptRevenueProjection", acViewNormal
End If
End Sub

Can you take a look?

Thanks

Ian

 

[ijswalker]

There you go

 

[ijswalker]

Hi Uncle Gizmo,

I'm going to need quite a bit of help on this one.

Where do I place the code? Do I need to include this as part of the Events in the report?

Thanks

Ian

 

[ijswalker]

Hi Uncle Gizmo,

I understand your table design reference however I am stuck with it at the moment. I also cannot open the zipped file as I can have winzip on my computer. Can you reattach the file unzipped? Also, can you recommend any good training material for VBA. I am very new to this and am trying to understand the basics in order to increase my ability to have better functionality in my applications.

Thanks

Ian

 

[ijswalker]

Uncle Gizmo,

YOU ARE AWESOME!! Finally got the zip file opened. Incorporated what you wrote into the database and it does exactly what I need it to do. I have one or two tweeks to the functionality of the database but this is terrific. I can see other applications for method too.

Once again thanks

Ian

 

[ijswalker]

Hi Uncle Gizmo,

The precedure works fine in my test database. I then moved the code to the live database to the open form event and I keep getting the error

Compile error: User-defined type not defined

Then the following code is highlighted in the function

Dim DB As DAO.Database

Any Ideas,

Thanks

Ian

 

[boblarson]

Check your References (in the VBA window, click Tools > References) and see if you have a DAO reference set. If not, you will need to set one.

 

[ijswalker]

Thank you so much,

It works

Cheers

Ian