|
|
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
|
|
"carriey" <carriey[ at ]discussions.microsoft.com> wrote in message
news:E8B5AB3C-00B0-4879-8656-BD6AB279EBBF[ at ]microsoft.com...
[Quoted Text] >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.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
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
"Dirk Goldgar" wrote:
[Quoted Text] > "carriey" <carriey[ at ]discussions.microsoft.com> wrote in message
> news:E8B5AB3C-00B0-4879-8656-BD6AB279EBBF[ at ]microsoft.com...
> >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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
|
|
carriey wrote:
[Quoted Text] > 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.)
|
|
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.
"Mike Painter" wrote:
[Quoted Text] > carriey wrote:
> > 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.)
>
>
>
|
|
(please see my comments and questions in-line)
"carriey" <carriey[ at ]discussions.microsoft.com> wrote in message
news:811B8D17-4E3B-4EA3-83C6-BB7D54D395B0[ at ]microsoft.com...
[Quoted Text] > 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.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
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 wrote:
[Quoted Text] > 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.
>
> "Mike Painter" wrote:
>
>> carriey wrote:
>>> 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.)
|
|
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" wrote:
[Quoted Text] > (please see my comments and questions in-line)
>
> "carriey" <carriey[ at ]discussions.microsoft.com> wrote in message
> news:811B8D17-4E3B-4EA3-83C6-BB7D54D395B0[ at ]microsoft.com...
> > 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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
|
|
"carriey" <carriey[ at ]discussions.microsoft.com> wrote in message
news:58FBD511-7F5A-49CF-93D1-DC85181C4C4B[ at ]microsoft.com...
[Quoted Text] > 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.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
|
[Quoted Text] > 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" wrote:
> "carriey" <carriey[ at ]discussions.microsoft.com> wrote in message
> news:58FBD511-7F5A-49CF-93D1-DC85181C4C4B[ at ]microsoft.com...
> > 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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
|
|
"carriey" <carriey[ at ]discussions.microsoft.com> wrote in message
news:A5C19C3A-2D8C-4891-A708-BB565FD466AF[ at ]microsoft.com...
[Quoted Text] >> 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.
That's all I meant by "corresponding to".
>> 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
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.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
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.
"Dirk Goldgar" wrote:
[Quoted Text] > "carriey" <carriey[ at ]discussions.microsoft.com> wrote in message
> news:A5C19C3A-2D8C-4891-A708-BB565FD466AF[ at ]microsoft.com...
> >> 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.
>
> That's all I meant by "corresponding to".
>
> >> 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
>
> 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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
|
|
|