Testing for Changes in Table before Updating Values in Other Table

[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

Already responded to in another newsgroup. Please don't post copies of the
same question in multiple newsgroups.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff, the first posting went inadevertantly into an unintended group
(apologies).

Regarding your take on this....The database with the table into which the
appending/updating is happening was built independently of the from which
it's coming w/o the goal of their being somehow intertwined. So, the
objective is to treat them as independent mdbs (perhaps with a linked
('Patients on Follow Up') table in the one with the 'Screening Log' table.
With that said, the objective remains the same.

Ted

Ted

Already responded to in another newsgroup. Please don't post copies of the
same question in multiple newsgroups.

Regards

Jeff Boyce
Microsoft Office/Access MVP

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