The field 'tblLoanRelation.lngBorrowerNumberCnt' cannot contain a null value bec

P

placek

Hi there,

My library database consists of the following 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).

It also 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.

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 go through this
process [for either Reserve or Borrow] and then try to
carry out a new search, the program freezes and the
following error message appears:

"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."

What i don't understand is that the error message even
appears when tblLoanRelation.lngBorrowerNumberCnt is not
null. Another confusing aspect is that the error handler
does not trap it. The code for the subform is as follows:

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

It seems the program is creating a new record even though
this has not been specified in the code. Can anyone please
tell me how to stop it doing this?
Thanks
Martin
 
A

Alex Dybenko

Suggest to use field names instead of numbers to make sure use use right
fields:

recLoanRelation("lngBorrowerNumberCnt") = lngBorrowerNumber


--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com



placek said:
Hi there,

My library database consists of the following 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).

It also 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.

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 go through this
process [for either Reserve or Borrow] and then try to
carry out a new search, the program freezes and the
following error message appears:

"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."

What i don't understand is that the error message even
appears when tblLoanRelation.lngBorrowerNumberCnt is not
null. Another confusing aspect is that the error handler
does not trap it. The code for the subform is as follows:

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(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

It seems the program is creating a new record even though
this has not been specified in the code. Can anyone please
tell me how to stop it doing this?
Thanks
Martin
Click to expand...
 

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

The field 'tblLoanRelation.lngBorrowerNumberCnt' cannot 0
update method creates phantom record 3
The field X cannot contain a null value ...... 0
dynaset recordset 2
troubl using a null field in strwhere 2
Automatically incrementing a value in a field 3
Using Range and Offset to determine value of a field 3
Assigning the value in recordset field to a variable 3

Top