Recommend a DELETE method?

[gray]

Hi All

WinXPPro
Access 2002 SP3

I have a form in datasheet view that presents a list of records. When the user selects a record for deletion I also want to go away and delete a corresonding record in another (unbound) table . I want to test that the corresponding record has been deleted before deleting the datasheet record, in fact it's crucial.

I'm thinking:-

1. In the Keydown event:-
a) Check for the Delete Key.
b) Trap the selected rows in and store in a global variable (could you help with syntax here pls?).

2. In the AfterDelConfirm event:-
a) Loop around the above global variable and issue a db.Execute DELETE etc... for the current record's "corresponding" partner.
b) Somehow trap the result of the above DELETE ( dbFailOnError??) in VB.
c) If successful - Allow the delete to go ahead for the current record.
d) If unsuccessful - Exit the AfterDelConfirm so that the current datasheet record does not get deleted. Put out a msg to the user and log the error message.

Or can anyone recommend a bullet-proof method please? An example would be great if poss.

Thanks

 

[Rabbie]

Is there a relationship between the two tables? If so then just make sure that Cascade delete is set on the relationship then if you delete the Parent record the Child records will be automatically deleted.

 

[gray]

Hi Rabbie

Thanks for the reply.

No, the other table is completely standalone. I left it that way to avoid any locking problems.

cheers

 

[Rabbie]

Hi Rabbie

Thanks for the reply.

No, the other table is completely standalone. I left it that way to avoid any locking problems.

cheers

In that case how do you know which record to delete?? If in fact there is a relationship then perhaps you need to formally define it because it will make life easier for you.

Is your data properly normalised? If it isn't you just storing up problems for yourself.

 

[gray]

Hi Rabbie

I create the datasheet records in a temp table, I add a column to it containing the corresponding record's autonumber rec_Id.

I like the cascade delete though so I'm going to do a bit more resarch on that. I guess I could add a relationship between my tempTbl and permanent one...

cheers

 

[Rabbie]

Alternatively you could have a simple Delete Query to delete the record from your temp table.

 

[gray]

Re: Recommend a DELETE method? Now 3022 Error

Hi

Unfortunately, whilst experimenting with the "relationship idea".. I seem to have created another problem.

In outline, I create a copy of a "permanent" table into a "working space" table which gets bound to subform. I then copy appropriate records into it based on the mainform. I use

strPath = CurrentProject.Fullname
DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acTable, etc etc

to create my workspace table. When records are written into it, Access handily copies over the same auto-number values as those for the real record in the orginal "permanent" table. I then work on the records and write them back.

I've been using this successfully for months but since trying to add a relationship between my "permanent" and "workspace" tables in order to try the "Cascade Delete" technique, I constantly get 3022 duplicate entry errors when I try to add records into the workspace tbl.

I've deleted any/all relationships (or at least none show in the relationship tool!), compacted/repaired the Db, deleted, deleted all records from the "workspace" Tbl and re-created the workspace tbl altogther..... but I just get the 3022 errors.

Curiously, if I run a manual query to add records into the "wotkspace" Tbl it works perfectly...even if my main/subform are open.

My guess is there's a phantom relationship still somewhere??

Any ideas chaps?

 

[gray]

Sorry All

Forget that last bit about duplicate entries... t'was my finger trouble!!

I'd still be interested to hear answers on my original question though (but thanks to Rabbie for his idea).

cheers