I need a good method to "lock" controls on a form, using a checkbox tied to a yes/no field in the record.

I have a form which I want to prevent data being inadvertently modified once a record is created. I am using a field in my underlying record which is a yes/no field, and a checkbox on the form tied to that field. Using the "on current" event for the form, I am placing the following code. This works very well with existing records, however when I create a new record, all of my controls are locked from the getgo, with no way to enter data, even though their default value is unlocked. I have tried adding a string of code setting the locked property to false if my checkbox value is false, even though that shouldn't be necessary, however that hasn't resolved my issue.

Any help will be appreciated.

If Me.chkLock = True Then
     Me.txtPlantcode.Locked = True
     Me.txtUsedFor.Locked = True
     Me.txtCostCenter.Locked = True
     Me.Abbrev.Locked = True
     Me.Project.Locked = True
     Me.txtWBS.Locked = True
     Me.txtTask.Locked = True
     Me.MaximoWO.Locked = True
     Me.ForBldg.Locked = True
     Me.Combo74.Locked = True
     Me.Combo82.Locked = True
     Me.Engineer.Locked = True
     Me.Planner.Locked = True
     Else

End If
Shooter54Asked:
Who is Participating?
 
Nick67Commented:
Using the "on current" event
however when I create a new record, all of my controls are locked from the getgo
A nicer way to code this is
Dim MyBoolean as Boolean
Select Case True
    Case me.NewRecord =True 'new record, unlock
        MyBoolean = False
    Case Me.chkLock = True 'existing record, chklock= true
        MyBoolean = True
    Case Me.chkLock = False 'existing record, chklock= true
        MyBoolean = False
    Case else
         'pick a default for unhandled situations!
End Select

     Me.txtPlantcode.Locked = MyBoolean
      Me.txtUsedFor.Locked = MyBoolean
      Me.txtCostCenter.Locked = MyBoolean
      Me.Abbrev.Locked = MyBoolean
      Me.Project.Locked = MyBoolean
      Me.txtWBS.Locked = MyBoolean
      Me.txtTask.Locked = MyBoolean
      Me.MaximoWO.Locked = MyBoolean
      Me.ForBldg.Locked = MyBoolean
      Me.Combo74.Locked = MyBoolean 
     Me.Combo82.Locked = MyBoolean
      Me.Engineer.Locked = MyBoolean
      Me.Planner.Locked = MyBoolean

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You need to add the other half:

If Me.chkLock = True Then
     Me.txtPlantcode.Locked = True
     Me.txtUsedFor.Locked = True
     Me.txtCostCenter.Locked = True
     Me.Abbrev.Locked = True
     Me.Project.Locked = True
     Me.txtWBS.Locked = True
     Me.txtTask.Locked = True
     Me.MaximoWO.Locked = True
     Me.ForBldg.Locked = True
     Me.Combo74.Locked = True
     Me.Combo82.Locked = True
     Me.Engineer.Locked = True
     Me.Planner.Locked = True
Else
     Me.txtPlantcode.Locked = False
     Me.txtUsedFor.Locked = False
     Me.txtCostCenter.Locked = False
     Me.Abbrev.Locked = False
     Me.Project.Locked = False
.....

But your seeming to say that you've already done that, so put a STOP right above the If check.  Trying adding a record.

You'll hit the stop.  use F8 to step through and see what's happening.

Jim.
0
 
Shooter54Author Commented:
I suspect the issue with this method is that the record doesn't exist yet when performing this check. Does that sound logical? When stepping through in creating a new record, it jumps straight to the else statement, skipping all of the locked = true lines. Is there perhaps a better method to lock up an entire form, without locking the subform tied to it?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Nick67Commented:
The problem with your existing code on a new record -- besides not explicitly lining out all cases -- is our friend NULL
I am using a field in my underlying record which is a yes/no field, and a checkbox on the form tied to that field.
On a new record, that underlying field and checkbox will have a value of null, thus squirrelling up the logic
0
 
Shooter54Author Commented:
You my friend, are a genious. I really appreciate your excellent solution.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I think in this case though, allowing a null in the field is the error.  The initial state should be defaulted to false.

Jim.
0
 
Nick67Commented:
I didn't write it up that way, but think about what you want to have happen if chkLock has a value of null.
Testing a control for null is always a pain, and likes to cause VBA to toss 'you have entered an expression that has no value' errors.
Someone will slag me for sure for saying it, but isnull() isn't my friend.  I never get bulletproof enough results from it to suit my tastes.

Now Nz() IS my friend, and while one is tempted to write
   Case Nz(Me.chkLock, True)= True 'existing record, chklock= null
        MyBoolean = True

You have to decide if that's the logic you want.

But Jim makes a good point.
Perhaps chkLocked should have a default value in the table.
That still won't fix things until a record gets created, but it takes null out of the equation after that.

I've run into another Expert who is ...extremely adamant... that null must never be permitted in boolean field, because you have to deal with its existence afterward.  And he dislikes how I describe the logic of how to deal with them.

Access is weird in that regard.  It permits null, but it has no way to display it.  Look in a Boolean field in a table with True, False and Null values -- you'll only see True and False.  The nulls are there.  Do a COUNT() on the field grouped by it and you'll get three results, one count of True, one count of False, and another count of False--which are the nulls.

Nulls happen and they have to be dealt with -- but if you can deal with them by not permitting them, that's easiest.
0
 
Dale FyeCommented:
Another method to accomplish this is to use the Tag property of controls you want to be able to lock/unlock.  I put the value "Lock" in the tag property of those controls and then use code to loop through the forms control collection; something like, using Nick's code to set the value of MyBoolean

dim ctrl as control
for each ctrl in me.controls
    if instr(ctrl.tag, "Lock") then ctrl.Locked = MyBoolean
Next

Open in new window

It is easy to add the "Lock" value by selecting all of the controls you want to do this with at one time, and then typing the value "Lock" once, in the Tag property of all of those controls.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Access is weird in that regard.  It permits null, but it has no way to display it.>>

  Access does support a triple state check box.

Jim.
0
 
Nick67Commented:
No disagreement there, I meant in the tables and in the query editor.  If you look in SQL Server Management Studio at a table with a bit field contain nulls, you see 1, 0, and NULL.  Link that table into Access and you see -1, and 0 and that's it.  You don't see null.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.