Hello,

I am working with a team on a building-management system. This means that the company were working for wants to enter their data about buildings in a Acces database.

I only have a problem now. The company would like to have the possibility to delete records with use of a button. If they push te button, it has to delete the records. BUT not totally, in fact the records need to be copied to a table. So that they can lookup previous rented buildings.

My question is :

How can i copy data to one table to another ?? The data has to be removed from the original table and be copied in the other table.

I hope some one of you could help me ! it's a very important project at my school.

Why not add a field to the table in question called "Deleted".

Make it a Yes/No field.

Change the record source of the form to ONLY include those that are NOT Deleted.

Make a button that changes the value of the Deleted field for a record from False to True.

Make another form that shows ONLY Deleted records.

The "deleted" records will not show in the main form, but will still be available elsewhere in the system.

Originally posted by scottfarcus
Why not add a field to the table in question called "Deleted".
Make it a Yes/No field.

I wouldn't make it a Yes/No field. A Date/Time field would be more preferable as it contains more information. With a Yes/No you only know its been deleted. With a Date/Time you can then audit the regularity of records deleted and know WHEN.

Query records admin deleted


SELECT * FROM yourTable WHERE YourDateField Is Not Null


Query for all other users


SELECT * FROM yourTable WHERE YourDateField Is Null



Jon

Decent idea, but I don't recall anything in the original post about needing to know when a record was deleted or by whom. Seemed to only be concerned with limiting the view of certain records and maintaining older records somewhere else, hence the yes/no recommendation.

Originally posted by scottfarcus
Decent idea, but I don't recall anything in the original post about needing to know when a record was deleted or by whom. Seemed to only be concerned with limiting the view of certain records and maintaining older records somewhere else, hence the yes/no recommendation.

Scott,

I think the point is ... that having a date accomplishes your idea and the audit. If there is a date (yes) delete the record, if no date (no bit) dont delete the record.

Not only does it give you that information but it also helps to see when the deletion occurred. Others may later be interested in queries that give you the number of lost customers / transactions during a given period, etc.

I think that is why the recommendation was to use a date.

But you are right you can do it with yes/no and the size is considerably smaller per row, but this size difference is very little, so most opt for the better method, which is using a date.

Jon

Yes, I did suggest using a date to advance the idea put forward by Scott. I wasn't undermining. :cool:

Why afterall, just give the answer, when you can encourage better thinking (and reasoning) on problems presented?

I have read all your replys, and would like to thank all of you !

The only problem is that the yes/no idea wouldn't work for me, i think. The one who posted that message said that yes/no checkbox was for one field !, but it has to be for a whole form with a lot !! of information. So could you please help me with it ??

I will discuss the recent posts with my classmates !

I have read all your replys, and would like to thank all of you !

The only problem is that the yes/no idea wouldn't work for me, i think. The one who posted that message said that yes/no checkbox was for one field !, but it has to be for a whole form with a lot !! of information. So could you please help me with it ??

I will discuss the recent posts with my classmates !

Originally posted by Tourniquet
I have read all your replys, and would like to thank all of you !

The only problem is that the yes/no idea wouldn't work for me, i think. The one who posted that message said that yes/no checkbox was for one field !, but it has to be for a whole form with a lot !! of information. So could you please help me with it ??

I will discuss the recent posts with my classmates !

Ehh..Huh?

Each record is displayed once right? Is it a normal form / continous? A continous form will still bound this per record.

Jon

So if i create a field for example called 'deleted' an if i check the box of it, the sytem copys all fields from the form ??


I also would like to ask, where does the information go ?? Do i need to create another table or something like it ??

I really appreciate your help, but i am a little Access n00b.

Awaiting a reply !:)

Tourniquet,
The information isn't going anywhere. The suggestions are to NOT delete the record at all. If you don't delete the record you don't need an additional table. The suggestion was to FLAG the record with the date deleted. This would accomplish a "logical" delete rather than a "physical" delete. Unless you have size issues with your tables, flagging deleted records is a much better idea and easier to work with for reporting purposes than archiving them to a separate table. You simply include criteria that excludes the logically deleted records when you don't want to see them.