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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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 FyeOwner, Developing Solutions LLCCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.