Add New only takes first record

[carriey]

I am trying to add from one subtable to another subtable where a checkbox is
True. This is part of a larger process where a record is added to the table
and then to the subtable. It works except that it only takes the first
record that is checked, not the other records.

I tried this with the rsTOD.MoveNext inside the Do While and Outside the Do
While and either way it only takes the first record checked. Can anyone see
where this might be busted?

Thank you in advance!!!!


With rsTOD
If
Forms!Frm_MAIN_AB!Frm_Internal_Inspections!Subfrm_Internal_Insp_Deficiencies.Form!Self_Dec = True Then
If Not rsTOD.EOF And Not rsTOD.BOF Then
Do While Not rsTOD.EOF
rsTOD.AddNew 'Add Deficiency Record
'Set Fields
rsTOD!Oblig_ID = rsTO!Oblig_ID
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
rsTOD.MoveNext
Exit Do
Loop
End If
End If

End With

rsTO.MoveNext

End With

rsTO.Close
rsTOD.Close

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

MsgBox "The Self-Declaration has been created. Thank you."

End Sub

 

[Dirk Goldgar]

I am trying to add from one subtable to another subtable where a checkbox
is
True. This is part of a larger process where a record is added to the
table
and then to the subtable. It works except that it only takes the first
record that is checked, not the other records.

I tried this with the rsTOD.MoveNext inside the Do While and Outside the
Do
While and either way it only takes the first record checked. Can anyone
see
where this might be busted?

Thank you in advance!!!!


With rsTOD
If
Forms!Frm_MAIN_AB!Frm_Internal_Inspections!Subfrm_Internal_Insp_Deficiencies.Form!Self_Dec
= True Then
If Not rsTOD.EOF And Not rsTOD.BOF Then
Do While Not rsTOD.EOF
rsTOD.AddNew 'Add Deficiency Record
'Set Fields
rsTOD!Oblig_ID = rsTO!Oblig_ID
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
rsTOD.MoveNext
Exit Do
Loop
End If
End If

End With

rsTO.MoveNext

End With

rsTO.Close
rsTOD.Close

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

MsgBox "The Self-Declaration has been created. Thank you."

End Sub

This is a bit confusing, because

(a) I'm not at all clear on the relationships among the two recordsets, rsTO
and rsTOD, and the form itself,

(b) You have an unconditional Exit Do statement in your loop, so you will
always exit that loop after the first iteration,

(c) If it were not for the Exit Do statement, you seem to be looping until
EOF on rsTOD, but rsTOD is the recordset you're adding to, so you would
never reach EOF, and

(d) it doesn't look like you posted all the relevant code, so your
intentions are also unclear.

I suspect that you've been trying various things to fix your problem, and
now your code is way off track. I suggest you post a description of the
form and tables involved, and what you are trying to achieve, and then maybe
we can work out the best way to do it.

 

[carriey]

Thanks for your resopnse although I'm a little disheartened to learn I'm way
off track. I did get a little guidance here before but could never get it
working properly and you're right, I have tried a lot of stuff.

There is Tbl_MAIN (pk = Record_ID) for Frm_MAIN_AB
Subtbl_Internal_Inspections (pk = IntInsp) for Frm_Internal_Inspections
(Record_ID is the parent, one-to-many)
Subtbl_IntInsp_Deficiencies (pk = IntDefID) for
Subfrm_Internal_Insp_Deficiencies (IntInsp is the parent, one-to-many)

Subtbl_Obligations_MAIN (pk = Oblig_ID) for Frm_Obligations_MAIN_AB
Tbl_Junction (Oblig_ID and Record_ID for a many-to-many)
Subtbl_Obligation_Deficiencies (pk = ObligDefID) for Subfrm_Oblig_Deficiencies

Both Frm_Internal_Inspections and Frm_Obligations_MAIN_AB are subforms of
Frm_MAIN_AB.

When the user enters the Internal Inspection, they are to click the button
to create the Obligation (new Oblig_ID) and add some fields to the
Subtbl_Obligations_MAIN and then where the check box Self_Dec = true in
Subtbl_IntInsp_Deficiencies then a deficiency record should also be added to
Subtbl_Obligation_Deficiencies.

There might be 20 deficiencies on the internal inspection but only 5 are
required to be self-declared as an obligation.

Where I kept running into trouble was with the Tbl_Junction. I originally
had my code to the tables instead of queries but no matter what I did, it
would say that there was no record in the other table. When I finally had
what I thought was the brilliant idea to change it to the queries, it worked!
Obviously not 100% though because it only takes the first deficiency.

Here's the whole code, your help is greatly appreciated. My two users have
been waiting on me for weeks now to get this working so thanks for your time!
--------------------------------------------------------
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
Dim Oblig_ID As Integer

'Open a recordset using a table

Set db = CurrentDb
Set rsTO = db.OpenRecordset("Qry_Obligations_MAIN", dbOpenDynaset)
Set rsTOD = db.OpenRecordset("Subtbl_Obligation_Deficiencies", dbOpenDynaset)

With rsTO
If Not rsTO.EOF And Not rsTO.BOF Then
Do While Not rsTO.EOF
rsTO.AddNew 'Add Obligation Record
'Set Fields
rsTO!Record_ID = Me!RecordID
rsTO!Oblig_Rcvd = Date 'Set today's date
rsTO!Oblig_Status = "Open"
rsTO!Obligation_Type = "Self-Declaration"
rsTO!Obligation_Subtype = "7"
rsTO!Oblig_Subtype2 = "70"
rsTO!Company = "Penn West"
rsTO!Coordinator = "12"
rsTO!Govt_Agency = "ERCB"
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!Locations = "1"
rsTO!Employee = Forms!Frm_MAIN_AB!Frm_Internal_Inspections.Form!Employee
rsTO.Update 'Save the New Record
rsTO.Bookmark = rsTO.LastModified
Exit Do
Loop
End If

With rsTOD
If
Forms!Frm_MAIN_AB!Frm_Internal_Inspections!Subfrm_Internal_Insp_Deficiencies.Form!Self_Dec = True Then
If Not rsTOD.EOF And Not rsTOD.BOF Then
Do While Not rsTOD.EOF
rsTOD.AddNew 'Add Deficiency Record
'Set Fields
rsTOD!Oblig_ID = rsTO!Oblig_ID
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
rsTOD.MoveNext
Exit Do
Loop
End If
End If

End With

rsTO.MoveNext

End With

rsTO.Close
rsTOD.Close

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

MsgBox "The Self-Declaration has been created. Thank you."

End Sub

 

[Mike Painter]

I am trying to add from one subtable to another subtable where a
checkbox is
True. This is part of a larger process where a record is added to
the table
and then to the subtable. It works except that it only takes the
first
record that is checked, not the other records.

<snip>
While there are times when the same information may be needed in two related
tables, this does not seem to be one of them.
I would also suggest that you look at doing this, if needbe, with an append
query rather than with code.
It will be neater and easier to maintain and also faster if that is an
issue.
<Tarzan Accent>Code bad, Query good </Tarzan accent>
should be the mantra in most cases.

(In the first major application I did in Access I went from 20 seconds or
more looping through records, then opening a form to less than a second
using queries. I also found out that there is a use for Cartesian sets.)

 

[carriey]

Thanks for your response Mike, and I guess I will look at append queries but
am not sure how to do that either with the tables and subtables so, I may
just be trading one problem for another.

I'm not sure about your comment when you say "While there are times when the
same information may be needed in two related tables, this does not seem to
be one of them."

If you what you mean is that it should all be tracked in one table that
really won't work. It is two totally seperate job functions and only certain
pieces of data are related at all. There is a lot of other data stored in
the tables besides what I have mentioned and it doesn't happen all the time
that the two intertwine, and when they do, we need them seperate.

If there is just a way to change my code to work, I would obviously prefer
that.

 

[Dirk Goldgar]

(please see my comments and questions in-line)

Thanks for your resopnse although I'm a little disheartened to learn I'm
way
off track. I did get a little guidance here before but could never get it
working properly and you're right, I have tried a lot of stuff.

There is Tbl_MAIN (pk = Record_ID) for Frm_MAIN_AB
Subtbl_Internal_Inspections (pk = IntInsp) for Frm_Internal_Inspections
(Record_ID is the parent, one-to-many)
Subtbl_IntInsp_Deficiencies (pk = IntDefID) for
Subfrm_Internal_Insp_Deficiencies (IntInsp is the parent, one-to-many)

Subtbl_Obligations_MAIN (pk = Oblig_ID) for Frm_Obligations_MAIN_AB
Tbl_Junction (Oblig_ID and Record_ID for a many-to-many)
Subtbl_Obligation_Deficiencies (pk = ObligDefID) for
Subfrm_Oblig_Deficiencies

Let me restate some things to see if I understand. Where I have questions,
could you answer them, please?

Tbl_MAIN defines ... something ... for which there are inspections and
obligations. The primary key of this table is Record_ID.

Tbl_MAIN has a child table named Subtbl_Internal_Inspections, with primary
key IntInsp. The field Record_ID in this table is the foreign key to
Tbl_MAIN.

Subtbl_Internal_Inspections has a child table named
Subtbl_IntInsp_Deficiencies, with primary key IntDefID. The field IntInsp
in this table is the foreign key to Subtbl_Internal_Inspections.

There is another table, Subtbl_Obligations_MAIN, with primary key Oblig_ID.
You say that Tbl_Junction establishes a many-to-many relationship; is that
relationship between Subtbl_Obligations_MAIN and Tbl_Main (as is implied by
the foreign keys Oblig_ID and Record_ID that you mention)? Or have I
misunderstood? You may have meant that Tbl_Junction joins
Subtbl_Obligations_MAIN and Subtbl_Obligation_Deficiencies, but in that case
it doesn't make sense that Tbl_Junction would have foreign key field
Record_ID.

There is a table, Subtbl_Obligation_Deficiencies, with primary key
ObligDefID. Is this table a child table of Subtbl_Obligations_MAIN, with a
foreign key field Oblig_ID? Or is it in a different, many-to-many
relationship, as I was wondering above?

Could you explain in more detail what sorts of entities and relationships
these tables are meant to represent?

Both Frm_Internal_Inspections and Frm_Obligations_MAIN_AB are subforms of
Frm_MAIN_AB.

Is Frm_Obligations_MAIN_AB based directly on Subtbl_Obligations_MAIN, or is
it based on Tbl_Junction? If the former, I'd have thought the relationship
between Tbl_MAIN and Subtbl_Obligations_MAIN to be one-to-many, rather than
many-to-many.

When the user enters the Internal Inspection,

On Frm_Internal_Inspections, the subform on Frm_MAIN_AB?

they are to click the button

Is that button on the main form, or on the subform?

to create the Obligation (new Oblig_ID) and add some fields to the
Subtbl_Obligations_MAIN

All that is to be done by the button code, correct? Is this supposed to
both create a new record in Subtbl_Obligations_MAIN, and also create a
record in Tbl_Junction to link that record to the main record? This is
where I am uncertain of the relationships involved, and can't see my way
clear to the correct procedure for your button.

and then where the check box Self_Dec = true in
Subtbl_IntInsp_Deficiencies then a deficiency record should also be added
to
Subtbl_Obligation_Deficiencies.

By "check box", I assume you mean that Self_Dec is a Yes/No (Boolean) field.
As I (maybe) understand what you've written, you need to select from
Subtbl_IntInsp_Deficiencies all those records which (a) are related to any
record in Subtbl_Internal_Inspections which is related to the current record
on the main form, and (b) have their Self_Dec field = True, and use them to
create records in Subtbl_Obligation_Deficiencies.

There might be 20 deficiencies on the internal inspection but only 5 are
required to be self-declared as an obligation.

Where I kept running into trouble was with the Tbl_Junction. I originally
had my code to the tables instead of queries but no matter what I did, it
would say that there was no record in the other table. When I finally had
what I thought was the brilliant idea to change it to the queries, it
worked!
Obviously not 100% though because it only takes the first deficiency.

Here's the whole code, your help is greatly appreciated. My two users
have
been waiting on me for weeks now to get this working so thanks for your
time!
--------------------------------------------------------
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
Dim Oblig_ID As Integer

'Open a recordset using a table

Set db = CurrentDb
Set rsTO = db.OpenRecordset("Qry_Obligations_MAIN", dbOpenDynaset)
Set rsTOD = db.OpenRecordset("Subtbl_Obligation_Deficiencies",
dbOpenDynaset)

With rsTO
If Not rsTO.EOF And Not rsTO.BOF Then
Do While Not rsTO.EOF
rsTO.AddNew 'Add Obligation Record
'Set Fields
rsTO!Record_ID = Me!RecordID
rsTO!Oblig_Rcvd = Date 'Set today's date
rsTO!Oblig_Status = "Open"
rsTO!Obligation_Type = "Self-Declaration"
rsTO!Obligation_Subtype = "7"
rsTO!Oblig_Subtype2 = "70"
rsTO!Company = "Penn West"
rsTO!Coordinator = "12"
rsTO!Govt_Agency = "ERCB"
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!Locations = "1"
rsTO!Employee =
Forms!Frm_MAIN_AB!Frm_Internal_Inspections.Form!Employee
rsTO.Update 'Save the New Record
rsTO.Bookmark = rsTO.LastModified
Exit Do
Loop
End If

With rsTOD
If
Forms!Frm_MAIN_AB!Frm_Internal_Inspections!Subfrm_Internal_Insp_Deficiencies.Form!Self_Dec
= True Then
If Not rsTOD.EOF And Not rsTOD.BOF Then
Do While Not rsTOD.EOF
rsTOD.AddNew 'Add Deficiency Record
'Set Fields
rsTOD!Oblig_ID = rsTO!Oblig_ID
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
rsTOD.MoveNext
Exit Do
Loop
End If
End If

End With

rsTO.MoveNext

End With

rsTO.Close
rsTOD.Close

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

MsgBox "The Self-Declaration has been created. Thank you."

End Sub

I don't see why you have Do While loops in two places which exit (using Exit
Do) after the first iteration of the loop. I think this must represent a
misunderstanding on your part, but there's no point in trying to address
that until I understand what it is you really need to be doing.

I apologize for all the questions, but the logic here is going to be
crucial.

 

[Mike Painter]

I'm not sure what you mean by "subtables" but am guessing that they are
related to some master table.
Disign an append query with the query builder that will append all the
records with the checkbox = true to whereever they need to go.
If the query needs some other criteria that is found on the form use
Forms!YourForm!Your fieldname in the criteria for the field and then run
that query from a button. There is a wizard that will walk you through
building the button.
If the two tables have different fields for the most part that do NOT
contain similar information then what you want may make sense. Jobs might
have planning, logistics, and construction tables for example.
However normally these would all be related to a Jobs table and that would
contain information that might be used in some common manner.

One key rule in this type of an application is that data only appear once.
It can't always happen but there should be a good reason why not.

 

[carriey]

Dirk thank you so much for taking the time - I had feared help was lost in
the other posts!

To answer your questions:

1) Tbl_MAIN defines locations and PK is Record_ID
2) Yes, Tbl_MAIN has a child table named Subtbl_Internal_Inspections and you
are correct on PK and FK
3) Yes, Subtbl_Internal_Inspections has a child table
Subtbl_IntInsp_Deficiencies and you are correct on the PK and FK
4) Subtbl_Obligations_MAIN has a pk of Oblig_ID and is a many-to-many with
Tbl_MAIn (per the Tbl_Junction)
5) Yes, Subtbl_Obligation_Deficiencies is the child of
Subtbl_Obligations_MAIN and PK is ObligDefID and fk is Oblig_ID - this is a
one-to-many

MORE DETAIL:

The Tbl_MAIN locations are actually wells and facilities (for an oil & gas
company).

Each location is required to conduct a certain number of Internal
Inspections per year and a person in head office records those inspections,
with deficiencies and follows up for closure.

Sometimes there are certain deficiencies that need to be self-declared to a
govt agency however, it is never all the deficiencies (becasue many are based
on internal policies).

A different person in head office does the self-declarations but their job
encompasses many other types of obligations of which Self-Declarations are
only one type. Additionally, they will self-declare for other reasons, and
for other departments that is totally unrelated to the Internal Inspection
process.

The reason that Subtbl_Obligations_MAIN is a many-to-many relationship is
that often they will self-declare one or more deficiencies for multiple
locations (although in the case of Internal Inspections it will strictly be
one location with many deficiencies). In order to capture those types, the
user has a totally seperate form where they can enter many locations

All my forms are actually based off of queries with there being a
Frm_MAIN_AB and then a tab for each Subform (subtable data) and in certain
instances there is a subform with in that (as is the case with deficiencies).
Both Internal Inspections and Obligations have a subform.

The button to send an Internal Inspection to the Self-Declaration Form is on
the Internal Inspections Form (not the Frm_MAIN_AB) and the code is all on
the button.

Yes, it is supposed to create a record in the Tbl_Junction and the
Subtbl_Obligations_MAIN which works except that it adds the first deficiency
only.

Yes the check box is a yes/no. There is a bit of data on the Internal
Inspections form that needs to move to the Obligations Form but mostly it is
hard coded data that will never change and is specific to the Obligation, not
the Internal Inspection. Some deficiency data needs to go from the
Subtbl_IntInsp_Deficiencies to the Subtbl_Obligation_Deficiencies based on
whether or not the user checked Self Dec Required as True.

I hope this answers all your questions and I cannot thank you enough for
your assistance!

 

[Dirk Goldgar]

Dirk thank you so much for taking the time - I had feared help was lost in
the other posts!

To answer your questions:

1) Tbl_MAIN defines locations and PK is Record_ID
2) Yes, Tbl_MAIN has a child table named Subtbl_Internal_Inspections and
you
are correct on PK and FK
3) Yes, Subtbl_Internal_Inspections has a child table
Subtbl_IntInsp_Deficiencies and you are correct on the PK and FK
4) Subtbl_Obligations_MAIN has a pk of Oblig_ID and is a many-to-many with
Tbl_MAIn (per the Tbl_Junction)
5) Yes, Subtbl_Obligation_Deficiencies is the child of
Subtbl_Obligations_MAIN and PK is ObligDefID and fk is Oblig_ID - this is
a
one-to-many

MORE DETAIL:

The Tbl_MAIN locations are actually wells and facilities (for an oil & gas
company).

Each location is required to conduct a certain number of Internal
Inspections per year and a person in head office records those
inspections,
with deficiencies and follows up for closure.

Sometimes there are certain deficiencies that need to be self-declared to
a
govt agency however, it is never all the deficiencies (becasue many are
based
on internal policies).

A different person in head office does the self-declarations but their job
encompasses many other types of obligations of which Self-Declarations are
only one type. Additionally, they will self-declare for other reasons,
and
for other departments that is totally unrelated to the Internal Inspection
process.

The reason that Subtbl_Obligations_MAIN is a many-to-many relationship is
that often they will self-declare one or more deficiencies for multiple
locations (although in the case of Internal Inspections it will strictly
be
one location with many deficiencies). In order to capture those types,
the
user has a totally seperate form where they can enter many locations

All my forms are actually based off of queries with there being a
Frm_MAIN_AB and then a tab for each Subform (subtable data) and in certain
instances there is a subform with in that (as is the case with
deficiencies).
Both Internal Inspections and Obligations have a subform.

The button to send an Internal Inspection to the Self-Declaration Form is
on
the Internal Inspections Form (not the Frm_MAIN_AB) and the code is all on
the button.

Yes, it is supposed to create a record in the Tbl_Junction and the
Subtbl_Obligations_MAIN which works except that it adds the first
deficiency
only.

Yes the check box is a yes/no. There is a bit of data on the Internal
Inspections form that needs to move to the Obligations Form but mostly it
is
hard coded data that will never change and is specific to the Obligation,
not
the Internal Inspection. Some deficiency data needs to go from the
Subtbl_IntInsp_Deficiencies to the Subtbl_Obligation_Deficiencies based on
whether or not the user checked Self Dec Required as True.

I hope this answers all your questions and I cannot thank you enough for
your assistance!

You're welcome, but alas I still have some questions. Is your button (on
the Internal Inspections form) supposed to do all of the following:

1. Create a record in Subtbl_Obligations_MAIN corresponding to the current
Subtbl_Internal_Inspections record that is displayed on the Internal
Inspections subform (where the button is)

2. Create a record in Tbl_Junction linking that newly created record to the
current location (Record_ID) as displayed on the main form

3. For every deficiency record in Subtbl_IntInsp_Deficiencies that
corresponds to the current Subtbl_Internal_Inspections record, *and* which
has its Self_Dec field = True, create a corresponding record in
Subtbl_Obligation_Deficiencies for the Obligation record created in step (1)

?

Is that a correct statement of what needs to be done? My understanding is
admittedly hazy.

 

[carriey]

1. Create a record in Subtbl_Obligations_MAIN corresponding to the current

Subtbl_Internal_Inspections record that is displayed on the Internal
Inspections subform (where the button is)

It is supposed to create a new record in Subtbl_Obligations_MAIN but I'm not
sure about the corresponding part? Both Subtbl_Obligations_MAIN and
Subtbl_Internal_Inspections are linked to the Record_ID (so the location) and
some of the info from Subtbl_Internal_Inspections needs to go into
Subtbl_Obligations_MAIN and that part is working.

2. Create a record in Tbl_Junction linking that newly created record to the
current location (Record_ID) as displayed on the main form
Yes!

3. For every deficiency record in Subtbl_IntInsp_Deficiencies that
corresponds to the current Subtbl_Internal_Inspections record, *and* which
has its Self_Dec field = True, create a corresponding record in
Subtbl_Obligation_Deficiencies for the Obligation record created in step (1)

Yes!

Thanks again - hopefully I answered correctly on # 1

 

[Dirk Goldgar]

It is supposed to create a new record in Subtbl_Obligations_MAIN but I'm
not
sure about the corresponding part? Both Subtbl_Obligations_MAIN and
Subtbl_Internal_Inspections are linked to the Record_ID (so the location)
and
some of the info from Subtbl_Internal_Inspections needs to go into
Subtbl_Obligations_MAIN and that part is working.

That's all I meant by "corresponding to".

Yes!

Thanks again - hopefully I answered correctly on # 1

Okay, then, if I've understood correctly you're going to want something like
this:

'----- start of code for command button -----
Private Sub cmd_sendto_SD_Click()

Dim db As DAO.Database
Dim rsObligations As DAO.Recordset

Dim lngObligID As Long
Dim lngRecordID As Long

' Save the current Record_ID in a variable for convenience.
lngRecordID = Me.RecordID

' Get a database object to work through.
Set db = CurrentDb

' Open an empty recordset on Subtbl_Obligations_MAIN
' add a record, and get the resulting Oblig_ID.

Set rsObligations = db.OpenRecordset( _
"SELECT * FROM Subtbl_Obligations_MAIN WHERE False")

With rsObligations
.AddNew 'Add Obligation Record
'Set Fields
!Record_ID = lngRecordID
!Oblig_Rcvd = Date 'Set today's date
!Oblig_Status = "Open"
!Obligation_Type = "Self-Declaration"
!Obligation_Subtype = "7"
!Oblig_Subtype2 = "70"
!Company = "Penn West"
!Coordinator = "12"
!Govt_Agency = "ERCB"
!Internal_Insp = True
!Oblig_Date = Me.IntInsp_Date
!Response_Due = Me.Response_Due
!Locations = "1"
!Employee = Me.Employee
.Update 'Save the New Record
' Move to the newly-added record and get the ID.
.Bookmark = .LastModified
lngObligID = !Oblig_ID
' Close the recordset; we're done with it.
.Close
End With

' Insert a record in Tbl_Junction to link the new Obligation record
' to the current location (Record_ID).

db.Execute _
"INSERT INTO Tbl_Junction(Oblig_ID, Record_ID) VALUES(" & _
lngObligID & ", " & lngRecordID & ")", _
dbFailOnError

' Create a record in Subtbl_Obligation_Deficiencies for every
' self-declared deficiency of the current Internal Inspection.

db.Execute _
"INSERT INTO Subtbl_Obligation_Deficiencies " & _
"(Oblig_ID, Deficiency, Deficiency_Comments) " & _
"SELECT " & lngObligID & " As Oblig_ID & _
", Deficiency, Deficiency_Comments " & _
"FROM Subtbl_IntInsp_Deficiencies " & _
"WHERE IntInsp = " & Me.IntInsp & _
" AND Self_Dec = True", _
dbFailOnError

' Requery the Obligations subform (of our parent form)
' to show the new obligation.
' NOTE: May have to correct the name of the subform.
Me.Parent!Frm_Obligations_MAIN_AB.Requery

MsgBox "The Self-Declaration has been created. Thank you."

End Sub

'----- end of code for command button -----

Be sure you make a backup copy of your database before trying this, as I may
have completely misunderstood.

 

[carriey]

Thank you! Thank you! Thank you!!!!!!!! Just had to change a couple names
and it works great. I really appreciate all the time you took to help me -
now that I see how it works properly, I realize I was never going to get it
going on the path I was on.