recording data amendments

[scubadiver007]

I have a parent table of patient figures and a child table for recording date/time and some text.

Is it possible so that every time the figure in the parent table is changed, a new record is added to the child table with a date/time field and text.

 

[MikeLeBen]

I have a parent table of patient figures and a child table for recording date/time and some text.

Is it possible so that every time the figure in the parent table is changed, a new record is added to the child table with a date/time field and text.

I'm just a newbie, but I think you could achieve that with a junction table:

Add a table and name it tblUpdate_Junction or whatever you see fit, with the following fields:
pkUpdateID AutoNumber
fkPatientID Number (foreign key to your parent table)
fkEntryID Number (foreign key to your child table)
DateAdded Date/Time

You should then set relations with the new junction table, so both your former table are linked to it and not one another.

Disclaimer: like I said I'm no expert, it might be a good idea to either do it on a copy of your DB, and/or wait for someone more experienced to guide you.

 

[gemma-the-husky]

if you are trying to record changes to data, then search for some audit functions - there are lots of examples here - you dont want to reinvent the wheel.

 

[scubadiver007]

reinventing the wheel is something I have no intention of doing!

Found this thread here.

http://www.access-programmers.co.uk/forums/showthread.php?t=44231

 

[scubadiver007]

It has occurred to me there might be a very simple and straightfoward method. Simply filter the one record and use an append query to add it to the child table.

I run the append query in the 'on dirty' event of the field using the table ID as the filter and it works a treat!

 

[scubadiver007]

I am having trouble with my code again.

I have a macro that should

1) run an append query in the after update event of the text box (once I have changed the figure) to insert the old record into the audit table.
2) resave and refresh the form
3) then open the audit form.

When I place the cursor in the activity text box, the Fact_ID isn't being picked up in the query (but it works when I run the macro)

INSERT INTO Tble_Audit ( Fact_ID, Upload_Date_Stamp, activity, UserName )
SELECT Tble_patientactivity.Fact_ID, Tble_patientactivity.Upload_Date_Stamp, Tble_patientactivity.Activity, fOSUserName() AS expr1
FROM Tble_patientactivity
WHERE (((Tble_patientactivity.Fact_ID)=[forms]![Form_activity]![Subform Activity]![Fact_ID]));

Q.1) Is there something I am missing?
Q.2) Is there a better way of doing it than using the after update event?

 

[scubadiver007]

The form reference in the query should be

[forms]![form_activity]![subform activity].[form]![Field_ID]