By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,483 Members | 1,644 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,483 IT Pros & Developers. It's quick & easy.

Syntax

P: 20
(Access 03)
Would someone tell me what keyword is used for finding deleted records in a table so they can be appended to another table ?
Jan 23 '08 #1
Share this Question
Share on Google+
4 Replies


missinglinq
Expert 2.5K+
P: 3,532
I'm afraid you'd have to talk to Harry Potter about that, as magic would be involved! Once a record is deleted, it is no longer in the table! You'd have to use an append query to place a copy of the record in the archive table then delete the record to accomplish this.

Another strategy used by many is to not actually delete the record, but instead to have a Yes/No checkbox on the record called Inactive. When you want to "delete" the record, you simply check the box.

You set up the query your form is based on (and all forms should be based on queries, even if only one table is involved) so that the Criteria for Inactive is 0 (zero)

You can then display "deleted" records by using an identical query and setting the Criteria for Inactive to -1.

Welcome to TheScripts!

Linq ;0)>
Jan 23 '08 #2

P: 20
I really appreciate that. Makes great sense.

BTW.
What do you mean ALL forms should be based on queries ?
If I want to add customers, or input receipts, etc. wouldn't the forms for those have to be based on the table(s) ?
Jan 23 '08 #3

P: 20
Tried the following numerous ways and fashions:

INSERT INTO tblArchive
SELECT cid AS cid
FROM tblCustmr
WHERE cancel='y';

cid is the account number for each customer and is the join field.
cancel is the field where I simply put a 'y' in for testing the query.

Get this error:
"MS Access set 0 field(s) to null, due to a type conversion failure, and it
didnít add 39 records to the table due to key violations, 0 record(s) due
to lock violations, and 0 record(s) due to validation rule violations."

Goes on to ask if I want to run the query anyway but I haven't.

ny help ?
Jan 24 '08 #4

P: 20
Tried the following numerous ways and fashions:

INSERT INTO tblArchive
SELECT cid AS cid
FROM tblCustmr
WHERE cancel='y';

cid is the account number for each customer and is the join field.
cancel is the field where I simply put a 'y' in for testing the query.

Get this error:
"MS Access set 0 field(s) to null, due to a type conversion failure, and it
didnít add 39 records to the table due to key violations, 0 record(s) due
to lock violations, and 0 record(s) due to validation rule violations."

Goes on to ask if I want to run the query anyway but I haven't.

ny help ?
decided not to pursue the append
Jan 24 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.