delete how? please!!!!!

[piet123]

have searched forum for this but not getting what i want (or maybe i just don't understand very well) but here's my question anyway:

the table is related to several other tables and the cascade delete property is on.

on a master form (with subfrm for related tbls) i want cmdbutton to do this:

when the user DELETE a record, the deleted record (and! it's subdata) must be removed from the current table(s) but copied(?) to another table(s) which won't be accessed by the user, so that i can keep track of deleted records.

do i have to setup DUPLICATE tables (and relate them again) to capture deleted records ? and then have a qry that will cut(?) paste records that gets deleted?

i think i need only one qry that will do all this, but not sure how ?

any help much appreciated.
regards,
Piet.

 

[llkhoutx]

Relation for the table holding deleted record is ok, but no cascades or the records will be deleted and you wont be able to create without a parent. I suggest no relation to this table. You have to stuff data into this table manually using ADO or DAO code on the BeforeDelete event.

 

[quest4]

I do it a little differently especially when I archive the "deleted" records, which are in a different dbase, but I you like it can be in the same. I have an append query append the data to the archive table and then I have a delete query delete the data from the current table. Works very well for me. hth.

 

[piet123]

what about the SUB-records ?

I do it a little differently especially when I archive the "deleted" records, which are in a different dbase, but I you like it can be in the same. I have an append query append the data to the archive table and then I have a delete query delete the data from the current table. Works very well for me. hth.

i think i need your way to do this, but not sure how to begin. here's what i have:

- mainform data captured in maintable.
- subforms data captured in other tbls (related to maintbl)

therefore the CASCADE delete must!! be used in order to delete sub-records together with the main record.

if i eventually figure out how to write a proper append query, how do i tell access to REMOVE one record (and! it's sub-records) form the maintbl and! the related tbls and then append them to an "archive table" ??

- this means i need a duplicate(?) of the maintable...
and duplicates of all! subtables ?

am i on the right path here ?

just point me in the right direction and i'll fight my battle from there.

your advice greatly appreciated,

regards,
piet.

 

[quest4]

First in the relationships I almost always check all three checkboxes, cadcade delete and update and referential integrety. First thing is decide whether you want a separate dbase for the archived, deleted/cancelled records or same? With a separate dbase, you won't even know they are separate, you will be able to open everything from your current dbase, using linked tables. If that is what you want, then get everything in this dbase working correctly, less the archiving of records. I archive all completed and cancelled records. So the next step is what is going to trigger the archiving? I have two txtboxes I use, DateComplete and DateCancelled, When either is "Is NotNull", away they go when the dbase is closed. Now when everything is ready and the way you like and works fine, less append queries and delete queries, let me know, and we will do the next stage. hth.

 

[piet123]

archive to same db

First in the relationships I almost always check all three checkboxes, cadcade delete and update and referential integrety. First thing is decide whether you want a separate dbase for the archived, deleted/cancelled records or same? With a separate dbase, you won't even know they are separate, you will be able to open everything from your current dbase, using linked tables. If that is what you want, then get everything in this dbase working correctly, less the archiving of records. I archive all completed and cancelled records. So the next step is what is going to trigger the archiving? I have two txtboxes I use, DateComplete and DateCancelled, When either is "Is NotNull", away they go when the dbase is closed. Now when everything is ready and the way you like and works fine, less append queries and delete queries, let me know, and we will do the next stage. hth.

1. "...the relationships I almost always check all three checkboxes, cadcade delete and update and referential integrety."
1.1. I agree and my tbl relations are like that

2. Archive must! remain in same! db. Not seperate db.
I need to write(archive) ONLY DELETED records to another table, which won't be accessed by user.

3. For my needs, It is not important WHEN(date) the record is deleted.
If user deletes a record, it's Ok, but I need to know (not when) but how many records were deleted for the month.

4!! Cascade Delete will also delete 'sub-records' (ofcourse); so the new "archive of deleted records table" should keep 'sub-records' also.

i'm not sure if i explain correctly here, but i know i need on the form a delete record action (which will firstly write the deleted record(and subs) to the archive tbl, and then cascade delete the current record.

thanks for the replies so far, i hope you can point my in the right direction.

have a good day !
regards
Piet.

 

[quest4]

First the remote dbase is tables only, in the end. They will be linked to your current dbase. I like to keep them read only, but that is your choice. Don't panic, just make a copy of the original and modify the copies. Do you have it working the way you want less querries at this point in time? The principle will still be the same, wee will just be working with DateDelete, instead of DateComplete. When all forms and reports are working correctly let me know, we will