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

P

placek

Hi there,
I've created a library database, with a user interface.
The tables are:
tblLoanRelation (lngBorrowerNumberCnt[FK],
lngAcquisitionNumberCnt[FK], dtmDateReserved,
dtmDateBorrowed, chkReserve, chkBorrow);
tblBorrowerRelation (lngBorrowerNumberCnt[PK],
strBorrowerName);
tblAcquisitionRelation (lngAcquisitionNumberCnt[PK],
strISBN[FK], dtmDateAcquired); and
tblBookRelation (strISBN[PK], strTitle, strAuthor,
strCategory).
The user interface has a listbox(containing search
criteria such as title, author etc...), a text box, a
command button and a sub form. The command button has the
following code attached to it(comments included):
Private Sub Command4_Click()

' Purpose: To populate the subform according to the search
criteria in the text box
' Returns: values in the subform
' Created by: Martin Placek
' Created on: 5/5/04

Dim lngCriteria As Long
Dim lstBook As ListBox
Dim rec As Recordset
Dim dbshigham As Database
Dim strBookSpec As String

On Error GoTo Err_Command4_Click

Set lstBook = Me.List0
Set dbshigham = DBEngine.Workspaces(0).Databases(0)

' Populate the subform according to criteria entered in
text box and option selected in the list box. If the value
' entered does not exist a message informs the user.
Reserved books are omitted.
Select Case lstBook.Value
Case "Acquisition Number"
lngCriteria = Text2.Value
Set rec = dbshigham.OpenRecordset
("tblAcquisitionRelation")
Do Until rec.EOF = True
If lngCriteria = rec(0) Then
Me.fsubControl.Form.RecordSource = "Select
tblAcquisitionRelation.lngAcquisitionNumberCnt, " & _
"tblBookRelation.strISBN,
tblBookRelation.strTitle, tblBookRelation.strAuthor, " & _
"tblBookRelation.strCategory,
tblLoanRelation.dtmDateBorrowed, " & _
"tblLoanRelation.chkReserve,
tblLoanRelation.chkBorrow " & _
"FROM tblLoanRelation RIGHT JOIN
(tblAcquisitionRelation RIGHT JOIN tblBookRelation ON " & _
"tblAcquisitionRelation.strISBN=tblBookRela
tion.strISBN) ON " & _
"tblLoanRelation.lngAcquisitionNumberCnt=tb
lAcquisitionRelation.lngAcquisitionNumberCnt WHERE " & _
"tblAcquisitionRelation.lngAcquisitionNumbe
rCnt=" & lngCriteria & " AND " & _
"(isnull(tblLoanRelation.dtmDateReserved)
AND isnull(tblLoanRelation.dtmDateBorrowed) OR " & _
"not isnull
(tblLoanRelation.dtmDateReserved) AND not isnull
(tblLoanRelation.dtmDateBorrowed) OR " & _
"isnull(tblLoanRelation.dtmDateReserved)
AND not isnull(tblLoanRelation.dtmDateBorrowed))"
Exit Sub
Else
rec.MoveNext
End If
Loop
MsgBox "Not a valid number, please try again!"
Case "ISBN"
strBookSpec = Text2.Value
Set rec = dbshigham.OpenRecordset
("tblBookRelation")
Do Until rec.EOF
If strBookSpec = rec(0) Then
Me.fsubControl.Form.RecordSource = "Select
tblAcquisitionRelation.lngAcquisitionNumberCnt, " & _
"tblBookRelation.strISBN,
tblBookRelation.strTitle, tblBookRelation.strAuthor, " & _
"tblBookRelation.strCategory,
tblLoanRelation.dtmDateReserved,
tblLoanRelation.dtmDateBorrowed, " & _
"tblLoanRelation.chkReserve,
tblLoanRelation.chkBorrow,
tblLoanRelation.lngBorrowerNumberCnt " & _
"FROM tblLoanRelation RIGHT JOIN
(tblAcquisitionRelation RIGHT JOIN tblBookRelation ON " & _
"tblAcquisitionRelation.strISBN=tblBookRela
tion.strISBN) ON " & _
"tblLoanRelation.lngAcquisitionNumberCnt=tb
lAcquisitionRelation.lngAcquisitionNumberCnt WHERE " & _
"tblBookRelation.strISBN='" & strBookSpec
& "' AND " & _
"(isnull(tblLoanRelation.dtmDateReserved)
AND isnull(tblLoanRelation.dtmDateBorrowed) OR " & _
"not isnull
(tblLoanRelation.dtmDateReserved) AND not isnull
(tblLoanRelation.dtmDateBorrowed) OR " & _
"isnull(tblLoanRelation.dtmDateReserved)
AND not isnull(tblLoanRelation.dtmDateBorrowed))"
Exit Sub
Else
rec.MoveNext
End If
Loop
MsgBox "Not a valid number, please try again!"
Case "Title"
strBookSpec = Text2.Value
Set rec = dbshigham.OpenRecordset
("tblBookRelation")
Do Until rec.EOF
If InStr(1, rec(1), strBookSpec) Then
Me.fsubControl.Form.RecordSource = "Select
tblAcquisitionRelation.lngAcquisitionNumberCnt, " & _
"tblBookRelation.strISBN,
tblBookRelation.strTitle, tblBookRelation.strAuthor, " & _
"tblBookRelation.strCategory,
tblLoanRelation.dtmDateReserved,
tblLoanRelation.dtmDateBorrowed, " & _
"tblLoanRelation.chkReserve,
tblLoanRelation.chkBorrow,
tblLoanRelation.lngBorrowerNumberCnt " & _
"FROM tblLoanRelation RIGHT JOIN
(tblAcquisitionRelation RIGHT JOIN tblBookRelation ON " & _
"tblAcquisitionRelation.strISBN=tblBookRela
tion.strISBN) ON " & _
"tblLoanRelation.lngAcquisitionNumberCnt=tb
lAcquisitionRelation.lngAcquisitionNumberCnt WHERE " & _
"tblBookRelation.strTitle LIKE '*" &
strBookSpec & "*' AND " & _
"(isnull(tblLoanRelation.dtmDateReserved)
AND isnull(tblLoanRelation.dtmDateBorrowed) OR " & _
"not isnull
(tblLoanRelation.dtmDateReserved) AND not isnull
(tblLoanRelation.dtmDateBorrowed) OR " & _
"isnull(tblLoanRelation.dtmDateReserved)
AND not isnull(tblLoanRelation.dtmDateBorrowed))"
Exit Sub
Else
rec.MoveNext
End If
Loop
MsgBox "No matches!"
Case "Author"
strBookSpec = Text2.Value
Set rec = dbshigham.OpenRecordset
("tblBookRelation")
Do Until rec.EOF
If InStr(1, rec(2), strBookSpec) Then
Me.fsubControl.Form.RecordSource = "Select
tblAcquisitionRelation.lngAcquisitionNumberCnt, " & _
"tblBookRelation.strISBN,
tblBookRelation.strTitle, tblBookRelation.strAuthor, " & _
"tblBookRelation.strCategory,
tblLoanRelation.dtmDateReserved,
tblLoanRelation.dtmDateBorrowed, " & _
"tblLoanRelation.chkReserve,
tblLoanRelation.chkBorrow,
tblLoanRelation.lngBorrowerNumberCnt " & _
"FROM tblLoanRelation RIGHT JOIN
(tblAcquisitionRelation RIGHT JOIN tblBookRelation ON " & _
"tblAcquisitionRelation.strISBN=tblBookRela
tion.strISBN) ON " & _
"tblLoanRelation.lngAcquisitionNumberCnt=tb
lAcquisitionRelation.lngAcquisitionNumberCnt WHERE " & _
"tblBookRelation.strAuthor LIKE '*" &
strBookSpec & "*' AND " & _
"(isnull(tblLoanRelation.dtmDateReserved)
AND isnull(tblLoanRelation.dtmDateBorrowed) OR " & _
"not isnull
(tblLoanRelation.dtmDateReserved) AND not isnull
(tblLoanRelation.dtmDateBorrowed) OR " & _
"isnull(tblLoanRelation.dtmDateReserved)
AND not isnull(tblLoanRelation.dtmDateBorrowed))"
Exit Sub
Else
rec.MoveNext
End If
Loop
MsgBox "No matches!"
Case Else
strBookSpec = Text2.Value
Set rec = dbshigham.OpenRecordset
("tblBookRelation")
Do Until rec.EOF
If InStr(1, rec(3), strBookSpec) Then
Me.fsubControl.Form.RecordSource = "Select
tblAcquisitionRelation.lngAcquisitionNumberCnt, " & _
"tblBookRelation.strISBN,
tblBookRelation.strTitle, tblBookRelation.strAuthor, " & _
"tblBookRelation.strCategory,
tblLoanRelation.dtmDateReserved,
tblLoanRelation.dtmDateBorrowed, " & _
"tblLoanRelation.chkReserve,
tblLoanRelation.chkBorrow,
tblLoanRelation.lngBorrowerNumberCnt " & _
"FROM tblLoanRelation RIGHT JOIN
(tblAcquisitionRelation RIGHT JOIN tblBookRelation ON " & _
"tblAcquisitionRelation.strISBN=tblBookRela
tion.strISBN) ON " & _
"tblLoanRelation.lngAcquisitionNumberCnt=tb
lAcquisitionRelation.lngAcquisitionNumberCnt WHERE " & _
"tblBookRelation.strCategory LIKE '*" &
strBookSpec & "*' AND " & _
"(isnull(tblLoanRelation.dtmDateReserved)
AND isnull(tblLoanRelation.dtmDateBorrowed) OR " & _
"not isnull
(tblLoanRelation.dtmDateReserved) AND not isnull
(tblLoanRelation.dtmDateBorrowed) OR " & _
"isnull(tblLoanRelation.dtmDateReserved)
AND not isnull(tblLoanRelation.dtmDateBorrowed))"
Exit Sub
Else
rec.MoveNext
End If
Loop
MsgBox "No matches!"
End Select

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
If Err.Number = 6 Then
MsgBox "Number does not exist!"
ElseIf Err.Number = 13 Then
MsgBox "Number required!"
Else
MsgBox Err.Number & " " & Err.Description
End If
Resume Exit_Command4_Click
End Sub

The application works fine if you carry out a search.
However, if you click either check boxes on the subform
(the sub form has fields lngAcquisitionNumberCnt,
strTitle, strAuthor, strCategory, dtmDateBorrowed,
chkReserve and chkBorrow) and click elsewhere on the GUI,
it freezes and a message box appears saying 'The field
tblLoanRelation.lngBorrowerNumberCnt cannot contain a null
value because the required property for this field is set
to true. Enter a value in this field'.

This message only appears when a check box is clicked,
even though there is no code attached to it. Any ideas
what could be causing this?

Thanks in advance, Martin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

update a subform with current data 2
underscore=line continuation? 2
dynaset recordset 2
The field 'tblLoanRelation.lngBorrowerNumberCnt' cannot contain a null value bec 1
The field 'tblLoanRelation.lngBorrowerNumberCnt' cannot 0

Top