The field X cannot contain a null value because the ..........

[placek]

Hello

My program says i have a null value in a field, but i do
not. It says..........

'The field X cannot contain a null value because the
required property for this field is set to true. Enter a
value in this field'

Has anybody come accross this before and, if so, how did
you resolve it?

Thanks, Martin

 

[Steve Schapel]

Martin,

I have never seen this error message where there was in fact data in the
field. Can you explain the circumstances please? For example, on a
form or otherwise, using code or not, field in question bound to textbox
or combobox or something else, at what point in the process do you see
this problem? Thanks.

 

[Tim Ferguson]

Has anybody come accross this before and, if so, how did
you resolve it?

Yes, quite frequently! The correct answer depends on your business needs:

If the field does need a value in it (can you have an employee without a
LastName?), then you'll just have to type something in!

If the field can legally be nulled, then remove the Required property from
the field in Table Design mode.

Bear in mind that a field that takes any part in a Primary Key must be
Required (and therefore not have any nulls).

Hope that helps


Tim F

 

[Guest]

Hi Steve
A little bit more about the circumstances. ........The
field is populated using an input box and is triggered by
an on click event on a check box. VBA is used to do this-
it goes :

' Update tblLoanRelation
Set recLoanRelation = dbshigham.OpenRecordset
("tblLoanRelation", dbOpenDynaset)
recLoanRelation.AddNew
recLoanRelation(0) = lngBorrowerNumber
recLoanRelation(1) = lngAcquisitionNumber
recLoanRelation(3) = Date
recLoanRelation.Update

This is only a snippet of code, but lngBorrowerNumber
comes from an earlier input box. lngBorrowerNumber is
checked for authenticity before the program continues. It
goes:

lngBorrowerNumber = InputBox("Please enter your borrower
Number")
Set recBorrowerRelation = dbshigham.OpenRecordset
("tblBorrowerRelation")
Do Until recBorrowerRelation.EOF = True
If recBorrowerRelation(0) = lngBorrowerNumber Then
Exit Do
Else
recBorrowerRelation.MoveNext
If recBorrowerRelation.EOF Then
MsgBox "This Borrower Number does not exist!"
Exit Sub
End If
End If
Loop

There is a lot more code but probably irrelevant. I'm
pretty sure this is the section of code with the error.
The error message comes up when i move to a next record on
the subform.
I hope this info is sufficient, if not i can give you more
info . Let me know.
Thanks for your time.
Martin

 

[Steve Schapel]

Martin,

Nothing immediately strikes the eye as being wrong. If you look in the
table tblLoanRelation, has the new record been added with the Borrower
Number in the right place? What I haven't quite grasped is what is the
connection between these recordsets and the code and the subform where
the error is occurring.

 

[placek]

Hi Steve
Nothing really shone out to me as being wrong either. But
in answer to your question, the Borrower Number is always
in the right place. What confuses me is that the error
comes up even though the field is always populated.

Let me explain the connection between the recordsets, code
and subform. My program has a front end (frmControl),
which contains:
- a list box(List0): the rowsource property of list box
object variable set to Acquisition Number, ISBN, Title,
Author, Category;
- a text box(Text2);
- a command button (Command4); and
- a subform (fsubControl): the fields are
lngAcquisitionNumberCnt, strISBN, strTitle, strAuthor,
strCategory, dtmDateBorrowed, chkReserve and chkBorrow.

As you may tell, it is a library database. To use
frmControl a user selects an option in the list
box, types in a search string in the text box, clicks the
command button [this populates the subform] and clicks the
check boxes to Reserve or Borrow. When i click reserve or
Borrow [this brings up an input box requesting Borrower
Number] and then try to move to a new record, the program
freezes and the error message appears. The code for these
are(with a brief description of the tables):

- tblLoanRelation (lngBorrowerNumberCnt,
lngAcquisitionNumberCnt, dtmDateReserved, dtmDateBorrowed,
chkReserve, chkBorrow).
NB. lngAcquisitionNumberCnt is a unique number assigned to
a book. Together with lngBorrowerNumberCnt, these make a
composite primary key.
- tblAcquisitionRelation (lngAcquisitionNumberCnt,
strISBN, dtmDateAcquired)
NB. There can be duplicate copies of a book
- tblBookRelation (strISBN, strTitle, strAuthor,
strCategory).
- tblBorrowerRelation (lngBorrowerNumberCnt,
strBorrowerName).

Private Sub Borrow_Click()

' Purpose: To populate tblLoanRelation

On Error GoTo Err_Borrow_Click

Dim recSubFormRecordSource As Recordset
Dim recBorrowerRelation As Recordset
Dim recLoanRelation As Recordset
Dim dbshigham As Database
Dim lngCurrentRecord As Long
Dim intLoop As Integer
Dim lngAcquisitionNumber As Long
Dim lngBorrowerNumber As Long

Set dbshigham = DBEngine(0)(0)
Set recSubFormRecordSource = dbshigham.OpenRecordset
(Me.Form.RecordSource)
lngCurrentRecord = Me.CurrentRecord

' Obtain the Acquisition Number of the record being checked
Do Until recSubFormRecordSource.EOF = True
intLoop = intLoop + 1
If intLoop = lngCurrentRecord Then
lngAcquisitionNumber = recSubFormRecordSource(0)
Exit Do
Else
recSubFormRecordSource.MoveNext
End If
Loop

' Verify Borrower Number
lngBorrowerNumber = InputBox("Please enter your borrower
Number")
Set recBorrowerRelation = dbshigham.OpenRecordset
("tblBorrowerRelation")
Do Until recBorrowerRelation.EOF = True
If recBorrowerRelation(0) = lngBorrowerNumber Then
Exit Do
Else
recBorrowerRelation.MoveNext
If recBorrowerRelation.EOF Then
MsgBox "This Borrower Number does not exist!"
Exit Sub
End If
End If
Loop

' Update tblLoanRelation
Set recLoanRelation = dbshigham.OpenRecordset
("tblLoanRelation", dbOpenDynaset)
recLoanRelation.AddNew
recLoanRelation(0) = lngBorrowerNumber
recLoanRelation(1) = lngAcquisitionNumber
recLoanRelation(3) = Date
recLoanRelation.Update

MsgBox "This is confirmation that Borrower Number " &
lngBorrowerNumber & " has borrowed " & _
"Acquisition Number " & lngAcquisitionNumber

Exit_Borrow_Click:
Exit Sub

Err_Borrow_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Borrow_Click

End Sub

Private Sub Reserve_Click()

' Purpose: To populate tblLoanRelation

Dim recSubFormRecordSource As Recordset
Dim recBorrowerRelation As Recordset
Dim recLoanRelation As Recordset
Dim dbshigham As Database
Dim lngCurrentRecord As Long
Dim intLoop As Integer
Dim lngAcquisitionNumber As Long
Dim lngBorrowerNumber As Long

On Error GoTo Err_Reserve_Click

Set dbshigham = DBEngine(0)(0)
Set recSubFormRecordSource = dbshigham.OpenRecordset
(Me.Form.RecordSource)
lngCurrentRecord = Me.CurrentRecord

' Obtain the Acquisition Number of the record being checked
Do Until recSubFormRecordSource.EOF = True
intLoop = intLoop + 1
If intLoop = lngCurrentRecord Then
lngAcquisitionNumber = recSubFormRecordSource(0)
Exit Do
Else
recSubFormRecordSource.MoveNext
End If
Loop

' Verify the Borrower Number
lngBorrowerNumber = InputBox("Please enter your borrower
Number")
Set recBorrowerRelation = dbshigham.OpenRecordset
("tblBorrowerRelation")
Do Until recBorrowerRelation.EOF = True
If recBorrowerRelation(0) = lngBorrowerNumber Then
Exit Do
Else
recBorrowerRelation.MoveNext
If recBorrowerRelation.EOF Then
MsgBox "This Borrower Number does not exist!"
Exit Sub
End If
End If
Loop

' Update tblLoanRelation
Set recLoanRelation = dbshigham.OpenRecordset
("tblLoanRelation", dbOpenDynaset)
recLoanRelation.AddNew
recLoanRelation(0) = lngBorrowerNumber
recLoanRelation(1) = lngAcquisitionNumber
recLoanRelation(2) = Date
recLoanRelation.Update

MsgBox "This is confirmation that Borrower Number " &
lngBorrowerNumber & " has reserved " & _
"Acquisition Number " & lngAcquisitionNumber

Exit_Reserve_Click:
Exit Sub

Err_Reserve_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Reserve_Click

End Sub

Any help would be appreciated. Thanks for your ongoing
help, Steve.
Martin