Prevent Deletion

[Toan Chau]

Hi All,

I have a form that users use to add / modify / delete
records.

Is there a way to prevent users from deleting specific
records?

For example, if the records has the business name "Jim's
Auto Sales", can I I prevent it from being deleted if the
user is using the form?

Or maybe on deletion it gets inputed in another table?
(That would acutally be best--if it is possible)

Or can this issue be solved else where? (like in queries
or at the table database level)

A thousand thanks in advance!
--Toan

 

[fredg]

Hi All,

I have a form that users use to add / modify / delete
records.

Is there a way to prevent users from deleting specific
records?

For example, if the records has the business name "Jim's
Auto Sales", can I I prevent it from being deleted if the
user is using the form?

Or maybe on deletion it gets inputed in another table?
(That would acutally be best--if it is possible)

Or can this issue be solved else where? (like in queries
or at the table database level)

A thousand thanks in advance!
--Toan

You would be better off using the [CompanyID] field, not the
[CompanyName] field for this, as it is possible to have more than one
company named the same, but each should have a different ID.
The OR above allows for several different companies to be denied
deletion.
Code the Form's Delete event:

If Me![CompanyID] = 123 or Me!CompanyID = 765 Then
MsgBox "Sorry, you cannot delete this record"
Cancel = True
End If

If you have many different companies you wish to deny deletion to, it
would be best to...
either add a Yes/No field to the table and instead use:

If Me![AllowDeleteField]= False Then
etc.
End If

or ... use a table to hold those company names and ID's and use a
DLookUp() to see if the name/ID is on the list.

If you instead wanted to append the record to a different table and
allow the deletion, code the Delete event:

If Me!CompanyID = 123 Or Me!CompanyID = 765 Then
Dim strSql as String
strSQL = "INSERT INTO tblArchive SELECT tblBasicData.* FROM
tblBasicData WHERE tblBasicData.CompanyID =" & Me![CompanyID] & ";"

CurrentDb.Execute strSQL, dbFailOnError
End If

The record will be archived and deleted from the current table.

 

[Toan Chau]

This looks like it will work.

But I am not very educated on programming.
I read over your coding and can you please clear some
items up for me?

1. Do I need to create a table labled tblArchive?
2. What is the tblBasicData in your coding? Is that the
table that holds the original data?
3. Where do I code the deletion event? Can I get to it if
I view the properites of the delete button?

Thanks again so much for taking the time to answer this.

-Toan

 

[John Vinson]

Hi All,

I have a form that users use to add / modify / delete
records.

Is there a way to prevent users from deleting specific
records?

Put code in the BeforeDeleteConfirm event of the Form and cancel the
event if this record should not be deleted.

For example, if the records has the business name "Jim's
Auto Sales", can I I prevent it from being deleted if the
user is using the form?

Or maybe on deletion it gets inputed in another table?
(That would acutally be best--if it is possible)

Or can this issue be solved else where? (like in queries
or at the table database level)

It's pretty common to set up such tables to not allow deletions *at
all* (e.g. use Allow Deletes = No on the form properties); instead,
have a yes/no field named [Deleted], defaulting to No. The user
"deletes" the record by checking this field; the form is based on a
query selecting only undeleted records so it vanishes from the users'
view.

Of course you can have another form (or change the filter on this
form) to display deleted records and change the field back.

 

[Toan Chau]

I tried to put this command in but the command...
tblBasicData WHERE tblBasicData.CompanyID =" & Me!
...is highlighted red.

Can you help me figure out what it should be?

Thanks,
Toan