Append Problem

D

Denis

Good day,
A reply from Allen Browne to an earlier post from "Angi"
sent : 4/12/2005 11:14:16 PM on the subject: "records
aren't appending...don't see an error! " has helped me in
looking at a similar problem from a different angle.

However I am stuck on something. First, some info:

Tables: tblCV_Pers is (PK PersID) on the 'one'side of the
relationship to tblCV_PersKeywords (PK KeywordID; FK
PersID)

Forms: frmPersDetails is the Parent to sfrmCV_PersKeywords
and sfrmKeywordSelect which are both located on the same
page of a tab control (TabCtlDetails).

sfrmKeywordSelect strictly contains a listbox (lstKeywords
based on tblKeywords), which when double-clicked, will
(hopefully) populate the "[Keyword] field on
sfrmCV_PersKeywords.


Now, I have unsuccessfully tried different approaches and
I am now attempting the approach suggested by Allen and am
encountering difficulties. Here is the code I am using:

Private Sub lstKeywords_DblClick(Cancel As Integer)

Dim rs As DAO.Recordset

Set rs = Forms!frmPersDetails!
sfrmCV_PersKeywords.Form.RecordsetClone

rs.AddNew
rs!Keyword.Value = Me.lstKeywords.Value
rs.Update
Set rs = Nothing

End Sub

When I 'double-click' on lstKeywords, the item selected in
the listbox is NOT added to the [Keyword] field located on
sfrmCV_PersKeywords and I get the following error (at
which time the "rs.Update" line is highlighted:

Run-time error '3201' - "You cannot add or change record
because a related record is required in table 'tblCV_Pers'.

I have confirmed that the Link Child and Master Fields on
sfrmCV_PersKeywords are PersID.

Any help is truly appreciated.
Merçi!
Denis
 
S

SteveS

Denis said:
Good day,
A reply from Allen Browne to an earlier post from "Angi"
sent : 4/12/2005 11:14:16 PM on the subject: "records
aren't appending...don't see an error! " has helped me in
looking at a similar problem from a different angle.

However I am stuck on something. First, some info:

Tables: tblCV_Pers is (PK PersID) on the 'one'side of the
relationship to tblCV_PersKeywords (PK KeywordID; FK
PersID)

Forms: frmPersDetails is the Parent to sfrmCV_PersKeywords
and sfrmKeywordSelect which are both located on the same
page of a tab control (TabCtlDetails).

sfrmKeywordSelect strictly contains a listbox (lstKeywords
based on tblKeywords), which when double-clicked, will
(hopefully) populate the "[Keyword] field on
sfrmCV_PersKeywords.


Now, I have unsuccessfully tried different approaches and
I am now attempting the approach suggested by Allen and am
encountering difficulties. Here is the code I am using:

Private Sub lstKeywords_DblClick(Cancel As Integer)

Dim rs As DAO.Recordset

Set rs = Forms!frmPersDetails!
sfrmCV_PersKeywords.Form.RecordsetClone

rs.AddNew
rs!Keyword.Value = Me.lstKeywords.Value
rs.Update
Set rs = Nothing

End Sub

When I 'double-click' on lstKeywords, the item selected in
the listbox is NOT added to the [Keyword] field located on
sfrmCV_PersKeywords and I get the following error (at
which time the "rs.Update" line is highlighted:

Run-time error '3201' - "You cannot add or change record
because a related record is required in table 'tblCV_Pers'.

I have confirmed that the Link Child and Master Fields on
sfrmCV_PersKeywords are PersID.

Any help is truly appreciated.
Merçi!
Denis
Click to expand...

Hi Denis,

Welcome to "Referential Integrity"! What the error message is telling
you is that you are trying to create a record in a table that is on the
"many" side ("tblCV_PersKeywords") table WITHOUT filling in the FK field
that links the new record to the "one" side ("tblCV_Pers") table. The
linking field, in this case "PersID", *cannot* be NULL.

When you create a new record, the fields that MUST be filled in is the
Primary Key field (or fields if it is a compound PK) and any FK's in the
table. And, of course, any required field values in the table.

Try this:
'********** begin code ************
Private Sub lstKeywords_DblClick(Cancel As Integer)

Dim rs As DAO.Recordset

Set rs = Forms!frmPersDetails!sfrmCV_PersKeywords.Form.RecordsetClone

rs.AddNew
' I think this will work
rs.PersID = Me.Parent.[PersID]
' if if doesn't work, try
' rs.PersID = Forms![frmPersDetails].[PersID]
' value is default property
rs!Keyword = Me.lstKeywords
rs.Update

' need to close first!!!
rs.Close
Set rs = Nothing

End Sub
'************ end code ***************

If [PersID] is not name of the control that that is bound to the PersID
field, you will need to change it to the correct name.
 
D

Denis

Hi Steve,
Well you have certainly helped me out! In fact, I will be
revisiting another form for similar functionality.... it
is certainly better than playing around with Append
Queries and all associated actions!

Thanks again!
Denis
 
S

SteveS

Denis said:
Hi Steve,
Well you have certainly helped me out! In fact, I will be
revisiting another form for similar functionality.... it
is certainly better than playing around with Append
Queries and all associated actions!

Thanks again!
Denis
Click to expand...
You're welcome.
 
Top