OpenRecordset, AddNew won't add a record?

  • Thread starter Thread starter carriey
  • Start date Start date
C

carriey

I'm no good at code but through lots of researching have managed to come up
with this and obviously I've missed something because it isn't adding a
record - hopefully someone can tell me where I'm going wrong.

Where the error appears is at rs.TOD.Update and it tells me "You cannot add
or change a record because a related record is required in
Subtbl_Obligations_MAIN".

What I'm trying to accomplish here is from a click of a button on a subform
with a subform (within a Main Form), add the Record_ID and Oblig_ID to the
Junction table and then add the appropriate fields to my two other Subtables.

Can anyone out there help me? Thank you so much!


Private Sub cmd_sendto_SD_Click()

Dim db As DAO.Database
Dim rsTJ As DAO.Recordset
Dim rsTO As DAO.Recordset
Dim rsTOD As DAO.Recordset

'Open a recordset using a table

Set db = CurrentDb
Set rsTJ = db.OpenRecordset("TBL_JUNCTION", dbOpenDynaset)
Set rsTO = db.OpenRecordset("Subtbl_Obligations_MAIN", dbOpenDynaset)
Set rsTOD = db.OpenRecordset("Subtbl_Obligation_Deficiencies", dbOpenDynaset)

'Add a New Record to TBL_JUNCTION

With rsTJ
If Not rsTJ.EOF And Not rsTJ.BOF Then
Do While Not rsTJ.EOF
rsTJ.AddNew 'Add Record_ID to TBL_JUNCTION
'Set Fields
rsTJ!Record_ID = Me!RecordID 'Me object is active form internal
inspections
rsTJ.Bookmark = rsTJ.LastModified
Exit Do
Loop
End If
rsTJ.MoveNext
End With

With rsTO
If Not rsTO.EOF And Not rsTO.BOF Then
Do While Not rsTO.EOF
rsTO.AddNew 'Add Obligation Record
'Set Fields
rsTO!Oblig_Rcvd = Date 'Set today's date
rsTO!Oblig_Status = "Open"
rsTO!Obligation_Type = "Self-Declaration"
rsTO!Internal_Insp = True
rsTO!Oblig_Date =
Forms!Frm_MAIN_AB!Frm_Internal_Inspections.Form!IntInsp_Date
rsTO!Response_Due =
Forms!Frm_MAIN_AB!Frm_Internal_Inspections.Form!Response_Due
rsTO.Update
rsTO.Bookmark = rsTO.LastModified
Exit Do
Loop
End If

rsTO.MoveNext
End With

With rsTOD
If Not rsTOD.EOF And Not rsTOD.BOF Then
Do While Not rsTOD.EOF
rsTOD.AddNew 'Add Deficiency Record
'Set Fields
rsTOD!Deficiency =
Forms!Frm_MAIN_AB!Frm_Internal_Inspections!Subfrm_Internal_Insp_Deficiencies.Form!Deficiency
rsTOD!Deficiency_Comments =
Forms!Frm_MAIN_AB!Frm_Internal_Inspections!Subfrm_Internal_Insp_Deficiencies.Form!Deficiency_Comments
rsTOD.Update
rsTOD.Bookmark = rsTOD.LastModified
Exit Do
Loop
End If

rsTOD.MoveNext

End With

rsTJ.Close
rsTO.Close
rsTOD.Close

Set rsTJ = Nothing
Set rsTO = Nothing
Set rsTOD = Nothing
Set db = Nothing

End Sub
 
You need to "end" the adding of the new record with an .Update action to
actually save the new record.

rsTJ.AddNew 'Add Record_ID to TBL_JUNCTION
'Set Fields
rsTJ!Record_ID = Me!RecordID 'Me object is active form internal
inspections
rsTJ.Update ' Save the new record
rsTJ.Bookmark = rsTJ.LastModified
 
Thanks Ken. I put in the update and now I get the same error but it comes up
at
rsTJ.Update

rsTJ is my Junction Table for my many-to-many and only has Record_ID and
Oblig_ID in it. My thoughts are that I should somehow be adding the Oblig_ID
before rsTJ.Update instead of having the AddNew in rsTO.........would that be
right? Is there a way to do that?
 
You will need to add a value for both fields in the junction table. The
error message that you're getting apparently indicates that a NULL value for
Oblig_ID field is not valid because this field must contain a value that is
in the Subtbl_Obligations_MAIN table.
 
So, what I'm actually trying to do is create the Oblig_ID as it doesn't exist
yet. I think what my code is doing is saying that the Record_ID for the
Record I'm on should be added to the Junction table but AddNew won't create
the new Oblig_ID?

Is there a way to do that in rsTJ

rsTJ.AddNew
rsTJ!Record_ID = Me!RecordID
rsTJ!Oblig_ID = ????? (like AddNew)
 
You will need to add a record to the Subtbl_Obligations_MAIN table FIRST
before you add the new record to the junction table. That new record in the
Subtbl_Obligations_MAIN table must have the same value for Oblig_ID as the
value that you'll be entering for the new record in the junction table.

You can add the new record to the Subtbl_Obligations_MAIN table by a similar
code approach as you're now using for the junction table.
 
Back
Top