Deletion requiring table?

[formby via AccessMonster.com]

i hope this doesnt sound like a dumb question, i jst need alittle guidance.

i have a form which can be deleted (according to the dates entered). if the
user decides to delete the record, is there a way i can store some
information from the record on another form BEFORE it's completley deleted...
i.e i need to know which employee deleted that record and why (as well as the
record_id etc) does this mean i need to create a new table to store it? or
can it be saved on a readonly form?

 

[Douglas J. Steele]

Rather than physically deleting the record, why not simply have a "Deleted"
flag on it? You can create a query that only returns those rows for which
the Deleted flag is False, and use that query wherever you would otherwise
have used the table.

 

[Guest]

Douglas, as usual, is correct; however, you have a few misconceptions I think
need to be corrected to help you in the future.

First, data is not stored in forms. It is stored in tables. Forms are only
a "window" used to view the data in tables. That data may be retrieved by
queries and presented to the form.

Notice that forms have a property named Record Source. This tells the form
where to look for the data. If you identify a table in the Record Source
property, you will be using the data in the table as is. If you identify a
query, the sorting and filtering of the query will determine what data is
presented to your form.

So what Douglas is saying is that if you add a field to your table that
indicates the record is "deleted" and filter out deleted records using a
query as the Record Source of the form, you will not see them.

Now, if you want to know which user deleted the record and when they deleted
it, add two more fields to the table. One for the user name and one for the
date/time it was deleted. To put that information into the table, you will
need a Delete command button on your form. In the click event of that form,
you will want to update the three fields in the table. To do that, the
easiest way is to add controls to your form for each of the three fields and
bind them to controls on your form. You can make the controls invisible so
the user never sees them. Then all you have to do is populate the fields in
the button's click event.

 

[fapa via AccessMonster.com]

thanks for your suggestion and the explaination,

i assume thay by adding a delete flag to the form - the related forms would
need to be updated? as the benefit of deleting the whole record is to prevent
it's 'data' to continue on though to other tables.

Douglas, as usual, is correct; however, you have a few misconceptions I think
need to be corrected to help you in the future.

First, data is not stored in forms. It is stored in tables. Forms are only
a "window" used to view the data in tables. That data may be retrieved by
queries and presented to the form.

Notice that forms have a property named Record Source. This tells the form
where to look for the data. If you identify a table in the Record Source
property, you will be using the data in the table as is. If you identify a
query, the sorting and filtering of the query will determine what data is
presented to your form.

So what Douglas is saying is that if you add a field to your table that
indicates the record is "deleted" and filter out deleted records using a
query as the Record Source of the form, you will not see them.

Now, if you want to know which user deleted the record and when they deleted
it, add two more fields to the table. One for the user name and one for the
date/time it was deleted. To put that information into the table, you will
need a Delete command button on your form. In the click event of that form,
you will want to update the three fields in the table. To do that, the
easiest way is to add controls to your form for each of the three fields and
bind them to controls on your form. You can make the controls invisible so
the user never sees them. Then all you have to do is populate the fields in
the button's click event.

i hope this doesnt sound like a dumb question, i jst need alittle guidance.

[quoted text clipped - 4 lines]

record_id etc) does this mean i need to create a new table to store it? or
can it be saved on a readonly form?

 

[Guest]

The delete flag is not added to the form. It is added to the record in the
table. It is just another field. Remember, forms are only a way to view and
manipulate the data in the tables. Forms don't have data.

The dirt is in the ground.
You use a shovle to dig dirt out of the ground.
A table is the dirt.
A form is a shovel.

You don't update the form. You can use the form to update the flag in the
table.
Deleting the record will prevent it from moving forward; however, if you
include the flag field of the table in the queries or procedures you use to
manipulate t he records in the table and filter out records flagged as
deleted, it has the same effect of not moving the "deleted" records forward.

thanks for your suggestion and the explaination,

i assume thay by adding a delete flag to the form - the related forms would
need to be updated? as the benefit of deleting the whole record is to prevent
it's 'data' to continue on though to other tables.

Douglas, as usual, is correct; however, you have a few misconceptions I think
need to be corrected to help you in the future.

First, data is not stored in forms. It is stored in tables. Forms are only
a "window" used to view the data in tables. That data may be retrieved by
queries and presented to the form.

Notice that forms have a property named Record Source. This tells the form
where to look for the data. If you identify a table in the Record Source
property, you will be using the data in the table as is. If you identify a
query, the sorting and filtering of the query will determine what data is
presented to your form.

So what Douglas is saying is that if you add a field to your table that
indicates the record is "deleted" and filter out deleted records using a
query as the Record Source of the form, you will not see them.

Now, if you want to know which user deleted the record and when they deleted
it, add two more fields to the table. One for the user name and one for the
date/time it was deleted. To put that information into the table, you will
need a Delete command button on your form. In the click event of that form,
you will want to update the three fields in the table. To do that, the
easiest way is to add controls to your form for each of the three fields and
bind them to controls on your form. You can make the controls invisible so
the user never sees them. Then all you have to do is populate the fields in
the button's click event.

i hope this doesnt sound like a dumb question, i jst need alittle guidance.

[quoted text clipped - 4 lines]

record_id etc) does this mean i need to create a new table to store it? or
can it be saved on a readonly form?