Record Editing

[remmons]

I have a form with subforms containing data linked by a primary key. I would
like to keep the user from making changes to the records unless they enter a
password. My problem is that when I make the master form not editable (using
recordset type = snapshot or allowedits = false) any of the subforms that
don't contain data appear blank. The controls don't even show up. The
master form also contains some data so notlocking it down is not an option
either. Is there any way around this or am I missing something? Thanks for
your help!

 

[Ken Sheridan]

First set the Tag property of each relevant control in the form and subform
you want to prevent the user from editing to:

LockMe

Then in the form's Open event procedure put:

Dim frm As Form
Dim ctrl As Control

For Each ctrl In Me.Controls
If ctrl.Tag = "LockMe" Then
ctrl.Locked = True
ctrl.Enabled = False
End If
Next ctrl

Set frm = Me.YourSubformControl.Form

For Each ctrl In frm.Controls
If ctrl.Tag = "LockMe" Then
ctrl.Locked = True
ctrl.Enabled = False
End If
Next ctrl

where YourSubformControl is the name of the subform control in the parent
form's Controls collection which houses the subform.

By both locking and disabling the controls their appearance remains
unchanged but the user cannot even move focus to them. Any controls not
tagged as LockMe will be unaffected, so if you have unbound controls such as
a navigational combo box, which you want available to all users these will
still operate.

In the procedure executed following a user's entry of a correct password do
the same, but this time setting the Locked property to False and the Enabled
property to True.

The form's recordset must be updatable and the AllowEdits property True of
course, but you'll presumably want to set its AllowDeletions property to
False by default, and only to True if the user enters a correct password, and
possibly not even then.

Ken Sheridan
Stafford, England

 

[remmons]

It looks like this will work perfectly! Thank you so much!

 

[remmons]

I'm sorry, I have one more issue with this. As the code begins to loop
through the controls, the control on the subform that has the focus will not
allow me to change its enabled property. I tried creating a dummy control
and setting the focus to that but that doesn't seem to work either. I may be
being too particular about this but, I have several subforms and I'm trying
not to have to fully qualify each subform before running the for each loop to
search for the tag property on the controls. Here's a little snippet of the
code I'm using:
frmEditProjects is the form, frmProjects is the first subform, and the
tabcontrol resides there with more subforms on each tab. By the way, there
are a couple of subforms within those subforms.

Dim ctl As Control
Dim subctl As Control
Dim nextsubctl As Control
Dim strSubFormName As String
Dim strNextSubFormName As String

For Each ctl In Form_frmProjects.Controls
If ctl.Tag = "LockMe" Then
Form_frmProjects.TxtSetFocus.SetFocus
ctl.Locked = True
ctl.Enabled = False
End If
Select Case ctl.ControlType
Case acSubform
strSubFormName = ctl.Name
ctl.SetFocus
For Each subctl In
Form_frmEditProjects.frmProjects.Form(strSubFormName).Controls
If subctl.Tag = "LockMe" Then
subctl.Locked = True
subctl.Enabled = False
End If
Select Case subctl.ControlType
Case acSubform
subctl.SetFocus
strNextSubFormName = subctl.Name
For Each nextsubctl In
Form_frmProjects.Form(strSubFormName).Form(strNextSubFormName).Controls
'For Each subthreectl In
Form_frmEditProjects.frmProjects.Form(strSubFormName).Form(strNextSubFormName).Controls
If nextsubctl.Tag = "LockMe" Then
nextsubctl.Locked = True
nextsubctl.Enabled = False
End If
Next nextsubctl
End Select
Next subctl
End Select
Next ctl
End Sub