asked on

View vs Edit mode enhancements

I had this question after viewing view mode vs edit mode.

I am trying to make the "edit" button on my form truly toggle. I.E. if it is clicked it will unlock tagged fields, if it is clicked again, it will relock them.

I am using the following code, could you tell me where my syntax is wrong?

Also, I would like all fields that are "unlocked" to turn a light blue, as well as the edit button itself when the form is in edit mode. Could you tell me how to integrate the color changes into the code?

Private Sub LockControls(frm As Form, bLock As Boolean)
Dim ctl As Control
For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
Select Case ctl.Tag
Case "NoLock"
ctl.Locked = False
Case "Lock"
ctl.Locked = True
Case Else
ctl.Locked = bLock 'toggle locks
End Select
Case acCommandButton
Select Case ctl.Tag
Case "NoLock"
ctl.Enabled = True
Case "Lock"
ctl.Enabled = False
Case Else
ctl.Enabled = Not bLock 'toggle locks
End Select
End Select
Next ctl
Set ctl = Nothing
End Sub

Private Sub Edit_Click()

If ctl.Locked = "True" Then

Call LockControls(Me, True)

Else

Call LockControls(Me, False)
End If

End Sub

Private Sub Form_Current()
Call LockControls(Me, True)
End Sub

Private Sub Form_AfterUpdate()
Call LockControls(Me, True)
End Sub

Jim Dettman (EE MVE)

Before you go too far with this, let me say that I did this many, many years ago. My forms looked like this:

The control panel was standard across all the forms and did the things that you'd except; caption changed depending on the mode you were in (View, Add, or Edit), navigation buttons enabled / disabled as appropriate. Clicking "Edit" would cause all the buttons to disappear, replaced by "Save" and "Cancel".

It worked fairly well, BUT (it's a big BUT):

a. If was tough to implement and control. I was fighting Access all the way. For example, if you have a sub form, Access automatically saves the main form's data. So to provide a cancel, you really need to use temp tables.

b. In the end, it slowed users down too much and people didn't like it.

Something to consider before you go too far with this.

Also, to answer your question, it would be good to know where the syntax error is or what is going on that your not expecting.

Jim.

PatHartman

I don't use the code this way. Like Jim, I believe that users find it to be annoying. I use the code to lock or unlock a form based on whether or not the user is allowed to edit. The user never has to click a button. He can edit or not depending on his profile.

To do what you want, you will need to establish a form level variable and toggle it as appropriate. Dim the variable in the form's header

Public bLocked as Boolean

In the Open event of the form set it True or False depending on how you want it to start. If you are going to do this, you also have to alter its appearance so the user can see the state of the form so every place you set the variable, call a sub to alter the appearance of the button

Private Sub Edit_Click()
 If bLocked = True Then
     Call LockControls(Me, False)
 Else
     Call LockControls(Me, True)
 End If
    Call AlterButton
 End Sub

Private Sub AlterButton
    If bLocked = True Then
        Me.cmdEdit.Caption = "Locked"
    Else
        Me.cmdEdit.Caption = "Edit"
    End If
End Sub

Open in new window

Jim Dettman (EE MVE)

<<I use the code to lock or unlock a form based on whether or not the user is allowed to edit. The user never has to click a button. He can edit or not depending on his profile.>>

This is what I do now, if anything. Some don't want security, but if they do, this is the only thing I do.

What I showed you in my last comment was the thing that taught me to let Access "do it's own thing".

While it's wonderful that Access has many features built-in, it's also a double edged sword. The more control you try to have over the interface, the more Access will fight you in doing so.

Jim.

Scott McDaniel (EE MVE )

No points please ...

I agree with Jim and Pat. Trying to force Access to behave in this manner will just end up with you being frustrated, and your users complaining about the way the interface works. Users expect to be able to edit data without having to click a button. Anything else just gets in their way, and that is NOT something you want to do.

J G

ASKER

When I try to use Pats code below I get a compile error : "method or data member not found." compile error code is highlighted in bold below.

Private Sub Edit_Click()
If bLocked = True Then
Call LockControls(Me, False)
Else
Call LockControls(Me, True)
End If
Call AlterButton
End Sub

Private Sub AlterButton
If bLocked = True Then
Me.cmdEdit.Caption = "Locked"
Else
Me.cmdEdit.Caption = "Edit"
End If

PatHartman

is cmdEdit the NAME of your button? If not, replace with the name of your button.

J G

ASKER

How would I set the variable to locked in the open event. I am a little confused on the vba

J G

ASKER

Where is my code off? I don't need anything to be public. The locks will only be for this form. It complies with no errors.

Private Sub LockControls(frm As Form, bLock As Boolean)
Dim ctl As Control
For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
Select Case ctl.Tag
Case "NoLock"
ctl.Locked = False
Case "Lock"
ctl.Locked = True
Case Else
ctl.Locked = bLock 'toggle locks
End Select
Case acCommandButton
Select Case ctl.Tag
Case "NoLock"
ctl.Enabled = True
Case "Lock"
ctl.Enabled = False
Case Else
ctl.Enabled = Not bLock 'toggle locks
End Select
End Select
Next ctl
Set ctl = Nothing
End Sub

Private Sub Form_Current()
Call LockControls(Me, True)

Me.SearchField.SetFocus

End Sub

Private Sub Form_AfterUpdate()
Call LockControls(Me, True)
End Sub

Private Sub Form_Open(Cancel As Integer)

Dim bLocked As Boolean

bLocked = True

End Sub
Private Sub Edit_Click()

If bLocked = True Then
Call LockControls(Me, False)
Else
Call LockControls(Me, True)
End If
Call AlterButton
End Sub

Private Sub AlterButton()
If bLocked = True Then
Me.Edit.Caption = "Locked"
Else

J G

ASKER

compiles with no errors, but locks don't work anymore

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

J G

ASKER

Ok, I realized I want the behavior to be as follows:

when "edit" button is showing, fields are locked (on open, default state)

when "lock" button is showing, fields are unlocked

something is wrong with my code below. When I try it the form opens to a "edit" button, and the fields are locked. That is fine, but when I hit the edit button nothing happens, fields are still locked.

Public bLocked As Boolean

Private Sub LockControls(frm As Form, Block As Boolean)
Dim ctl As Control
For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
Select Case ctl.Tag
Case "NoLock"
ctl.Locked = False
Case "Lock"
ctl.Locked = True
Case Else
ctl.Locked = Block 'toggle locks
End Select
Case acCommandButton
Select Case ctl.Tag
Case "NoLock"
ctl.Enabled = True
Case "Lock"
ctl.Enabled = False
Case Else
ctl.Enabled = Not Block 'toggle locks
End Select
End Select
Next ctl
Set ctl = Nothing
End Sub

Private Sub Form_Current()

bLocked = False
Call AlterButton
Call LockControls(Me, True)

Me.SearchField.SetFocus

End Sub

Private Sub Edit_Click()

If bLocked = True Then
Call LockControls(Me, False)
Else
Call LockControls(Me, True)
End If
Call AlterButton
End Sub

Private Sub AlterButton()

If bLocked = True Then
Me.Edit.Caption = "Lock"
Else
Me.Edit.Caption = "Edit"
End If
End Sub

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial