Protecting certain information in a Table Field

[gheyman]

If I have a Table with Two fields in a Table (lets say Department Name & Department Number).

The Users have Access to table and can make changes (Add/Delete/Change).

Now lets say I have queries that looks for a certain Department Name. So I don't want the Users to ever be able to Delete or change that Department Name in the Table.

What is the best way to always keep that Department in the Table? Should I make another Table where the user makes the changes... and then Append it to an original Table with that department? I assume if I did this I would also have to go in and delete duplicates.

Any other suggestions or solutions?

Thanks

 

[Joe4]

The Users have Access to table and can make changes (Add/Delete/Change).

You should NEVER give users access to the underlying Tables.
Users should only have access to update data via Forms (a well-designed Access database will be Form-driven - so you control where users go through a Main Menu where they click on buttons that direct them to where they need to go - there should never be any need for them to access objects from the Object Menu - I usually hide that from users).

With Forms, you can "lock" down certain fields so that they cannot change them. You can also create different forms for entering new data versus editing existing data, if you need to have different controls/protections in those scenarios.

 

[gheyman]

Thanks, Joe. But I cannot lockdown that field. I want them to be able to add and modify the data except certain ones. For example if I have a "Department Name" field and I have Department XYZ entered into it. I want the user to be able to add new department names and modify existing name EXCEPT Department XYZ. That's why I was thinking have a separate table that contains Department XYZ and then a second Table that the User can Add/Delete... from a Form. Then when they are done, create a Third table that combined the First Table (With Department XYZ) with the Table the user can Modify (via a form). Not to say this is the best way - which is why I am asking the board how they would go about doing this.

 

[Joe4]

I would just add a flag to that table with something like "Lock Record". Don't present it on the Form to the users, but have VBA lock records/fields depending on the value of that flag.

 

[gheyman]

Thanks