Form Lock/Unlock for Data Entry

[djkekos]

I'm trying to add a toggle button to a form that will lock and unlock the form to prevent inadvertant editing errors.

I've looked up all of the code I could find on this issue and still run into errors and problems (my VBA level is beginner). Any help would be great.

I have a form with two sub-forms:
frmPhysician
subfrmPhysicianAddress
subfrmPhysicianShipments

I am placing a toggle button with a picture of a lock named tglLockUnlock

I want the form to load with the toggle button depressed and the form set to No Edits. Then I want the user to push the button to lock or unlock the record for edits. And if the user goes to a new record I want to prompt the user to lock the record if they have not done so.

Any simple code I could use? Thank you!

 

[Pat Hartman]

In the Current event of the parent form -

Me.AllowEdits = False
Me.AllowDeletions = False
Me.AllowAdditions = False

In the Current event of each of the subforms -

Me.AllowEdits = Me.Parent.AllowEdits
Me.AllowDeletions = Me.Parent.AllowDeletions
Me.AllowAdditions = Me.Parent.AllowAdditions

In the Click event of the button -

Me.AllowEdits = True
Me.AllowDeletions = True
Me.AllowAdditions = True

 

[djkekos]

Pat, thanks for your response. I tried the code above and it worked in locking the records but unfortunately also locked all of my other command buttons on the forms, (I guess that's logical) so I would have to make them exceptions.

So since the whole form is locked the toggle button is also locked and I can't press it. I guess I'll research how to make exceptions for the code of the parent record.

 

[Simon_MT]

There is another way. Have an Enquiry Screen - controls that can't be changed and an Entry Form that allows edits additions etc.

Simon

 

[Pat Hartman]

Setting the "Allow" properties does not lock buttons. There is some other problem.

 

[djkekos]

You're right, Pat. The other buttons are working now. It is only the button I am trying to use to unlock the record that is not working. When I click it it won't even stay depressed. I'm using the code:

Private Sub tglLockUnlock_Click()
Me.AllowEdits = True
Me.AllowDeletions = True
Me.AllowAdditions = True
End Sub

But it's still not working. I wonder if the use of a toggle button makes a difference instead of a command button. I'll mess with it.

Any more suggestions from anyone would be greatly appreciated. Thanks

 

[Pat Hartman]

Toggle buttons are used within option groups. Switch to a standard button.

 

[gemma-the-husky]

if you set allowedits to false, everything except a command button gets locked. combo boxes, option groups etc.

however, why are you so bothered about editing. if you are allowing any user to toggle the lock/unlock, then you are happy for them to edit stuff. just leave it to the user to take responsibility for his data. they will/might be intensely irritated having to keep setting the lock/unlock field in order to edit data. I know I would be - it is most unusual to see this in windows applications.

you could put some extra checks on particular fields. Use the beforeupdate event to ask users to confirm that they do want to change the field. use the form's beforeupdate button as a final confirmation that they are happy with all the changes. give them an undo button to remove all unwritten edits. (although they can generally do this by pressing esc a few times)

 

[djkekos]

Again, thanks for the replies.

Pat, I switched it to a command button and it works in unlocking the subforms but not the parent form. When the form loads everything is locked down but when I push the button only the subforms allow me to edit. That seems strange since the OnCurrent() event of the subform is = to the parent form.

 

[djkekos]

In response to gemma-the-husky,

The database I'm creating is mainly for data entry but it will require the users to do some occasional search and edits. My users are not very savy so I need to make sure they consciously make the decision to update an existing record. I would prefer not making them hit repeated message boxes after updating individual controls.

I'd be happy to hear any other suggestions, though. I'm still very new at this.

Thanks

 

[kaiwei]

I guess I'll research how to make exceptions for the code of the parent record.

 

[djkekos]

Any other ideas?

 

[VilaRestal]

You can always put a common piece of text in each controls Tag property and loop through all controls in the form setting the enabled and locked properties to the opposite of what they are:

    Dim ctl As Control, subctl As Control
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acCheckBox, acTextBox, acComboBox
                If ctl.Tag = "lock" Then 
                     If Me.ActiveControl.Name <> ctl.Name Then ctl.Enabled = Not ctl.Enabled
                     ctl.Locked = Not ctl.Locked
                End If
            Case acSubform
                For Each subctl In ctl.Form.Controls
                    Select Case subctl.ControlType
                        Case acCheckBox, acTextBox, acComboBox
                            If subctl.Tag = "lock" Then 
                                 If ctl.Form.ActiveControl.Name <> subctl.Name Then subctl.Enabled = Not subctl.Enabled
                                 subctl.Locked = Not subctl.Locked
                            End If
                    End Select
                Next subctl
        End Select
    Next ctl

And you could run that at form load to initially lock them.

Edit: Added a line that presents it bugging out trying to disable the control with the focus.

 

[sharky376]

You can always put a common piece of text in each controls Tag property and loop through all controls in the form setting the enabled and locked properties to the opposite of what they are:

    Dim ctl As Control, subctl As Control
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acCheckBox, acTextBox, acComboBox
                If ctl.Tag = "lock" Then 
                     If Me.ActiveControl.Name <> ctl.Name Then ctl.Enabled = Not ctl.Enabled
                     ctl.Locked = Not ctl.Locked
                End If
            Case acSubform
                For Each subctl In ctl.Form.Controls
                    Select Case subctl.ControlType
                        Case acCheckBox, acTextBox, acComboBox
                            If subctl.Tag = "lock" Then 
                                 If ctl.Form.ActiveControl.Name <> subctl.Name Then subctl.Enabled = Not subctl.Enabled
                                 subctl.Locked = Not subctl.Locked
                            End If
                    End Select
                Next subctl
        End Select
    Next ctl

And you could run that at form load to initially lock them.

Edit: Added a line that presents it bugging out trying to disable the control with the focus.

Thx VilaRestal, this actually works for me, but it locks everything, and I would like to be able to add new record to a subform ...