Me.AllowEdits = False and ComboBox

[Robert T]

I recently noticed 1 of our users was changing old records on a form, which
isn’t acceptable. I’m not sure if he was doing it deliberately or by
accident, however, I decided to add the following code to the form’s OnLoad
event.

Me.AllowEdits = FALSE

That ensures no one will inadvertently edit an old record. I also placed a
button on the form that converts Me.AllowEdits back to True so users who need
to edit an old record can do it.

All of the above works fine. The problem is a Combo Box that looks up
records on the form. It will NOT work unless the user clicks on the
aforementioned button that turns Me.AllowEdits back to true.

Obviously that isn’t acceptable, so I placed the following line in the
OnClick event of the Combo Box but it does NOT work. I’m mystified.

Me.AllowEdits = True

What am I doing wrong?

Thanks,
Robert

 

[Dirk Goldgar]

I recently noticed 1 of our users was changing old records on a form, which
isn’t acceptable. I’m not sure if he was doing it deliberately or by
accident, however, I decided to add the following code to the form’s
OnLoad
event.

Me.AllowEdits = FALSE

That ensures no one will inadvertently edit an old record. I also placed a
button on the form that converts Me.AllowEdits back to True so users who
need
to edit an old record can do it.

All of the above works fine. The problem is a Combo Box that looks up
records on the form. It will NOT work unless the user clicks on the
aforementioned button that turns Me.AllowEdits back to true.

Obviously that isn’t acceptable, so I placed the following line in the
OnClick event of the Combo Box but it does NOT work. I’m mystified.

Me.AllowEdits = True

What am I doing wrong?

With AllowEdits = False, you can't update the combo box at all (even though
it's unbound, blast it!), so its Click event won't fire. What you can do
instead is leave AllowEdits = True, but lock all the controls except the
combo box, by setting their Locked property to True. Your button could then
unlock them all when you really need to edit them.

 

[Robert T]

Dirk:

Thanks for explaining why my code didn't work. As for your other suggestion,
I'll probably need help in writing the code to unlock the fields.

Thanks,
Robert

 

[Dirk Goldgar]

Dirk:

Thanks for explaining why my code didn't work. As for your other
suggestion,
I'll probably need help in writing the code to unlock the fields.

Here's a function you can put into a standard module and call to lock or
unlock all bound controls on a form:

'------- start of code -------
Public Function fncLockUnlockControls(frm As Form, LockIt As Boolean)

' Lock or unlock all data-bound controls on form <frm>,
' depending on the value of <LockIt>: True = lock; False = unlock.

On Error GoTo Err_fncLockUnlockControls

Const conERR_NO_PROPERTY = 438

Dim ctl As Control

For Each ctl In frm.Controls

With ctl
If Left(.ControlSource & "=", 1) <> "=" Then
.Locked = LockIt
End If
End With
Skip_Control: ' come here from error if no .ControlSource property
Next ctl

Exit_fncLockUnlockControls:
Exit Function

Err_fncLockUnlockControls:
If Err.Number = conERR_NO_PROPERTY Then
Resume Skip_Control
Else
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_fncLockUnlockControls
End If

End Function
'------- end of code -------

You might call it to lock the controls in the form's Current event:

Private Sub Form_Current()

fncLockUnlockControls Me, True

End Sub

And your command button to unlock a record for editing could have a Click
event procedure like this:

Private Sub cmdUnlock_Click()

fncLockUnlockControls Me, True

End Sub

Do you see how that works? Remember that the function itself should be
stored in a standard module, and remember that the module itself should not
be named the same as the function.

 

[Dirk Goldgar]

And your command button to unlock a record for editing could have a Click
event procedure like this:

Private Sub cmdUnlock_Click()

fncLockUnlockControls Me, True

End Sub

Argh! That one should have been

fncLockUnlockControls Me, False

*not* True.

 

[Robert T]

Dirk:

fncLockUnlockControls Me, False

*not* True.

Is the above supposed to go on 1 line or 2 lines?

Thanks,
Robert

 

[Dirk Goldgar]

Dirk:

fncLockUnlockControls Me, False

*not* True.

Is the above supposed to go on 1 line or 2 lines?

Only that one indented line, "fncLockUnlockControls Me, False", is intended
to go in the event procedure. Everything else was commentary.

 

[Robert T]

Thanks for all of your help.

Robert

:

 

[Devon]

Dirk, you were correct on all points. I have made the update to the Module
code in addition to setting the VB option "Require Variable Declaration".

Works perfectly now. Thanks for your time and expertise.

Devon

 

[Dirk Goldgar]

I have made the update to the Module
code in addition to setting the VB option "Require Variable Declaration".

Great! That;s going to solve you a lot of time in debugging.

Works perfectly now. Thanks for your time and expertise.

You're welcome.