append info from a record that will be deleted

[dkuzminski]

My office maintains a tracking database using highly confidential medical records. When a case is closed, we delete the record from the tracking database. What we want to do is copy the non-confidential DateReceived (date), Level(numeric), and Completion status (text) fields for statistical purposes to another table when the delete button on the screen form is selected. The statistical table will hold those plus a DateEnded field that defaults to the date the information is appended.

The problem I'm encountering is that I'm uncertain how to append those portions from only the record that's about to be deleted. So far, any attempts to append result in an append of those fields from the entire table. The button works fine for deleting the record on display. Any ideas for how to incorporate a single-record append into this process?

 

[Blorf]

If the button is pressed on the form that has the record in question, then you can do it like this:

INSERT INTO StatisticTable ( Field1, Field2 )
SELECT [Forms]![YourForm]![Field1] AS field1,
[Forms]![YourForm]![Field2] AS field2;

ChaZ


Ascii dumb question, get a dumb Ansi

 

[HockeyGuy]

Do you use VBA in this application?

If you do then you can:
- set variables to the values of the fields
- create an INSERT INTO ... sql string
- run the the sql using CurrentDB.Execute(sql)

I'll put some meat on those bones if this is in your realm

Rick

 

[dkuzminski]

Nope, it doesn't use any VBA.

 

[dkuzminski]

I should clarify that as meaning aside from what Access creates behind the scenes. I know there are procedures set in place and have viewed some of those, particularly the one for the delete function I created on the form using the Wizard.

 

[dkuzminski]

Please consider this question closed. The simplest solution was to let the user mark a field that the record was to be used for the append and subsequent deletion. Then two queries are operated by pressing a button on the form. If the record isn't marked for append and delete, nothing happens to it. If it is marked correctly, then a simple macro calls up two queries in sequence. The first is an append that grabs exactly what we want from the correct record. The second deletes only marked records.

Thank you to everyone who helped.