How best to allow edits to some fields but not others?

[RSW]

I had a continuous form that was set up with Allow Edits = No, because I don't want any records to be changed once they're entered.

However, I added a text box in the form header that will allow users to filter based on what they type in the box. I found that this box can't be typed into unless Allow Edits is set to yes. However, I still want records to be locked down once they're entered.

Does anyone know a good way to handle this?

Thanks in advance.

 

[boblarson]

Go into design view of the form and place a tag of "lock" (without the quotes) into the TAG property of the controls you want to lock.

Then you can set the AllowEdits to YES so your text box works and then you put this in the On Current event of the form:

Dim ctl As Control

For Each ctl In Me.Controls
   If ctl.Tag = "lock" Then
      ctl.Locked = True
   End If
Next ctl

 

[RSW]

Thanks Bob,

If I do that, will I then be able to use the locked fields to add records to begin with?

 

[boblarson]

Thanks Bob,

If I do that, will I then be able to use the locked fields to add records to begin with?

You can either add a button to enable them or I THINK you can just use:

Dim ctl As Control
[COLOR="darkorchid"]If Not Me.NewRecord Then[/COLOR]
   For Each ctl In Me.Controls
        If ctl.Tag = "lock" Then
           ctl.Locked = True
        End If
   Next ctl
[COLOR="DarkOrchid"]End If[/COLOR]

 

[RSW]

Unfortunately, that doesn't seem to work--the form fields are locked down for the new record (technically, I'm not sure if it's even a record yet, because without being able to type into any fields, it's not being assigned an AutoNumber).

 

[boblarson]

Unfortunately, that doesn't seem to work--the form fields are locked down for the new record (technically, I'm not sure if it's even a record yet, because without being able to type into any fields, it's not being assigned an AutoNumber).

If you set the form's Allow Additions property to NO and then have a button on the form that says "Add Record" you could use the click event to do this:

Dim ctl As Control

Me.AllowAdditions = True

For Each ctl In Me.Controls
     If ctl.Tag = "lock" Then
        ctl.Locked = False
     End If
Next ctl

And then when you go to the next record it will reset back because of the On Current event code. And add Me.AllowAdditions = False to the On Current code.

 

[missinglinq]

You have to mark the one textbox you want to use, so that Access can recognize it, then loop thru all other controls and Lock/Disable them.

In Design View, select the textbox you want "active" and goto Properties - Other and in the Tag Property box enter DoNotLock, just like that, no quotes.

Then place this code in the code window

Private Sub Form_Current()
Dim ctrl As Control

If Not Me.NewRecord Then
  For Each ctrl In Me.Controls
    If (TypeOf ctrl Is TextBox) Or (TypeOf ctrl Is CheckBox) Or (TypeOf ctrl Is ComboBox) Then
     If ctrl.Tag <> "DoNotLock" Then
       ctrl.Enabled = False
       ctrl.Locked = True
     Else
       ctrl.Enabled = True
       ctrl.Locked = False
     End If
    End If
  Next
Else
For Each ctrl In Me.Controls
 If (TypeOf ctrl Is TextBox) Or (TypeOf ctrl Is CheckBox) Or (TypeOf ctrl Is ComboBox) Then
  ctrl.Enabled = True
  ctrl.Locked = False
 End If
Next
End If

End Sub

Now change AllowEdits back to Yes. You should be set now. On new records, all controls will be usable. On an existing record (one that has been saved to the table) only the textbox with the DoNotLock Tag will be usable.

 

[missinglinq]

Sorry, Bob! Didn't see you there! Had this almost done and the dog had to go out! Posted it without looking!

 

[boblarson]

Sorry, Bob! Didn't see you there! Had this almost done and the dog had to go out! Posted it without looking!

Been there and done that . No worries - we're on the same path anyway.

 

[RSW]

Thanks to both of you for your help!

Missinglinq's solution unfortunately doesn't seem to work I still couldn't add data into new records. Bob's "Add Record" button makes the page a little more unwieldy, but it will do fine

 

[missinglinq]

Then you've changed the code somehow. This code has worked for years. I just copied it from my post here, pasted it into a form with a single textbox with a Tag of DoNotLock and it works exactly as expected.

Granted, I only included TextBoxes, CheckBoxes and ComboBoxes as the controls to lock, and you'd have to include others (such as Listboxes, Option Groups, etc) here if you wanted to lock these as well, but this code, pasted in as posted here, certainly should allow new records to be added.

But I'm glad you got it working at any rate.

 

[RSW]

Then you've changed the code somehow. This code has worked for years. I just copied it from my post here, pasted it into a form with a single textbox with a Tag of DoNotLock and it works exactly as expected.

Granted, I only included TextBoxes, CheckBoxes and ComboBoxes as the controls to lock, and you'd have to include others (such as Listboxes, Option Groups, etc) here if you wanted to lock these as well, but this code, pasted in as posted here, certainly should allow new records to be added.

But I'm glad you got it working at any rate.

It's very possible I screwed something up trying it because I don't have any of the other controls you mention. I appreciate all of this information, I am learning a lot.

 

[missinglinq]

All that counts is that you got something working!

 

[ions]

Nice solutions