How to let users modify only two data fields while locking the rest in a form?

[shanecmc]

Help with locking specific fields on records and creating new ones.

I am an Access beginner so forgive the elementary question. I have a small database (used by about 20 people). They need to be able to create new records and are able to now. They also need to be able to modify specific fields in current records. For example they shouldn't be able to modify the users name or address but they should be able to modify the persons phone number or place a check in a checkbox (just an example). Thanks for your help.

 

[Bob Miller]

I am an Access beginner so forgive the elementary question. I have a small database (used by about 20 people). They need to be able to create new records and are able to now. They also need to be able to modify specific fields in current records. For example they shouldn't be able to modify the users name or address but they should be able to modify the persons phone number or place a check in a checkbox (just an example). Thanks for your help.

Are they using a form to do this? If they aren't they should be. You can lock individual fields in a form so that the user can't change them. This is another good reason to use forms for data manipulation and not allow users to go directly into the table.

 

[ajetrumpet]

Open your form and take a look at the properties for your controls (text boxes, etc...)....you can change them in accordance with your editing preferences....

 

[rainman89]

but heres the thing, if im reading his request correctly, how can he distinguish between a new record to allow all edits, and a previous record that can only have certain things editied...... i might be wrong

 

[shanecmc]

rainman89 hit it on the head. They are using a form to enter data. The problem is that I need the user to be able to enter a complete record and then later they need to go back to a record and change just two fields. I don't want them to be able to change anything else on a previously created record except for those two specific fields. Thanks for your help.

 

[ajetrumpet]

Then create a data entry form and do not allow edits or deletions there, but only additions, then how about a modification form, with specified properties that you need on all the controls there?? Both sourced the same....
It's better then making changes everytime your users have to do this...

 

[boblarson]

Actually, in the On Current event of the form you can check to see

If Me.NewRecord Then
... put code to unlock the fields here
Else
... put code to lock the ones that need to be locked
End If

 

[shanecmc]

I know its asking a lot but could you give me a code example? I'm very new to access and am really only doing this database out of forced necessity.

 

[boblarson]

Okay, I like to use tags in my control properties to make locking and unlocking easier. Select the controls you want to be able to lock and unlock and then, in the tag property in the properties dialog box, type "ForNew" without the quotation marks but in the code below you will use the quotation marks.

Then, in the On Current event of the form (in the VBA window) put:

Dim ctl As Control

If Me.NewRecord Then
   For Each ctl In Me.Controls
      If ctl.Tag = "ForNew" Then
         ctl.Locked = False
         ctl.Enabled = True
      End If
   Next ctl
Else
   For Each ctl In Me.Controls
       If ctl.Tag = "ForNew" Then
          ctl.Locked = True
          ctl.Enabled = False
       End If
   Next ctl
End If