> 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)
>
>
>
> "Ken Snell (MVP)" wrote:
>
>> 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.
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
http://www.accessmvp.com/KDSnell/>>
>>
>> "carriey" <carriey[ at ]discussions.microsoft.com> wrote in message
>> news:AB35D67C-4433-4D35-844A-29DA54A81335[ at ]microsoft.com...
>> > 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?
>> >
>> > "Ken Snell (MVP)" wrote:
>> >
>> >> 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
>> >>
>> >> --
>> >>
>> >> Ken Snell
>> >> <MS ACCESS MVP>
>> >>
http://www.accessmvp.com/KDSnell/>> >>
>> >>
>> >> "carriey" <carriey[ at ]discussions.microsoft.com> wrote in message
>> >> news:E52C3A94-84F5-41FE-9CC1-89D2B09664A1[ at ]microsoft.com...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>