Field cannot contain a Null Value

[annie52]

When I try to add a new record in the subform, I get this error:

"cannot contain a Null value because the required property for this field is set to True"

I don't understand why this Null error. I entered the following in a query against both tables and got good results (i.e., Not Null).

IIf(IsNull([TicketNum]),"Unknown","Not Null")

I can clear the popup box and continue and both tables get properly updated. Here's what I have behind the TicketNum field on the subform (the main form is "frmReqnWatch"):

Private Sub TicketNum_AfterUpdate()
On Error GoTo Err_TicketNum_AfterUpdate

Forms!frmReqnWatch.DLATicketNum = Me!TicketNum

Exit_TicketNum_AfterUpdate:
Exit Sub

Err_TicketNum_AfterUpdate:
MsgBox "Error #" & Err.Number & Err.Description
Resume Exit_TicketNum_AfterUpdate

End Sub

 

[PHV]

I'd try this:

Private Sub TicketNum_AfterUpdate()
If Trim(Me!TicketNum & "") <> "" Then
  Forms!frmReqnWatch!DLATicketNum = Me!TicketNum
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DimGeekAsString]

PHV is right.

I know Nulls are a confusing topic. His solution is universal and will work in every situation!!

 

[annie52]

Good morning folks. Unfortunately, this code does not work in my situation. Same error. I'm not sure what other information I could provide that might be helpful.

 

[annie52]

Are there any other thoughts out there?

I've had to change my linked fields to Not Required in order to get rid of the error message. Obviously, I don't want to leave things like that.

 

[JoeAtWork]

I've had to change my linked fields to Not Required in order to get rid of the error message.

The sample code you showed only had one field. Did you apply PHV's solution to all required fields (except the ones used to link the subform to the main form)?

 

[PHV]

Check the required fields in their BeforeUpdate event procedures, playing with the Cancel parameter.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[annie52]

Hi JoeAtWork and PHV.

To clarify, I have three child forms in a tab control. Each has a primary key that is related to the parent. I've tried different combinations of the Required property for each of the three fields. I've tried stepping through the code trying to trap the error (including BeforeUpdate) but haven't had any luck. During that process, I could only determine that the error occurs somewhere between the child form's BeforeInsert and AfterInsert events.

Here's what I decided to do. I set the primary key fields in the three child forms to Not Required since validation won't allow Null values in those fields anyway. Everything works well but I sure wish I understood what's happening in the background better. Hopefully, as I gain more experience, it will make more sense to me.

Thank you both for your support. I really appreciate it.