E
emailmeeric
This is complicated so I hope to be able to explain properly. I have
searched the groups and have not found this question so here it goes.
tblCR ---< tblDeliv ---< tblDelivSched
tblDeliv and tblDelivSched have enforced referential integrity with
cascade delete (but not update).
I have a table tblCR (Change Request) that has a one to many
relationship with tblDeliv (Deliverables). Deliverables are what is
required to implement the change. tblDeliv has a one to many
relationship with tblDelivSched (Deliverable Process Schedules) that
contain the step by step process to implement the deliverable.
The CR form has a Deliverables subform (record source tblDeliv) linked
to the CR (Master/Child =lngCRID) and the Deliverables subform has a
Schedule subform (record source tblDeliSched) linked to it (Master/
Child = lngDelivID)
Typing into a new record on the Deliverable subform will of course
autofill lngCRID into the foreign key (lngCRID) of tblDeliv. This is
also the case when adding a Schedule record using the subform within
the subform. This autofill feature seems to be built into Access 2003
when using a linked subform. This feature is convenient for using this
tree'd form/subform/subform as you just type and Access does the
linking for you based upon the master/child relationship.
I needed a way to quickly add action items in a meeting and have it
appear seamless so I created an additional subform (Action) that was
based upon a query where tblDeliv and tblDelivSched are already
related within the query. An Action is simply a Deliverable where the
field ysnAction is checked(-1). This special type of deliverable is
filtered out of most schedules and is simply used for meeting actions
items or other actions outside of the normal deliverable process.
The intent of this Action subform is to directly show only the
Schedule line items without the intermediary tblDeliv records and
without having to click the tree structure "plus" sign.
When a value was typed into a new record line in the Action subform I
needed the Action subform to check whether a an Action Deliverable
exists (ysnAction = -1) and if so add the schedule record against it
or if not create a new deliverable, set it as a action, link it to the
parent CR, and then create the schedule record and link it to the
deliverable.
So what I did was this in the Action subform:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim DID As Long
'qlkpDelivID_Action is a lookup query that returns all existing
Deliverables set as Actions and their related CRID
DID = Nz(DLookup("[lngDelivID]", "qlkpDelivID_Action", "[lngCRID]
= " & Forms!frmCR!CRID), 0)
If DID = 0 Then
'Create a new Deliverable and set ysnAction to -1
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblDeliv ( lngCRID, strCategory,
ysnAction, strTitle ) " & _
"SELECT [Forms]![frmCR]![CRID] AS CR, 'Action' AS A, -1 AS
ysn, 'Action Items' AS AI;"
DoCmd.SetWarnings True
DID = Nz(DLookup("[lngDelivID]", "qlkpDelivID_Action",
"[lngCRID] = " & Forms!frmCR!CRID), 0)
Me!txtDelivID = DID
Me!txtCRID = Forms![frmCR]![CRID]
Else
Me!txtDelivID = DID
Me!txtCRID = Forms![frmCR]![CRID]
End If
End Sub
Please note that the CR form is older and that is why the fields
haven't been renamed with the proper naming conventions.
Also remember that the Action subform is based upon a query that
contains and relates (INNER JOIN) the tblDeliv and tblDelivSched based
upon lngDelivID. tblDeliv is required as it contains the CRID
foreign key for the master/child relationship for the subform.
The query SQL is here in case it will help:
SELECT tblDeliv.lngCRID, tblDelivSched.lngDelivID,
tblDelivSched.strTitle, tblDelivSched.lngOwnerID,
tblDelivSched.dtmPStart, tblDelivSched.dtmPFinish,
tblDelivSched.dtmAStart, tblDelivSched.dtmAFinish,
tblDelivSched.dtmPush, tblDelivSched.strComments,
tblDelivSched.lngSlipDays, tblDelivSched.lngSlipCnt,
tblDeliv.ysnAction
FROM tblDeliv INNER JOIN tblDelivSched ON tblDeliv.lngDelivID =
tblDelivSched.lngDelivID
WHERE (((tblDeliv.lngCRID)=[forms]![frmCR]![CRID]) AND
((tblDelivSched.dtmAFinish) Is Null) AND ((tblDeliv.ysnAction)=-1))
ORDER BY tblDelivSched.dtmPFinish;
What happened was I recently restructured the Deliverable tables and
also renamed the fields with proper naming conventions. The
Before_Insert code above used to work perfectly. After I went through
the application, this code failed. It took me hours of troubleshooting
to determine why but I did.
If you open the query the subform is based upon and type a number into
tblDelivSched.lngDelivID it will work properly. But when you related
the Action subform to the CR form using Master/Child lngCRID what it
would do is this:
Autofills foreign key lngCRID in the tblDeliv table.
This autofill automatically creates a new record in the tblDeliv
table.
Then when you try to fill the foreign key lngDelivID in the
tblDelivSched table with the appropriate value, it fails because it
the lngDelivID field in the tblDeliv table contains the value of the
new record just created by the autofill.
Is there a way to prevent this autofill from happening?
The really strange thing is that the code worked prior to the fields
rename. I spent many hours troubleshooting and I am confident that it
works the way I've described. What is really frustrating is I don't
know whether the old code wasn't supposed to work that way or the new
code is. I've deleted and re-created the queries and forms to see
whether some hidden "corruption" or internal gremlins were causing the
error. But now that I understand what is happening I am more surprised
that the old code worked perfectly. I have no idea why it would. But I
really need to know whether that "autofill" can be superseded or the
timing of the update can be changed so I am able to "jump ahead" of
the autofill to do what i need.
I have already implemented a workaround where the query uses the the
criteria [Forms]![frmCR]![CRID] to filter lngCRID so I do not need to
use the master/child link feature of the subform. But this is not an
ideal solution.
I hope my question is coherent and I want to thank you in advance for
reading this long post. I also want to thank all you MVP's for all the
invaluable knowledge you've given me and all of us over the years.
Best regards, Eric
searched the groups and have not found this question so here it goes.
tblCR ---< tblDeliv ---< tblDelivSched
tblDeliv and tblDelivSched have enforced referential integrity with
cascade delete (but not update).
I have a table tblCR (Change Request) that has a one to many
relationship with tblDeliv (Deliverables). Deliverables are what is
required to implement the change. tblDeliv has a one to many
relationship with tblDelivSched (Deliverable Process Schedules) that
contain the step by step process to implement the deliverable.
The CR form has a Deliverables subform (record source tblDeliv) linked
to the CR (Master/Child =lngCRID) and the Deliverables subform has a
Schedule subform (record source tblDeliSched) linked to it (Master/
Child = lngDelivID)
Typing into a new record on the Deliverable subform will of course
autofill lngCRID into the foreign key (lngCRID) of tblDeliv. This is
also the case when adding a Schedule record using the subform within
the subform. This autofill feature seems to be built into Access 2003
when using a linked subform. This feature is convenient for using this
tree'd form/subform/subform as you just type and Access does the
linking for you based upon the master/child relationship.
I needed a way to quickly add action items in a meeting and have it
appear seamless so I created an additional subform (Action) that was
based upon a query where tblDeliv and tblDelivSched are already
related within the query. An Action is simply a Deliverable where the
field ysnAction is checked(-1). This special type of deliverable is
filtered out of most schedules and is simply used for meeting actions
items or other actions outside of the normal deliverable process.
The intent of this Action subform is to directly show only the
Schedule line items without the intermediary tblDeliv records and
without having to click the tree structure "plus" sign.
When a value was typed into a new record line in the Action subform I
needed the Action subform to check whether a an Action Deliverable
exists (ysnAction = -1) and if so add the schedule record against it
or if not create a new deliverable, set it as a action, link it to the
parent CR, and then create the schedule record and link it to the
deliverable.
So what I did was this in the Action subform:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim DID As Long
'qlkpDelivID_Action is a lookup query that returns all existing
Deliverables set as Actions and their related CRID
DID = Nz(DLookup("[lngDelivID]", "qlkpDelivID_Action", "[lngCRID]
= " & Forms!frmCR!CRID), 0)
If DID = 0 Then
'Create a new Deliverable and set ysnAction to -1
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblDeliv ( lngCRID, strCategory,
ysnAction, strTitle ) " & _
"SELECT [Forms]![frmCR]![CRID] AS CR, 'Action' AS A, -1 AS
ysn, 'Action Items' AS AI;"
DoCmd.SetWarnings True
DID = Nz(DLookup("[lngDelivID]", "qlkpDelivID_Action",
"[lngCRID] = " & Forms!frmCR!CRID), 0)
Me!txtDelivID = DID
Me!txtCRID = Forms![frmCR]![CRID]
Else
Me!txtDelivID = DID
Me!txtCRID = Forms![frmCR]![CRID]
End If
End Sub
Please note that the CR form is older and that is why the fields
haven't been renamed with the proper naming conventions.
Also remember that the Action subform is based upon a query that
contains and relates (INNER JOIN) the tblDeliv and tblDelivSched based
upon lngDelivID. tblDeliv is required as it contains the CRID
foreign key for the master/child relationship for the subform.
The query SQL is here in case it will help:
SELECT tblDeliv.lngCRID, tblDelivSched.lngDelivID,
tblDelivSched.strTitle, tblDelivSched.lngOwnerID,
tblDelivSched.dtmPStart, tblDelivSched.dtmPFinish,
tblDelivSched.dtmAStart, tblDelivSched.dtmAFinish,
tblDelivSched.dtmPush, tblDelivSched.strComments,
tblDelivSched.lngSlipDays, tblDelivSched.lngSlipCnt,
tblDeliv.ysnAction
FROM tblDeliv INNER JOIN tblDelivSched ON tblDeliv.lngDelivID =
tblDelivSched.lngDelivID
WHERE (((tblDeliv.lngCRID)=[forms]![frmCR]![CRID]) AND
((tblDelivSched.dtmAFinish) Is Null) AND ((tblDeliv.ysnAction)=-1))
ORDER BY tblDelivSched.dtmPFinish;
What happened was I recently restructured the Deliverable tables and
also renamed the fields with proper naming conventions. The
Before_Insert code above used to work perfectly. After I went through
the application, this code failed. It took me hours of troubleshooting
to determine why but I did.
If you open the query the subform is based upon and type a number into
tblDelivSched.lngDelivID it will work properly. But when you related
the Action subform to the CR form using Master/Child lngCRID what it
would do is this:
Autofills foreign key lngCRID in the tblDeliv table.
This autofill automatically creates a new record in the tblDeliv
table.
Then when you try to fill the foreign key lngDelivID in the
tblDelivSched table with the appropriate value, it fails because it
the lngDelivID field in the tblDeliv table contains the value of the
new record just created by the autofill.
Is there a way to prevent this autofill from happening?
The really strange thing is that the code worked prior to the fields
rename. I spent many hours troubleshooting and I am confident that it
works the way I've described. What is really frustrating is I don't
know whether the old code wasn't supposed to work that way or the new
code is. I've deleted and re-created the queries and forms to see
whether some hidden "corruption" or internal gremlins were causing the
error. But now that I understand what is happening I am more surprised
that the old code worked perfectly. I have no idea why it would. But I
really need to know whether that "autofill" can be superseded or the
timing of the update can be changed so I am able to "jump ahead" of
the autofill to do what i need.
I have already implemented a workaround where the query uses the the
criteria [Forms]![frmCR]![CRID] to filter lngCRID so I do not need to
use the master/child link feature of the subform. But this is not an
ideal solution.
I hope my question is coherent and I want to thank you in advance for
reading this long post. I also want to thank all you MVP's for all the
invaluable knowledge you've given me and all of us over the years.
Best regards, Eric