invalid use of null on data entry

[pbuethe]

My data entry was working fine (I thought) but suddenly today, whenever I start to enter data in a new subform record (single form) I get the invalid use of null error.
(I can add subform records if the main record already has related records, but I can no longer add related record #1 on a new or existing main record.) The error occurs in the code below which is to generate the next sequential test_id # in tblTests.

tblTests has a many-to-one relationship with tblReviews and is related by the field CaseNbr which is the pk of tblReviews as well as a field in tblTests and on the form. cboTestID is a combo box from which to select existing test records.

The invalid use of null error happens on
the line starting i = DMax...

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim i As Integer
i = DMax("[Test_ID]", "tblTests", "[tblTests].[CaseNbr] = " & Me![CaseNbr]) + 1
Me!Test_ID = i
Me!cboTestID = i
End Sub

I have to hand the database off to someone else this week so I would like to solve this soon!

Thanks for any assistance.

 

[Travis]

From MSDN:

...The DMin and DMax functions ignore Null values in the field referenced by expr. However, if no record satisfies criteria or if domain contains no records, the DMin and DMax functions return a Null...

You need to check the return value of DMax to see if it is Null. If it is Null then i=1.

or

Use the Nz function (Null to Zero).

i = Nz(DMax("[Test_ID]", "tblTests", "[tblTests].[CaseNbr] = " & Me![CaseNbr]),0) + 1

This will "convert" Null values to Zero (0).

 

[pbuethe]

Thanks Travis, I used the Nz function and it solved the problem.