Appending Records to Another Table When Certain Values Get Updates

[Guest]

I have an append query which is launched when a user selects Case 5 from a
seven level Options Group (Frame1) from an A2K form (‘Screening Log’) as shown

INSERT INTO [Patients on Follow-Up] ( Dummy, [Study#], [Reg#], [IRB Number],
MR_Number, [Pt Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[Sponsor ID Nbr], [Screening
Log].SequenceNum, [Screening Log].[IRB Number], [Screening Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials], [Screening
Log].OffStudyDate AS [On-Study Date], [Screening Log].Campus AS Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code = [Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening Log].MR_Number)=[Forms]![Screening Log
(Edit Only)].[MR Number]) AND (([Screening
Log].OffStudyDate)=[Forms]![Screening Log (Edit Only)].[OffStudyDate]) AND
(([Screening Log].Outcome_) In (5)));

After developing it, it’s occurred to me that a user could conceivably
update/modify some of the values in the Screening Log table which would mean
it was important to bring their kindred values in the ‘Patients of
Follow-Up’ table into alignment with the newer values.

Is there some way that the Screening Log form could be upgraded to sense the
occurrence of a change in the value(s) of any of the controls/fields involved
in the append query (with perhaps the exception of the ‘Dummy’ field which is
a constant having the value ‘1’)?

-Ted

 

[Jeff Boyce]

Ted

Not sure I completely understand how you've structured your data, but it
seemed like you were describing having the same data appear in more than one
table.

If this is your situation, consider looking into normalizing the structure a
bit more. It would be rare that you'd need to do the work of adding records
to a table. In a well-normalized design, Access would take care of adding
"child" records appropriately after a "parent" record was established, but
I'm not getting that sense here.

Regards

Jeff Boyce
Microsoft Office/Access MVP