CODEWITHSUNDEEP
ms-accessms-access-2013
Sylvia
Sylvia

Reputation: 2616

Lock fields at table level for linked table

I'd like to build the simplest MS Access database possible, to allow users to edit a linked table (it links to a SQL Server table).

I only want users to edit 2 of the 10 fields.

Is there any way to do this at the table level? I know I could do this at the form level, but I'm looking for something really quick and easy. I was thinking perhaps there's some way of doing it via a Validation Rule, but I don't see a way of getting the previous/current value.

The main reason I'd like to do this with a table, instead of a form, is that there is the excel-like feature which allows you to filter the table recordset REALLY easily by right-clicking on the field, and filtering (as long as you have it turned on for ODBC tables, like so MSAccess and ODBC. Filter dialog doesn't show).

@HansUp - thanks for your answer. I think what you described would NOT get me the filtering capability that I have in the table view. Or am I missing something?

Upvotes: 1

Views: 740

Answers (1)

HansUp
HansUp

Reputation: 97101

You can give the users an Access query to limit which columns they can edit. Use a field expression instead of the field itself for those you don't want them to edit. And use just the field itself for those you do want them to edit:

SELECT y.ID + 0 AS [ID], y.text_field & '' AS [text_field], y.date_field
FROM YourTable AS y;

In that example, ID and text_field would be read-only, and date_field could be edited.

However, I don't quite understand why you're opposed to using a form for this purpose. It should be quick and easy to set Enabled = False and/or Locked = True for any bound data controls for which you don't want the users to change values. You could create such a simple form starting from the form wizard and then modifying the Enabled and Locked properties as needed. It should really take only a couple minutes.

Upvotes: 2

Related Questions