Controlling Allow Edits Property

[Peter Hallett]

I have a form and sub-form, where the AllowEdits property of the former is
set True (and therefore inherited by the sub-form on opening). If the first
data entry is made to the main form then the need is to reset the AllowEdits
property of the sub-form to False, and vice versa – i.e. if the first data
entry is made to the sub-form then the AllowEdits property of the main form
is to be set False. The On Dirty property provides a convenient way of
actioning the first data entry but the compiler is wholly unco-operative when
it comes to changing the AllowEdits property. Thus, although it recognizes
‘Me.sfrm_Sub_Form’ and happily returns the correct sub-form name in response
to, say, ‘Me.sfrm_Sub_Form.Name’ it fails to acknowledge
‘Me.sfrm_Sub_Form.AllowEdits’ as valid, although it has no difficulty with
‘Me.AllowEdits’. Either I have the syntax wrong or the compiler is telling
me that I am not entitled to change the AllowEdits property of the sub-form
from the main form and, possibly, vice versa. I suspect that the latter is
the case but would someone be kind enough to enlighten me and perhaps suggest
a fix? The alternative of individual control locking is considerably less
attractive.

 

[rm]

Me.sForm.SetFocus
Form_sForm.AllowEdits = False

 

[Peter Hallett]

Have I been suffering under a misapprehension? It is a while since I
re-asigned the Allow properties on forms and sub-forms but I thought that the
sub-form acquired the form's settings. Certainly one partner inherits the
other's 'Allow' values, so it is pointless trying to set them independently
in form design, but your comments have prompted me to re-examine my earlier
conclusions.

 

[rm]

I created Form1. Placed text1 on the form. Placed command 1 on the
same.

Created sForm with Text2 on the sub form.

Private Sub Command1_Click()

Me.sForm.SetFocus
Form_sForm.AllowEdits = False

End Sub

Text1 is editable.
Text2 is not.

 

[Peter Hallett]

Thanks. That's very useful. A couple of issues have been raised and I am
just in the process of implementing the suggestions. I'll report back if
anything further of interest comes to light. In the mean time, I am very
grateful for the help. As Linq Adams observed, there is always more ways
than one of skinning a cat but it is so much easier if someone will lend you
a sharp knife.

 

[Peter Hallett]

A couple of points on the approach you suggested, which I have now had chance
to test in a bit more detail:–

1. I have not seen the ‘Form_’ syntax used before. It clearly works but is
there any more information on this construction?

2. There appears to be a flaw in the code offered. If the focus has to be
shifted to the sub-form, on data entry to the main form, in order to set
AllowEdits false on the sub-form, then no further input can be made to the
main form controls until the focus is shifted back. But we cannot do that
whenever the sub-form has the focus or no input could be made to the
sub-form. The problem is symmetrical. It appears to be a vicious circle,
from which I cannot at the moment see any simple escape, although,
admittedly, I have not spent a lot of time on it yet.

It would seem that the only method likely to work relatively
straightforwardly is one that retains the focus in the main form while
changing the properties of the sub-form and vice versa. I can lock sub-form
controls from the main form, and main form controls from the sub-form, for
example, but that means explicitly identifying each relevant control. That
is not too bad when there are not a lot of controls involved but it is a lot
clumsier than globally setting/resetting AllowEdits and, at the moment, I
cannot see how to do that without running into serious difficulties. Have I
missed something obvious?

 

[Peter Hallett]

I report these results without claiming to fully understand all of them:–
1. Rechecking the effects of changing ‘AllowEdits’ combinations on a form
and sub-form yielded the following:–
(a) With AllowEdits set ‘Yes’ for both main and sub-forms, edits are, hardly
surprisingly, permitted on both.
(b) With AllowEdits set ‘No’ for the main form and ‘Yes’ for the sub-form,
no edits are possible on either main or sub-form.
(c) With AllowEdits set ‘Yes’ for the main form and ‘No’ for the sub-form,
edits are possible on the main form but not on the sub-form.
(d) With AllowEdits set ‘No’ for both forms, again hardly surprisingly, no
edits are possible on either form.

This is not quite what I expected but appears to show that a ‘No’ setting
for the main form overrides that for the sub-form – ie the main form controls
the sub-form, in certain circumstances, and not the other way around.

2. The required form behaviour that initiated this thread – that any entry
on the main form inhibits data entry to the sub-form, and vice versa – has
now been achieved, relatively concisely but at the expense of a rather odd
mix of syntax.
(a) The command ‘Form_sfrm_Sub_Form.AllowEdits = False’, run in the ‘On
Dirty’ event of the main form, prohibits edits to the sub-form. There
appears to be no need to shift the focus to the sub-form, thus avoiding a
major problem perceived earlier. Although my tests have been far from
exhaustive, I have been unable to find an alternative statement of the above
command, such as ‘Me.sfrm_Sub_Form.AllowEdits = False’, that is not rejected
by the VBA compiler or fails at run-time.
(b) It does not appear possible to use this technique symmetrically.
Neither a matching On Dirty event, nor an ‘AllowEdits’ setting command
similar to that quoted in (a), could be made to work in the sub-form.
Surprisingly, however, the command ‘Me.Parent.AllowEdits = False’, run by the
After Update event of the first control in the sub-form did work.
3. I pass on these observations in the hope that they might be of use to
others but I am unable to offer any further analysis of the code. Someone
more knowledgeable might, however, be able to throw further light on the
rather idiosyncratic techniques I have been forced to adopt and/or suggest
improvements. In the mean time, I am very grateful for the advice received.
I had not previously encountered the ‘Form_sfrm_Sub_Form.AllowEdits = False’
syntax, for example, but, without it my aim could not have been achieved.