Best method to selectively lock/disable controls on a form.

[Saintor]

What I do now is I put a value in the tag property, and using the
form_current event, I run through all controls properties until the ones
with the required tag value are met. Sound OK in theory, but it is ...
slow.

I thought having a table with the name, type of controls and put my 'tag
value' there. At the opening of the form, I would retrieve a recordset from
the table, and perform a subroutine on the current event to lock/unlock
controls witht ehstored value.

But before spending time trying it, I wonder if anybody had to face this
situation and how to deal with it the best way. TIA.

[Bess Eaton]

"Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
news:t2*********************@wagner.videotron.net. ..

What I do now is I put a value in the tag property, and using the
form_current event, I run through all controls properties until the ones
with the required tag value are met. Sound OK in theory, but it is ...
slow.

I thought having a table with the name, type of controls and put my 'tag
value' there. At the opening of the form, I would retrieve a recordset from the table, and perform a subroutine on the current event to lock/unlock
controls witht ehstored value.

But before spending time trying it, I wonder if anybody had to face this
situation and how to deal with it the best way. TIA.

Tags are metadata and are therefore goofy and inefficient. Enable-disable,
hide-unhide, lock-unlock controls from within the target form only.

Most forms are unique in that no two have the same collection of controls.
A universal solution would be therefore very complicated to implement, if
not impossible. Walking a recordset of control states is inefficient and
unnecessary.

Create a global Boolean variable to represent the state of the controls in
the form, whether the controls are locked or unlocked or whether the
controls are hidden or unhidden, etc. gvIsOn = True, or something similar.

Creat TWO procedures to set the controls whose states need to be changed.

***
Public Sub TurnOff()
'Disables or hides form and subform controls.
On Error GoTo ErrorHandler
cmbEID1.SetFocus
txtIncdntEmp.Enabled = False
txtIncdntEmp.Locked = False
cmbEID2.Enabled = False
txtSuperEmp.Enabled = False
txtSuperEmp.Locked = False
txtIncdntDate.Enabled = False
txtIncdntTime.Enabled = False
cmbDeptID.Enabled = False
cmbOPID.Enabled = False
cmbTask.Enabled = False
cmbZID.Enabled = False
ckbCI.Enabled = False
ckbClosed.Enabled = False
optgrpClass.Enabled = False
bxHS.Visible = False
bxE.Visible = False
bxQ.Visible = False
cmbTypeHS.Enabled = False
cmbBodyPart.Enabled = False
txtLostDay.Enabled = False
txtExpAmt.Enabled = False
optgrpExpUnit.Enabled = False
cmbTypeE.Enabled = False
txtInvolve.Enabled = False
txtEMSTrack.Enabled = False
cmbTypeQ.Enabled = False
txtCustomer.Enabled = False
txtOrderNum.Enabled = False
txtProductLot.Enabled = False
sbfIncident1.Visible = False
sbfIncident2.Visible = False
txtIncdntDescript.Enabled = False
txtCostAct.Enabled = False
txtCostCalc.Enabled = False
txtCause.Enabled = False
txtICAT.Enabled = False
optgrpSeverity.Enabled = False
optgrpProbability.Enabled = False
optgrpCAW.Enabled = False
txtFCAT.Enabled = False
txtCAEmp.Enabled = False
txtCAEmp.Locked = False
cmbCAEID.Enabled = False
txtCADate.Enabled = False
optgrpExternal.Enabled = False
txtENDate.Enabled = False
txtENEmp.Enabled = False
txtENEmp.Locked = False
cmbENEID.Enabled = False
txtRMEmp.Enabled = False
txtRMEmp.Locked = False
cmbRMID.Enabled = False
txtDateReview.Enabled = False
ckbxIncludeCTP.Enabled = False
tpg1.Enabled = False
sbfFormal.Visible = False
lblStart.Visible = True
gvIsOn = False 'Sets global flag.
ExitRoutine:
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume ExitRoutine
End Sub
***

Public Sub TurnOn()
'Enables or unhides form and subform controls.
On Error GoTo ErrorHandler
txtIncdntEmp.Enabled = True
txtIncdntEmp.Locked = True
cmbEID2.Enabled = True
txtSuperEmp.Enabled = True
txtSuperEmp.Locked = True
txtIncdntDate.Enabled = True
txtIncdntTime.Enabled = True
cmbDeptID.Enabled = True
cmbOPID.Enabled = True
cmbTask.Enabled = True
cmbZID.Enabled = True
ckbCI.Enabled = True
ckbClosed.Enabled = True
optgrpClass.Enabled = True
txtIncdntDescript.Enabled = True
txtCostAct.Enabled = True
txtCostCalc.Enabled = True
txtCause.Enabled = True
txtICAT.Enabled = True
optgrpSeverity.Enabled = True
optgrpProbability.Enabled = True
optgrpCAW.Enabled = True
optgrpExternal.Enabled = True
txtRMEmp.Enabled = True
txtRMEmp.Locked = True
cmbRMID.Enabled = True
txtDateReview.Enabled = True
ckbxIncludeCTP.Enabled = True
tpg1.Enabled = True
sbfFormal.Visible = True
lblStart.Visible = False
gvIsOn = True 'Sets global flag.
ExitRoutine:
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume ExitRoutine
End Sub

***

In the On Current event of the form, apply the criteria you want to use to
activate or deactivate the form's specific controls. In the example below,
the form is mostly deactivated when a new record is about to be started.
But if the form is displaying an existing record, then the form is fully
activated. By checking the value of the global variable gvIsOn, we avoid
turning the form on or off unnecessarily when a user pages through the
records.

*** Snippet***

If IsNull([MyID]) Then 'New record.
TurnOff
Else 'Existing record. Activate the form only if needed.
If gvIsOn = False Then TurnOn
End If
*** Snippet***
The principle downside to this solution is that each time a control is added
or removed from the form, the code must be updated to reflect the design
change. On the other hand, the code is exceptionally fast becuase it uses a
minimum of resources.

[Don Leverton]

Hi,

You could loop thru a recordset to obtain these settings as you said.

The "cons" (well, the "pros", too) of that approach is that you would have
to open the table directly, or from a different form in order to
select/designate these properties...

I decided to give this a try, and this is what I came up with ...a table
named:
**tblEnableControls**
ControlName - Text
ControlEnabled - Yes/No
ControlLocked - Yes/No
TypeOfControl - Text
(This field actually fills with the Constant rather than the actual readable
value, unfortunately. i.e. "106" instead of "vbCheckbox") Darn!!

Then I used DLookup to find the setting in this table, and used that to set
the control properties.

I designed a seperate Sub which I can call in response to more than one
event. I wanted to be able to call it from the Form Load event, as well as
from a command button

I also realized that some ControlTypes (i.e. Labels) don't have Enabled /
..Locked properties, so you'll have have to include code to test and exclude
them. This code only sets the values for TextBoxes or CheckBoxes, using an
If statement. If there were a lot of other control types on your form, you
might want to use Select Case instead.

***********************************************
Option Compare Database
Option Explicit

'-----------------------------------
Private Sub cmdSetEnabled_Click()
sSetControlProperties
End Sub

'-----------------------------------
Private Sub Form_Load()
sSetControlProperties
End Sub

'-----------------------------------
Public Sub sSetControlProperties()

Dim ctl As Control
Dim strName

For Each ctl In Me.Controls
strName = ctl.Name
If ctl.ControlType = 106 Or ctl.ControlType = 109 Then
'Checkbox = 106, Textbox = 109
ctl.Enabled = DLookup("[ControlEnabled]", "tblEnableControls",
"([ControlName] = '" & strName & "')")
ctl.Locked = DLookup("[ControlLocked]", "tblEnableControls",
"([ControlName] = '" & strName & "')")
End If
Next ctl

End Sub

'-----------------------------------
Private Sub cmdPopulateTable_Click()
'This code populates "tblEnableControls" with
'the names and control types of all of the
'controls on this form. (Yeah ... I'm lazy.)

Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim rst As DAO.Recordset
Set rst = MyDB.OpenRecordset("tblEnableControls", dbOpenDynaset)

Dim ctl As Control
Dim strName
For Each ctl In Me.Controls
strName = ctl.Name
With rst
.AddNew
!ControlName = strName
!ControlEnabled = True
!ControlLocked = False
!TypeOfControl = ctl.ControlType
.Update
End With
Next ctl

Set rst = Nothing
Set MyDB = Nothing
End Sub

***********************************************

HTH,
Don
"Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
news:t2*********************@wagner.videotron.net. ..

What I do now is I put a value in the tag property, and using the
form_current event, I run through all controls properties until the ones
with the required tag value are met. Sound OK in theory, but it is ...
slow.

I thought having a table with the name, type of controls and put my 'tag
value' there. At the opening of the form, I would retrieve a recordset from the table, and perform a subroutine on the current event to lock/unlock
controls witht ehstored value.

But before spending time trying it, I wonder if anybody had to face this
situation and how to deal with it the best way. TIA.

[rkc]

"Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
news:t2*********************@wagner.videotron.net. ..

What I do now is I put a value in the tag property, and using the
form_current event, I run through all controls properties until the ones
with the required tag value are met. Sound OK in theory, but it is ...
slow.

I thought having a table with the name, type of controls and put my 'tag
value' there. At the opening of the form, I would retrieve a recordset from the table, and perform a subroutine on the current event to lock/unlock
controls witht ehstored value.

But before spending time trying it, I wonder if anybody had to face this
situation and how to deal with it the best way. TIA.

Even in the simplest of cases I believe the information you have in the
tag property belongs in a table. The information should be read from the
table when the form opens and stored in an object that persists for the life
of the form. Again in the simplest of cases that object could be a simple
collection or array. In more complex cases where the state of a control
changes under more than a single circumstance you might want to spend
some time creating a class that monitors the form and manages the
controls as needed. The worst thing you could do is scatter that logic
all over the form's code module in the control event procedures.

[Saintor]

In what is it that bad? I considered creating the recordset in the form
declarations (header), use a private subroutine and use the unload event to
set it to nothing.

controls as needed. The worst thing you could do is scatter that logic
all over the form's code module in the control event procedures.

[Saintor]

Thanks for your help. But I am a bit afraid of using so much the Dlookup
function. I will give it a try with a copy of the recordset, using
recordsetclone.

For Each ctl In Me.Controls
strName = ctl.Name
If ctl.ControlType = 106 Or ctl.ControlType = 109 Then
'Checkbox = 106, Textbox = 109
ctl.Enabled = DLookup("[ControlEnabled]", "tblEnableControls",
"([ControlName] = '" & strName & "')")
ctl.Locked = DLookup("[ControlLocked]", "tblEnableControls",
"([ControlName] = '" & strName & "')")
End If
Next ctl

End Sub

'-----------------------------------
Private Sub cmdPopulateTable_Click()
'This code populates "tblEnableControls" with
'the names and control types of all of the
'controls on this form. (Yeah ... I'm lazy.)

Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim rst As DAO.Recordset
Set rst = MyDB.OpenRecordset("tblEnableControls", dbOpenDynaset)

Dim ctl As Control
Dim strName
For Each ctl In Me.Controls
strName = ctl.Name
With rst
.AddNew
!ControlName = strName
!ControlEnabled = True
!ControlLocked = False
!TypeOfControl = ctl.ControlType
.Update
End With
Next ctl

Set rst = Nothing
Set MyDB = Nothing
End Sub

***********************************************

HTH,
Don
"Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
news:t2*********************@wagner.videotron.net. ..

What I do now is I put a value in the tag property, and using the
form_current event, I run through all controls properties until the ones
with the required tag value are met. Sound OK in theory, but it is ...
slow.

I thought having a table with the name, type of controls and put my 'tag
value' there. At the opening of the form, I would retrieve a recordset

from

the table, and perform a subroutine on the current event to lock/unlock
controls witht ehstored value.

But before spending time trying it, I wonder if anybody had to face this
situation and how to deal with it the best way. TIA.

[Saintor]

"Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
news:h7*********************@wagner.videotron.net. ..

In what is it that bad? I considered creating the recordset in the form
declarations (header),

Oops. I meant only declaring the recordset variables, creating them in
form_load event.

[rkc]

"Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
news:h7*********************@wagner.videotron.net. ..

In what is it that bad? I considered creating the recordset in the form
declarations (header), use a private subroutine and use the unload event to set it to nothing.

Nothings 'bad' about either method. Tags or table. I just find that keeping
the information in a table is easier to maintain and seems more inline
with the fact that you have a database readily available to store this type
of information. On the other hand, if speed is your major concern, then
hard code everything as was suggested by someone else. Not everything
has to be clever and nothings going to run faster than that.