Locking a form

[BruceM]

I have a purchase order incorporated into a vendor database. It's a typical
configuration, with a PO table and a related Details table, with a form and
subform used for entering information. This is on one tab of a form with a
tab control. The other tab is for approvals (Financial, etc.). The way it
needs to work is that as soon as an approval has been entered (the Approval
field is no longer null), the PO information is locked to prevent changes.
The approval is entered by a user clicking a command button that inserts
CurrentUser() into the Approval field.

I can set the Tab property of controls that need to be locked to, say, 99,
then use something like the following to accomplish what I need:

If Not IsNull(Me.txtFinanceApproval) And Not
IsNull(Me.txtProductionApproval) Then
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = 99 Then
ctl.Locked = True
End If
Next ctl
End If

Controls associated with approvals (i.e. the text boxes into which
CurrentUser() is inserted) do not have 99 as the Tag property. This allows
approvals to be completed after the rest of the PO is locked. Once all
approvals have been completed I will set Allow Edits and Allow Deletions to
No.
The form's Current event is one place for all of this to occur (either the
partial lockdown of controls with the tag 99 or preventing any edits or
deletions at all). The code to lock controls could be a public sub so that
it can also be run from the command button that inserts CurrentUser as
described above, if needed.
However, I don't know if the form's Current event is the best way to handle
this. Before committing to this route I would like to hear what others have
to say about whether there is a better or more efficient way. I feel like
I'm overlooking something.

 

[Allen Browne]

Form_Current makes perfect sense.

If you want to lock it the instant the record is saved, in Form_AfterUpdate:
Call Form_Current

There are some side effects of setting AllowEdits to No:
a) Even unbound controls are disabled (e.g. if you use these for navigation
or filtering.)

b) Subforms are also disabled. (If the form is opened with AllowEdits set to
No, setting it to Yes may not enable the subforms.)

c) If AllowAdditions is No, and the form is filtered so it contains no
records, its detail section goes completely blank. (You can avoid this by
leaving AllowAdditions as Yes, and cancelling Form_BeforeInsert instead.)

My personal preference is therefore to set the Locked property of the
relevant controls rather than the AllowEdits property of the form. Example:
Locking bound controls on a form and subforms
at:
http://allenbrowne.com/ser-56.html
The code calls itself to handle subforms.
It also accepts an exception list (a list of controls that should *not* be
locked/unlocked) rather than reading the Tab property of each one.

 

[BruceM]

Thanks for the feedback. I hadn't realized those limitation of AllowEdits
and AllowAdditions being set to No. I use a combo box to filter the
records, so I likely would have been perplexed when that stopped working. I
want the user to be able to add records (new purchase orders), so the
AllowAdditions limitation you mentioned will not be an issue since I always
want to allow additons; however, it's good to know. Are there any problems
with disallowing deletions? I don't think it matters, since I have no
record selectors or delete button (or other delete code) on the form, and
there will be no tool bar or menu bar, but maybe I have overlooked
something.

Thanks for the link. Since I don't want to toggle the Locked property, but
rather to allow or disallow it based on whether certain fields contain
values, I won't be using it in this case. It looks like it could be useful
in other projects, but when I went to bookmark it I discovered it was
already there. I guess I should study my list of IE Favorites more often
when questions arise. As it turns out the Tag property works well. I added
the code to a public sub, which is called from the form's Current event and
from a few other places.

This leads to a question. I populate a text box with CurrentUser() by
clicking a command button. However, the AfterUpdate event for that text box
does not run. I want to call the sub that locks the controls if certain
criteria are met, but I can't use AfterUpdate for that. Instead, I call the
sub at the end of the code in the command button's Click event (the command
button that inserts CurrentUser()). Help informs me that changing a control
by VBA does not trigger the AfterUpdate event, but then it goes on to say:
"To run a macro or event procedure when this event occurs, set the
AfterUpdate property to the name of the macro or to [Event Procedure]."
I can't make any sense of that instruction.

I studied your code for locking bound controls. It took a while, as I
wanted to work through some things I didn't immediately understand (the use
of LBound and UBound, for instance). I may have specific questions when I
try to implement the code in a future project, but for now I'm curious about
one particular thing. It may be that I am unknowingly asking a very complex
question, in which case please say so, but here it is. The question is
based on this part of the code in your link:

Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox,
acOptionButton, acToggleButton
'Lock/unlock these controls if bound to fields.
bSkip = False
For lngI = LBound(avarExceptionList) To
UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If HasProperty(ctl, "ControlSource") Then
If Len(ctl.ControlSource) > 0 And Not ctl.ControlSource
Like "=*" Then
If ctl.Locked <> bLock Then
ctl.Locked = bLock
End If
End If
End If
End If

I had thought that Next sends the code back to For to see if the control is
on the exception list, until all of the controls have been checked.
However, if so, how does the part of the code following Next loop through
the controls? What causes the code to loop if not the Next line? I'm
afraid this is a naive question, but I'm not following something I would
really like to understand.

 

[Allen Browne]

No problems with disallowing deletions.

You can set the Locked property of the controls conditionally. It just means
you will need to set the value of the boolean bLock based on the conditions
you have in mind.

As you found the AfterUpdate event of a text box does not fire if you assign
a value programmatically. But you can call the event after assiging the
value. Example:
Me.Text1 = CurrentUser()
Call Text1_AfterUpdate

The last argument of LockBoundControls() is a ParamArray. That means you can
pass in any number of arguments, and they become an array. LBound() is
usually 0. UBound() is the upper bound of the array. We use that since we
don't know how many items the array contains.

The code you asked about has a loop within a loop. It starts by looping
through each control. For each one, it loops through the names in the array
to see if it finds a match. The array contains the names of controls that
should *not* be locked/unlocked, so if the name of the control is found in
the array, we skip that control. The inner "Next" therefore refers to the
next name in the array, while the outer "Next" (near the end of the routine)
moves on to the next control.

Good questions: the main point of providing the code in the web pages is an
an example for you to learn from, so it's good to see you are doing more
than merely a copy'n'paste.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the feedback. I hadn't realized those limitation of AllowEdits
and AllowAdditions being set to No. I use a combo box to filter the
records, so I likely would have been perplexed when that stopped working.
I want the user to be able to add records (new purchase orders), so the
AllowAdditions limitation you mentioned will not be an issue since I
always want to allow additons; however, it's good to know. Are there any
problems with disallowing deletions? I don't think it matters, since I
have no record selectors or delete button (or other delete code) on the
form, and there will be no tool bar or menu bar, but maybe I have
overlooked something.

Thanks for the link. Since I don't want to toggle the Locked property,
but rather to allow or disallow it based on whether certain fields contain
values, I won't be using it in this case. It looks like it could be
useful in other projects, but when I went to bookmark it I discovered it
was already there. I guess I should study my list of IE Favorites more
often when questions arise. As it turns out the Tag property works well.
I added the code to a public sub, which is called from the form's Current
event and from a few other places.

This leads to a question. I populate a text box with CurrentUser() by
clicking a command button. However, the AfterUpdate event for that text
box does not run. I want to call the sub that locks the controls if
certain criteria are met, but I can't use AfterUpdate for that. Instead,
I call the sub at the end of the code in the command button's Click event
(the command button that inserts CurrentUser()). Help informs me that
changing a control by VBA does not trigger the AfterUpdate event, but then
it goes on to say:
"To run a macro or event procedure when this event occurs, set the
AfterUpdate property to the name of the macro or to [Event Procedure]."
I can't make any sense of that instruction.

I studied your code for locking bound controls. It took a while, as I
wanted to work through some things I didn't immediately understand (the
use of LBound and UBound, for instance). I may have specific questions
when I try to implement the code in a future project, but for now I'm
curious about one particular thing. It may be that I am unknowingly
asking a very complex question, in which case please say so, but here it
is. The question is based on this part of the code in your link:

Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox,
acOptionButton, acToggleButton
'Lock/unlock these controls if bound to fields.
bSkip = False
For lngI = LBound(avarExceptionList) To
UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If HasProperty(ctl, "ControlSource") Then
If Len(ctl.ControlSource) > 0 And Not ctl.ControlSource
Like "=*" Then
If ctl.Locked <> bLock Then
ctl.Locked = bLock
End If
End If
End If
End If

I had thought that Next sends the code back to For to see if the control
is on the exception list, until all of the controls have been checked.
However, if so, how does the part of the code following Next loop through
the controls? What causes the code to loop if not the Next line? I'm
afraid this is a naive question, but I'm not following something I would
really like to understand.

Form_Current makes perfect sense.

If you want to lock it the instant the record is saved, in
Form_AfterUpdate:
Call Form_Current

There are some side effects of setting AllowEdits to No:
a) Even unbound controls are disabled (e.g. if you use these for
navigation or filtering.)

b) Subforms are also disabled. (If the form is opened with AllowEdits set
to No, setting it to Yes may not enable the subforms.)

c) If AllowAdditions is No, and the form is filtered so it contains no
records, its detail section goes completely blank. (You can avoid this by
leaving AllowAdditions as Yes, and cancelling Form_BeforeInsert instead.)

My personal preference is therefore to set the Locked property of the
relevant controls rather than the AllowEdits property of the form.
Example:
Locking bound controls on a form and subforms
at:
http://allenbrowne.com/ser-56.html
The code calls itself to handle subforms.
It also accepts an exception list (a list of controls that should *not*
be locked/unlocked) rather than reading the Tab property of each one.

 

[BruceM]

Allen,

Thanks again for the reply and for the explanations. I was away for a few
days, which is why I didn't respond sooner.
As it turns out I think I was doing what you suggest in terms of calling the
AfterUpdate event for the text box to which a value is assigned
programatically, execpt I was calling a separate public sub rather than a
control's event.
I think I see what's happening with the Next business and the looping. The
way I understand it is that the inner Next (in the Case that includes
controls other than subforms, for instance) runs until there are no more
controls in the Array, in which case an error is generated at the Next line.
When that happens the code skips to the On Error Resume Next line, which
sends the code back to the line of code after the inner Next (If Not bSkip
Then etc.). Thereafter the HasProperty function generates the error to send
the code back to the following line of code. Something like that. I'll
have to go back and study the code a few more times. I expect it will sink
in after a while. I may try a message box or Debug.Print or something to
try to identify when errors are occurring, but that will have to wait until
a few things on my desk are cleared away.
I try to understand code rather than just copying and pasting, but of course
there are times when copy and paste solves an immediate problem, so I accept
it and come back to it later. For instance, James Brooks has some code for
group page numbering in a report. I remembered that it uses arrays, so I
went back to it this morning for a closer look, but I am still a ways from
understanding it, although I understand it better now than I did when I
first incorporated it, now that I know a little bit about the nature of an
array.
Anyhow, thanks again for your help, and for the valuable information you
make available on your web site.

No problems with disallowing deletions.

You can set the Locked property of the controls conditionally. It just
means you will need to set the value of the boolean bLock based on the
conditions you have in mind.

As you found the AfterUpdate event of a text box does not fire if you
assign a value programmatically. But you can call the event after assiging
the value. Example:
Me.Text1 = CurrentUser()
Call Text1_AfterUpdate

The last argument of LockBoundControls() is a ParamArray. That means you
can pass in any number of arguments, and they become an array. LBound() is
usually 0. UBound() is the upper bound of the array. We use that since we
don't know how many items the array contains.

The code you asked about has a loop within a loop. It starts by looping
through each control. For each one, it loops through the names in the
array to see if it finds a match. The array contains the names of controls
that should *not* be locked/unlocked, so if the name of the control is
found in the array, we skip that control. The inner "Next" therefore
refers to the next name in the array, while the outer "Next" (near the end
of the routine) moves on to the next control.

Good questions: the main point of providing the code in the web pages is
an an example for you to learn from, so it's good to see you are doing
more than merely a copy'n'paste.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the feedback. I hadn't realized those limitation of
AllowEdits and AllowAdditions being set to No. I use a combo box to
filter the records, so I likely would have been perplexed when that
stopped working. I want the user to be able to add records (new purchase
orders), so the AllowAdditions limitation you mentioned will not be an
issue since I always want to allow additons; however, it's good to know.
Are there any problems with disallowing deletions? I don't think it
matters, since I have no record selectors or delete button (or other
delete code) on the form, and there will be no tool bar or menu bar, but
maybe I have overlooked something.

Thanks for the link. Since I don't want to toggle the Locked property,
but rather to allow or disallow it based on whether certain fields
contain values, I won't be using it in this case. It looks like it could
be useful in other projects, but when I went to bookmark it I discovered
it was already there. I guess I should study my list of IE Favorites
more often when questions arise. As it turns out the Tag property works
well. I added the code to a public sub, which is called from the form's
Current event and from a few other places.

This leads to a question. I populate a text box with CurrentUser() by
clicking a command button. However, the AfterUpdate event for that text
box does not run. I want to call the sub that locks the controls if
certain criteria are met, but I can't use AfterUpdate for that. Instead,
I call the sub at the end of the code in the command button's Click event
(the command button that inserts CurrentUser()). Help informs me that
changing a control by VBA does not trigger the AfterUpdate event, but
then it goes on to say:
"To run a macro or event procedure when this event occurs, set the
AfterUpdate property to the name of the macro or to [Event Procedure]."
I can't make any sense of that instruction.

I studied your code for locking bound controls. It took a while, as I
wanted to work through some things I didn't immediately understand (the
use of LBound and UBound, for instance). I may have specific questions
when I try to implement the code in a future project, but for now I'm
curious about one particular thing. It may be that I am unknowingly
asking a very complex question, in which case please say so, but here it
is. The question is based on this part of the code in your link:

Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox,
acOptionButton, acToggleButton
'Lock/unlock these controls if bound to fields.
bSkip = False
For lngI = LBound(avarExceptionList) To
UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If HasProperty(ctl, "ControlSource") Then
If Len(ctl.ControlSource) > 0 And Not
ctl.ControlSource Like "=*" Then
If ctl.Locked <> bLock Then
ctl.Locked = bLock
End If
End If
End If
End If

I had thought that Next sends the code back to For to see if the control
is on the exception list, until all of the controls have been checked.
However, if so, how does the part of the code following Next loop through
the controls? What causes the code to loop if not the Next line? I'm
afraid this is a naive question, but I'm not following something I would
really like to understand.

Form_Current makes perfect sense.

If you want to lock it the instant the record is saved, in
Form_AfterUpdate:
Call Form_Current

There are some side effects of setting AllowEdits to No:
a) Even unbound controls are disabled (e.g. if you use these for
navigation or filtering.)

b) Subforms are also disabled. (If the form is opened with AllowEdits
set to No, setting it to Yes may not enable the subforms.)

c) If AllowAdditions is No, and the form is filtered so it contains no
records, its detail section goes completely blank. (You can avoid this
by leaving AllowAdditions as Yes, and cancelling Form_BeforeInsert
instead.)

My personal preference is therefore to set the Locked property of the
relevant controls rather than the AllowEdits property of the form.
Example:
Locking bound controls on a form and subforms
at:
http://allenbrowne.com/ser-56.html
The code calls itself to handle subforms.
It also accepts an exception list (a list of controls that should *not*
be locked/unlocked) rather than reading the Tab property of each one.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a purchase order incorporated into a vendor database. It's a
typical configuration, with a PO table and a related Details table, with
a form and subform used for entering information. This is on one tab of
a form with a tab control. The other tab is for approvals (Financial,
etc.). The way it needs to work is that as soon as an approval has been
entered (the Approval field is no longer null), the PO information is
locked to prevent changes. The approval is entered by a user clicking a
command button that inserts CurrentUser() into the Approval field.

I can set the Tab property of controls that need to be locked to, say,
99, then use something like the following to accomplish what I need:

If Not IsNull(Me.txtFinanceApproval) And Not
IsNull(Me.txtProductionApproval) Then
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = 99 Then
ctl.Locked = True
End If
Next ctl
End If

Controls associated with approvals (i.e. the text boxes into which
CurrentUser() is inserted) do not have 99 as the Tag property. This
allows approvals to be completed after the rest of the PO is locked.
Once all approvals have been completed I will set Allow Edits and Allow
Deletions to No.
The form's Current event is one place for all of this to occur (either
the partial lockdown of controls with the tag 99 or preventing any
edits or deletions at all). The code to lock controls could be a
public sub so that it can also be run from the command button that
inserts CurrentUser as described above, if needed.
However, I don't know if the form's Current event is the best way to
handle this. Before committing to this route I would like to hear what
others have to say about whether there is a better or more efficient
way. I feel like I'm overlooking something.