Conditonal List Processing

[Peter Hallett]

In the heady far-off days of Kernighan & Ritchie’s ‘C’, the Switch statement
(= VBA Select Case) defaulted to ‘fall-through’, unless a Break command was
used to terminate each ‘case’ clause. This allowed considerable flexibility
– a feature of the language. VBA is not so accommodating.

One particularly useful property of ‘fall-though’ was that it allowed
conditional processing of a list. Imagine a situation where a set of form
controls has to be cleared and disabled in a manner determined by the outcome
of an event. Let us assume that the latter returns values 1, 2, 3, 4 …
Ignoring the data clearance commands, for the sake of simplicity, the
‘flushing’ module might be contained in a sub-routine of the form:-

Public Sub Flush (InForm As Form, InStart As Byte)

‘Some initial code, followed by:–

With InForm
.Control_A.Enabled = False
.Control_B.Enabled = False
.Control_C.Enabled = False
…
Label_2:
.Control_D.Enabled = False
.Control_E.Enabled = False
.Control_F.Enabled = False
.Control_G.Enabled = False
.Control_H.Enabled = False
…
Label_3:
.Control_I.Enabled = False
.Control_J.Enabled = False
.Control_K.Enabled = False
.Control_L.Enabled = False
…
Etc.
Etc.

End With

End Sub

This permits disabling to be started from a selected point determined by the
value of InStart. For 1, the default, all the specified controls will be
disabled. If InStart = 2 then clearance will start at Label_2, and so on.
(Such a construction can be used to force a user to complete data entry to a
form in a particular order. This is particularly useful where the permitted
input values for later controls depend upon earlier entries. Should the user
change his mind, part way though data entry, and amend an earlier control,
potentially invalidating later entries, a clearance routine, of the above
type, can be used to delete all subsequent inputs, forcing selective re-input
and hence revalidation against the revised earlier values. (The alternative,
of locking each control following data entry, is not attractive. It
virtually prohibits amendment.)

If VBA permitted fall-though, Select Case would provide a simple solution.
The alternative, however, appears to be the use of the dreaded multiple GoTo
statements in the initial code of the above sub-routine, directing execution
to the appropriate label. The fact that GoTo will not accept parameterized
label addresses makes the thing even messier. It works, but that is the best
that can be said for it. Can anyone suggest a more elegant solution?

 

[Ken Snell MVP]

If I'm understanding your wish correctly, this might work:

Dim varValue As Variant
varValue = VariableWithActualValue
LoopStart:
Select Case varValue
Case Value1
' do something here
varValue = Value2
GoTo LoopStart
Case Value2
' do something here
varValue = Value3
GoTo LoopStart
Case Value3
' do something here
varValue = Value4
GoTo LoopStart
Case Value4
' do something here
End Select

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Marshall Barton]

In the heady far-off days of Kernighan & Ritchie’s ‘C’, the Switch statement
(= VBA Select Case) defaulted to ‘fall-through’, unless a Break command was
used to terminate each ‘case’ clause. This allowed considerable flexibility
– a feature of the language. VBA is not so accommodating.

One particularly useful property of ‘fall-though’ was that it allowed
conditional processing of a list. Imagine a situation where a set of form
controls has to be cleared and disabled in a manner determined by the outcome
of an event. Let us assume that the latter returns values 1, 2, 3, 4 …
Ignoring the data clearance commands, for the sake of simplicity, the
‘flushing’ module might be contained in a sub-routine of the form:-

Public Sub Flush (InForm As Form, InStart As Byte)

‘Some initial code, followed by:–

With InForm
.Control_A.Enabled = False
.Control_B.Enabled = False
.Control_C.Enabled = False
…
Label_2:
.Control_D.Enabled = False
.Control_E.Enabled = False
.Control_F.Enabled = False
.Control_G.Enabled = False
.Control_H.Enabled = False
…
Label_3:
.Control_I.Enabled = False
.Control_J.Enabled = False
.Control_K.Enabled = False
.Control_L.Enabled = False
…
Etc.
Etc.

End With

End Sub

This permits disabling to be started from a selected point determined by the
value of InStart. For 1, the default, all the specified controls will be
disabled. If InStart = 2 then clearance will start at Label_2, and so on.
(Such a construction can be used to force a user to complete data entry to a
form in a particular order. This is particularly useful where the permitted
input values for later controls depend upon earlier entries. Should the user
change his mind, part way though data entry, and amend an earlier control,
potentially invalidating later entries, a clearance routine, of the above
type, can be used to delete all subsequent inputs, forcing selective re-input
and hence revalidation against the revised earlier values. (The alternative,
of locking each control following data entry, is not attractive. It
virtually prohibits amendment.)

If VBA permitted fall-though, Select Case would provide a simple solution.
The alternative, however, appears to be the use of the dreaded multiple GoTo
statements in the initial code of the above sub-routine, directing execution
to the appropriate label. The fact that GoTo will not accept parameterized
label addresses makes the thing even messier. It works, but that is the best
that can be said for it. Can anyone suggest a more elegant solution?

Personally, I never liked the Break statement with Switch.

Seems to me that a sequence of If blocks is more versatile
than Select Case. It may require a bunch of End If
statements, but at least my mind tends to not dwell on them
as clutter.

If x = 2 Then
.Control_D.Enabled = False
End If
If x = 3 Then
.Control_K.Enabled = False
End If

The Case Else statement could be more problematic though.

 

[Stuart McCall]

Personally, I never liked the Break statement with Switch.

Seems to me that a sequence of If blocks is more versatile
than Select Case. It may require a bunch of End If
statements, but at least my mind tends to not dwell on them
as clutter.

If x = 2 Then
.Control_D.Enabled = False
End If
If x = 3 Then
.Control_K.Enabled = False
End If

The Case Else statement could be more problematic though.

I never liked switch/break either. Re your above code, I would write it
thus:

..Control_D.Enabled = Not (x = 2)
..Control_K.Enabled = Not (x = 3)

 

[Marshall Barton]

"Marshall Barton" wrote

I never liked switch/break either. Re your above code, I would write it
thus:

.Control_D.Enabled = Not (x = 2)
.Control_K.Enabled = Not (x = 3)

I tend to write things that way a lot too.

But the way I understood Peter's point, he wanted to avoid
the auto break that is done with the Case statement.

To do it without the If statements would mean using:

.Control_D.Enabled = Not (x = 2)
.Control_K.Enabled = Not (x = 2 Or x = 3)

which can quickly get kind of messy.

 

[tina]

reading your post, Peter, and the other responses, i have to say that this
is probably over my head. but i had a thought...

your example 'C' code grouped the controls under labels, and then started at
a specific label based on a variable value, am i understanding it right? if
so, you might use the Tag property of the controls to assign each one to a
"group". then compare the tag property of each control to the variable to
determine the action, as

Private Sub isLockControls(ByVal i As Integer)

On Error Resume Next

Dim ctl As Control

For Each ctl In Me.Controls
ctl.Enabled = Not (Nz(ctl.Tag, 0) >= i)
Next

End Sub

so controls A - C might be assigned Tag values of 1, controls D - H Tag
value 2, I - L Tag 3, etc. the above is pretty loose, of course, since it
will check labels, command buttons, etc (hence the Resume Next). but if
you're working with specific controls only, like textboxes, you could test
for the typeof control at the beginning of the loop - i just didn't bother,
in my test.

hth

 

[Stuart McCall]

Stuart McCall wrote:

I tend to write things that way a lot too.

But the way I understood Peter's point, he wanted to avoid
the auto break that is done with the Case statement.

To do it without the If statements would mean using:

.Control_D.Enabled = Not (x = 2)
.Control_K.Enabled = Not (x = 2 Or x = 3)

which can quickly get kind of messy.

You're right of course. Hadn't thought it through. Yes the only way to get
proper 'drop-through' is with If statements, as you say. However if there's
only ever one line of code to execute, you could lose all those End If's and
it would be more easily readable.

 

[Marshall Barton]

your example 'C' code grouped the controls under labels, and then started at
a specific label based on a variable value, am i understanding it right? if
so, you might use the Tag property of the controls to assign each one to a
"group". then compare the tag property of each control to the variable to
determine the action, as

Private Sub isLockControls(ByVal i As Integer)

On Error Resume Next

Dim ctl As Control

For Each ctl In Me.Controls
ctl.Enabled = Not (Nz(ctl.Tag, 0) >= i)
Next

End Sub

so controls A - C might be assigned Tag values of 1, controls D - H Tag
value 2, I - L Tag 3, etc. the above is pretty loose, of course, since it
will check labels, command buttons, etc (hence the Resume Next).

That's a good idea, Tina, especially if there are a lot of
controls in each "group".

If there are a lot of controls on the form that should be
ignored, it would be more efficient to use a collection with
only the controls that need to be manipulated.

 

[tina]

loop through a collection, rather than checking the typeof every control on
the form...yes, a better solution. thx Marsh.

 

[Peter Hallett]

Actually, Tina, the example I supplied was a sort of outline VBA, rather than
‘C’. Sorry if I misled you.

In addition,for the sake of simplicity, in my first post, I described the
sub-routine as taking a numeric InStart argument. In my actual code,
however, it takes a string. This makes the calling code more intuitive. For
example, when the originating form is loaded, the clearance sub-routine is
invoked with, say, ‘Flush Me, “OnLoadâ€â€™. After entering a surname, the
sub-routine would be invoked with, perhaps, ‘Flush Me, “AfterSurnameâ€.

Using a sort of hybrid VBA/’C’ code, if this were possible, the resulting
sub-routine would look something like:–

Public Sub Flush (InForm As Form, InStart As String)
With InForm
‘The ‘C’ bit starts here:-
Switch InStart
{ case “First_Stringâ€:
.Control_A.Enabled = False;
.Control_B.Enabled = False;
.Control_C.Enabled = False;
case “Second_Stringâ€:
.Control_D.Enabled = False;
.Control_E.Enabled = False;
.Control_F.Enabled = False;
.Control_G.Enabled = False;
.Control_H.Enabled = False;
Etc.
Etc.
case “Nth_Stringâ€:
.Control_I.Enabled = False;
.Control_J.Enabled = False;
.Control_K.Enabled = False;
.Control_L.Enabled = False;
break;
}
‘And ends here.
End With
End Sub

Having taken on board the comments so far made, it looks as if the best that
can be done to achieve the same ends with VBA is likely to be something like:–

Public Sub Flush (InForm As Form, InStart As String)

‘*************Conversion Code ****************************
Dim bytIndex As Byte

Select Case InStart
Case “FirstStringâ€
bytIndex = 0
Case “SecondStringâ€
bytIndex = 1
Case “ThirdStringâ€
bytIndex = 2
Etc.
Etc.
Case “LastStringâ€
bytIndex = N-1
End Select

‘************************************************************
With InForm
If bytIndex < 1 Then
.Control_A.Enabled = False
.Control_B.Enabled = False
.Control_C.Enabled = False
End If
If bytIndex < 2 Then
.Control_D.Enabled = False
.Control_E.Enabled = False
.Control_F.Enabled = False
.Control_G.Enabled = False
.Control_H.Enabled = False
End If
Etc.
Etc.
If bytIndex < N
.Control_I.Enabled = False
.Control_J.Enabled = False
.Control_K.Enabled = False
.Control_L.Enabled = False
End If

End With
End Sub

The difference between the two versions is clear and much in the ‘hybrid’s’
favour, I suggest. In the ‘C’ version, each case falls through into the next
one until a ‘break’ statement is encountered. (The terminating ‘break’
statement is not actually needed. It was an ANSI ‘C’ convention to include
it after the last case statement.)

In defence of this construction (and it seems to need some!) it allowed a
lot of flexibility. ‘Fall-through’ and ‘non-fall-though’ clauses could be
mixed, for example, simply by including or excluding the break statement.
Apart from allowing more concise code, the ‘C’ solution also obviates the
need for string:numeric conversions. In my humble opinion, the result is
more concise, easier to understand and maintain – and therefore better. I
can’t see much virtue in restricting programming options without very good
reason. Still, that’s water under the bridge.

To dispense with the ‘conversion code’ in the VBA version, InSort needs to
be passed as a numeric variable, as in my first posting, but then the
sub-routine-invoking commands ‘Flush Me, “OnLoadâ€â€™, and ‘Flush Me,
“AfterSurnameâ€â€™ become, perhaps, ‘Flush Me, 0’ and ‘Flush Me, 5’, which are
significantly less intuitive.

There are other weaknesses in the VBA version. If an additional clearance
is needed, after the insertion of a new control, say ‘Address_4’, between the
existing ‘Address_3’ and ‘Town/City’ controls, all the subsequent numbers
have to be reallocated. The problem could, of course, be mitigated by
setting bytIndex in multiples of 10, thus facilitating later interpolation,
and this is what I have done in the version I have now implemented, but it
still represents an overhead that I could do without. Also, however you
define ‘clutter’, the VBA version is not pretty.

Turning to the method for effecting the actual control flushing, your tagged
control solution is interesting. Allied to Marsh’s idea of allocating the
relevant controls to collections, it could make things a lot slicker. The
snag is that I have no idea how to do it. MS Access Help, on collections, is
woefully unhelpful and my so-called Access ‘manuals’ have nothing to say on
the matter. I would need some assistance.

In the absence of this sort of solution, there is a further, as yet
unconsidered, problem, which has been partly hidden by the simplification
applied when formulating the original question. For the sake of
illustration, the ‘flushing’ commands in the examples so far offered have
been reduced to just the disabling statements. In practice, at least two
commands are needed to ‘flush’ each control, namely:-

.Control_N = Null
.Control_N.Enabled = False

And some controls need to be reset slightly differently, e.g.:–

.Control_N = Default value
.Control_N.Enabled = False

It appears to be unnecessary to enable controls before they are assigned a
Null value but in some cases the current value may need to be tested before
taking action, whereupon the above expression becomes:–

If Condition(N) = True Then
.Control_N = Null ‘or default value
.Control_N.Enabled=False
End If

With a lot of controls, significant repetition is involved, suggesting the
use of another sub-routine, or subroutines, to handle the repeated code
fragments but I can’t get that to work. I have so far failed to successfully
pass the controls as an argument, from the sub-routine ‘Sub Flush’ to such a
subsequent sub-routine. Both Form and Control parameters need to be passed
if the sub-sub-routine is to identify the appropriate controls but VBA is
having none of it. The only version the compiler will accept without
complaint does precisely nothing. A ‘tagged collection’ solution would
probably render this sort approach unnecessary but it would nevertheless be
useful to know how to do it. Any thoughts?

loop through a collection, rather than checking the typeof every control on
the form...yes, a better solution. thx Marsh.

 

[tina]

well, a couple thoughts, yes - but i think it wiser to hang back and let the
expert programmers in the thread respond now. i'm more likely to learn than
teach, in this company.

Actually, Tina, the example I supplied was a sort of outline VBA, rather than
'C'. Sorry if I misled you.

In addition,for the sake of simplicity, in my first post, I described the
sub-routine as taking a numeric InStart argument. In my actual code,
however, it takes a string. This makes the calling code more intuitive. For
example, when the originating form is loaded, the clearance sub-routine is
invoked with, say, 'Flush Me, "OnLoad"'. After entering a surname, the
sub-routine would be invoked with, perhaps, 'Flush Me, "AfterSurname".

Using a sort of hybrid VBA/'C' code, if this were possible, the resulting
sub-routine would look something like:-

Public Sub Flush (InForm As Form, InStart As String)
With InForm
'The 'C' bit starts here:-
Switch InStart
{ case "First_String":
.Control_A.Enabled = False;
.Control_B.Enabled = False;
.Control_C.Enabled = False;
case "Second_String":
.Control_D.Enabled = False;
.Control_E.Enabled = False;
.Control_F.Enabled = False;
.Control_G.Enabled = False;
.Control_H.Enabled = False;
Etc.
Etc.
case "Nth_String":
.Control_I.Enabled = False;
.Control_J.Enabled = False;
.Control_K.Enabled = False;
.Control_L.Enabled = False;
break;
}
'And ends here.
End With
End Sub

Having taken on board the comments so far made, it looks as if the best that
can be done to achieve the same ends with VBA is likely to be something like:-

Public Sub Flush (InForm As Form, InStart As String)

'*************Conversion Code ****************************
Dim bytIndex As Byte

Select Case InStart
Case "FirstString"
bytIndex = 0
Case "SecondString"
bytIndex = 1
Case "ThirdString"
bytIndex = 2
Etc.
Etc.
Case "LastString"
bytIndex = N-1
End Select

'************************************************************
With InForm
If bytIndex < 1 Then
.Control_A.Enabled = False
.Control_B.Enabled = False
.Control_C.Enabled = False
End If
If bytIndex < 2 Then
.Control_D.Enabled = False
.Control_E.Enabled = False
.Control_F.Enabled = False
.Control_G.Enabled = False
.Control_H.Enabled = False
End If
Etc.
Etc.
If bytIndex < N
.Control_I.Enabled = False
.Control_J.Enabled = False
.Control_K.Enabled = False
.Control_L.Enabled = False
End If

End With
End Sub

The difference between the two versions is clear and much in the 'hybrid's '
favour, I suggest. In the 'C' version, each case falls through into the next
one until a 'break' statement is encountered. (The terminating 'break'
statement is not actually needed. It was an ANSI 'C' convention to include
it after the last case statement.)

In defence of this construction (and it seems to need some!) it allowed a
lot of flexibility. 'Fall-through' and 'non-fall-though' clauses could be
mixed, for example, simply by including or excluding the break statement.
Apart from allowing more concise code, the 'C' solution also obviates the
need for string:numeric conversions. In my humble opinion, the result is
more concise, easier to understand and maintain - and therefore better. I
can't see much virtue in restricting programming options without very good
reason. Still, that's water under the bridge.

To dispense with the 'conversion code' in the VBA version, InSort needs to
be passed as a numeric variable, as in my first posting, but then the
sub-routine-invoking commands 'Flush Me, "OnLoad"', and 'Flush Me,
"AfterSurname"' become, perhaps, 'Flush Me, 0' and 'Flush Me, 5', which are
significantly less intuitive.

There are other weaknesses in the VBA version. If an additional clearance
is needed, after the insertion of a new control, say 'Address_4', between the
existing 'Address_3' and 'Town/City' controls, all the subsequent numbers
have to be reallocated. The problem could, of course, be mitigated by
setting bytIndex in multiples of 10, thus facilitating later interpolation,
and this is what I have done in the version I have now implemented, but it
still represents an overhead that I could do without. Also, however you
define 'clutter', the VBA version is not pretty.

Turning to the method for effecting the actual control flushing, your tagged
control solution is interesting. Allied to Marsh's idea of allocating the
relevant controls to collections, it could make things a lot slicker. The
snag is that I have no idea how to do it. MS Access Help, on collections, is
woefully unhelpful and my so-called Access 'manuals' have nothing to say on
the matter. I would need some assistance.

In the absence of this sort of solution, there is a further, as yet
unconsidered, problem, which has been partly hidden by the simplification
applied when formulating the original question. For the sake of
illustration, the 'flushing' commands in the examples so far offered have
been reduced to just the disabling statements. In practice, at least two
commands are needed to 'flush' each control, namely:-

.Control_N = Null
.Control_N.Enabled = False

And some controls need to be reset slightly differently, e.g.:-

.Control_N = Default value
.Control_N.Enabled = False

It appears to be unnecessary to enable controls before they are assigned a
Null value but in some cases the current value may need to be tested before
taking action, whereupon the above expression becomes:-

If Condition(N) = True Then
.Control_N = Null 'or default value
.Control_N.Enabled=False
End If

With a lot of controls, significant repetition is involved, suggesting the
use of another sub-routine, or subroutines, to handle the repeated code
fragments but I can't get that to work. I have so far failed to successfully
pass the controls as an argument, from the sub-routine 'Sub Flush' to such a
subsequent sub-routine. Both Form and Control parameters need to be passed
if the sub-sub-routine is to identify the appropriate controls but VBA is
having none of it. The only version the compiler will accept without
complaint does precisely nothing. A 'tagged collection' solution would
probably render this sort approach unnecessary but it would nevertheless be
useful to know how to do it. Any thoughts?

 

[Stuart McCall]

In defence of this construction (and it seems to need some!)

"It is practically impossible to teach good programming to students that
have had a prior exposure to BASIC: as potential programmers they are
mentally mutilated beyond hope of regeneration." - Dr. E.W. Dijkstra

So good luck <g>

 

[Peter Hallett]

Perhaps that's going a bit OTT but I wouldn't disagree with the sentiments.
I once worked with chap who declared that he could see no reason for using
anything other than BASIC. His mortal soul was clearly lost beyond
redemption.

In the mean time it's not luck that I need - it's help. It would appear
that both Tina and I are now waiting, with baited breath, for the 'big guns'
to open up.

 

[Marshall Barton]

Actually, Tina, the example I supplied was a sort of outline VBA, rather than
‘C’. Sorry if I misled you.

In addition,for the sake of simplicity, in my first post, I described the
sub-routine as taking a numeric InStart argument. In my actual code,
however, it takes a string. This makes the calling code more intuitive. For
example, when the originating form is loaded, the clearance sub-routine is
invoked with, say, ‘Flush Me, “OnLoad”’. After entering a surname, the
sub-routine would be invoked with, perhaps, ‘Flush Me, “AfterSurname”.

Using a sort of hybrid VBA/’C’ code, if this were possible, the resulting
sub-routine would look something like:–

Public Sub Flush (InForm As Form, InStart As String)
With InForm
‘The ‘C’ bit starts here:-
Switch InStart
{ case “First_String”:
.Control_A.Enabled = False;
.Control_B.Enabled = False;
.Control_C.Enabled = False;
case “Second_String”:
.Control_D.Enabled = False;
.Control_E.Enabled = False;
.Control_F.Enabled = False;
.Control_G.Enabled = False;
.Control_H.Enabled = False;
Etc.
Etc.
case “Nth_String”:
.Control_I.Enabled = False;
.Control_J.Enabled = False;
.Control_K.Enabled = False;
.Control_L.Enabled = False;
break;
}
‘And ends here.
End With
End Sub

Having taken on board the comments so far made, it looks as if the best that
can be done to achieve the same ends with VBA is likely to be something like:–

Public Sub Flush (InForm As Form, InStart As String)

‘*************Conversion Code ****************************
Dim bytIndex As Byte

Select Case InStart
Case “FirstString”
bytIndex = 0
Case “SecondString”
bytIndex = 1
Case “ThirdString”
bytIndex = 2
Etc.
Etc.
Case “LastString”
bytIndex = N-1
End Select

‘************************************************************
With InForm
If bytIndex < 1 Then
.Control_A.Enabled = False
.Control_B.Enabled = False
.Control_C.Enabled = False
End If
If bytIndex < 2 Then
.Control_D.Enabled = False
.Control_E.Enabled = False
.Control_F.Enabled = False
.Control_G.Enabled = False
.Control_H.Enabled = False
End If
Etc.
Etc.
If bytIndex < N
.Control_I.Enabled = False
.Control_J.Enabled = False
.Control_K.Enabled = False
.Control_L.Enabled = False
End If

End With
End Sub

The difference between the two versions is clear and much in the ‘hybrid’s’
favour, I suggest. In the ‘C’ version, each case falls through into the next
one until a ‘break’ statement is encountered. (The terminating ‘break’
statement is not actually needed. It was an ANSI ‘C’ convention to include
it after the last case statement.)

In defence of this construction (and it seems to need some!) it allowed a
lot of flexibility. ‘Fall-through’ and ‘non-fall-though’ clauses could be
mixed, for example, simply by including or excluding the break statement.
Apart from allowing more concise code, the ‘C’ solution also obviates the
need for string:numeric conversions. In my humble opinion, the result is
more concise, easier to understand and maintain – and therefore better. I
can’t see much virtue in restricting programming options without very good
reason. Still, that’s water under the bridge.

To dispense with the ‘conversion code’ in the VBA version, InSort needs to
be passed as a numeric variable, as in my first posting, but then the
sub-routine-invoking commands ‘Flush Me, “OnLoad”’, and ‘Flush Me,
“AfterSurname”’ become, perhaps, ‘Flush Me, 0’ and ‘Flush Me, 5’, which are
significantly less intuitive.

There are other weaknesses in the VBA version. If an additional clearance
is needed, after the insertion of a new control, say ‘Address_4’, between the
existing ‘Address_3’ and ‘Town/City’ controls, all the subsequent numbers
have to be reallocated. The problem could, of course, be mitigated by
setting bytIndex in multiples of 10, thus facilitating later interpolation,
and this is what I have done in the version I have now implemented, but it
still represents an overhead that I could do without. Also, however you
define ‘clutter’, the VBA version is not pretty.

Turning to the method for effecting the actual control flushing, your tagged
control solution is interesting. Allied to Marsh’s idea of allocating the
relevant controls to collections, it could make things a lot slicker. The
snag is that I have no idea how to do it. MS Access Help, on collections, is
woefully unhelpful and my so-called Access ‘manuals’ have nothing to say on
the matter. I would need some assistance.

In the absence of this sort of solution, there is a further, as yet
unconsidered, problem, which has been partly hidden by the simplification
applied when formulating the original question. For the sake of
illustration, the ‘flushing’ commands in the examples so far offered have
been reduced to just the disabling statements. In practice, at least two
commands are needed to ‘flush’ each control, namely:-

.Control_N = Null
.Control_N.Enabled = False

And some controls need to be reset slightly differently, e.g.:–

.Control_N = Default value
.Control_N.Enabled = False

It appears to be unnecessary to enable controls before they are assigned a
Null value but in some cases the current value may need to be tested before
taking action, whereupon the above expression becomes:–

If Condition(N) = True Then
.Control_N = Null ‘or default value
.Control_N.Enabled=False
End If

With a lot of controls, significant repetition is involved, suggesting the
use of another sub-routine, or subroutines, to handle the repeated code
fragments but I can’t get that to work. I have so far failed to successfully
pass the controls as an argument, from the sub-routine ‘Sub Flush’ to such a
subsequent sub-routine. Both Form and Control parameters need to be passed
if the sub-sub-routine is to identify the appropriate controls but VBA is
having none of it. The only version the compiler will accept without
complaint does precisely nothing. A ‘tagged collection’ solution would
probably render this sort approach unnecessary but it would nevertheless be
useful to know how to do it.

It think there is a fundamental flaw in your attachment to
the drop through Case goal. Except for the first time after
a form is opened, you must set ALL the controls' Enabled to
either True or False. If you don't, a previous setting will
still be in effect for any controls that are not set.

I think I would tend towards an approach that assigns each
control to one or more "groups". This can be done using the
Tag property by setting it to the name of each "group" that
it belongs to. E.g. a tag could be set to something like:
string1;string2;string4
if it should be disabled for any of those "groups" and
enabled for all other "groups".

A general outline of the procedure could then be:

Sub DisableControls(grp)
Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Tag <> "" Then
ctl.Enabled = (";" & ctl.Tag & ";") Like ("*;" & grp &
";*")
End If
Next ctl
End Sub

As I said in another reply, if there are a significan number
of controls that are not involved in this operation, then a
collection may provide a significant performance benefit.
You would use code at the top of the form's Load event to
populate the collection.

Static col As Collection 'in form's declarations section

Dim ctl As Control 'in Forms Load event
Set col = New Collection
For Each ctl In Me.Controls
If ctl.Tag <> "" Then
col.Add ctl, ctl.Name
End If
Next ctl

Then the procedure would be

Sub DisableControls(grp)
Dim ctl As Control

For Each ctl In col
ctl.Enabled = (";" & ctl.Tag & ";") Like ("*;" & grp &
";*")
Next ctl
End Sub

If needed, this approach can easily be extended to deal with
setting some controls to Null by adding more "groups" for
the purpose.

 

[Peter Hallett]

It think there is a fundamental flaw in your attachment to
the drop through Case goal. Except for the first time after
a form is opened, you must set ALL the controls' Enabled to
either True or False. If you don't, a previous setting will
still be in effect for any controls that are not set.

I think I would tend towards an approach that assigns each
control to one or more "groups". This can be done using the
Tag property by setting it to the name of each "group" that
it belongs to. E.g. a tag could be set to something like:
string1;string2;string4
if it should be disabled for any of those "groups" and
enabled for all other "groups".

A general outline of the procedure could then be:

Sub DisableControls(grp)
Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Tag <> "" Then
ctl.Enabled = (";" & ctl.Tag & ";") Like ("*;" & grp &
";*")
End If
Next ctl
End Sub

As I said in another reply, if there are a significan number
of controls that are not involved in this operation, then a
collection may provide a significant performance benefit.
You would use code at the top of the form's Load event to
populate the collection.

Static col As Collection 'in form's declarations section

Dim ctl As Control 'in Forms Load event
Set col = New Collection
For Each ctl In Me.Controls
If ctl.Tag <> "" Then
col.Add ctl, ctl.Name
End If
Next ctl

Then the procedure would be

Sub DisableControls(grp)
Dim ctl As Control

For Each ctl In col
ctl.Enabled = (";" & ctl.Tag & ";") Like ("*;" & grp &
";*")
Next ctl
End Sub

If needed, this approach can easily be extended to deal with
setting some controls to Null by adding more "groups" for
the purpose.

Thanks for that, Marshall. The ‘collected tags’ solution appears to be just
what is needed. Tina recognized that tags alone could involve a significant
number of irrelevant controls but that is neatly resolved by your
‘collections’ extension of her original idea.

The ‘fundamental flaw’ to which you refer is, I suggest, illusory and
probably results from the simplified model I presented for discussion.

‘Variable’ data (e.g. purpose of flight, aircraft used, flight duration,
etc.) is added to ‘constant’ data (e.g. name, address, ID, account balance
etc.) in an invoice pro form which, on completion, is appended to the sales
ledger. It’s a pretty run-of-the-mill application and is handled by typical
form/sub-form architecture. Data entry to the sub-form is controlled by the
‘conditional clearance’ routine that formed the subject of this post. This
ensures that data is entered, and/or amended, in the required sequence and in
compliance with the data validation rules. In summary, ‘constant’ data from
tables P, Q and R, say, is combined with manually input ‘variable’ data and
the result is appended to table S, the sales ledger. However, I never bind
data input forms to source or object tables. The required fields from P, Q
and R are first copied to a buffer table B, to which the data entry form is
then bound. Only when the subsequent data entry has been completed can table
B be appended to table S. If the process is aborted at any point then buffer
table B is simply abandoned. If the form is subsequently reopened, the
original ‘constant’ data is recopied into the freshly cleared buffer table B,
effectively flushing all the form controls and hence avoiding the
‘fundamental flaw’. Tables P, Q, R and S thus remain protected behind a
Chinese wall.

I notice that you did not pick up my second problem so I think I’ll repose
that with a new posting. This thread is getting a bit unwieldy. If you
would care to address this other issue I would, of course, be very pleased
but, in the mean time, I am grateful for your help.

 

[Marshall Barton]

Thanks for that, Marshall. The ‘collected tags’ solution appears to be just
what is needed. Tina recognized that tags alone could involve a significant
number of irrelevant controls but that is neatly resolved by your
‘collections’ extension of her original idea.

The ‘fundamental flaw’ to which you refer is, I suggest, illusory and
probably results from the simplified model I presented for discussion.

‘Variable’ data (e.g. purpose of flight, aircraft used, flight duration,
etc.) is added to ‘constant’ data (e.g. name, address, ID, account balance
etc.) in an invoice pro form which, on completion, is appended to the sales
ledger. It’s a pretty run-of-the-mill application and is handled by typical
form/sub-form architecture. Data entry to the sub-form is controlled by the
‘conditional clearance’ routine that formed the subject of this post. This
ensures that data is entered, and/or amended, in the required sequence and in
compliance with the data validation rules. In summary, ‘constant’ data from
tables P, Q and R, say, is combined with manually input ‘variable’ data and
the result is appended to table S, the sales ledger. However, I never bind
data input forms to source or object tables. The required fields from P, Q
and R are first copied to a buffer table B, to which the data entry form is
then bound. Only when the subsequent data entry has been completed can table
B be appended to table S. If the process is aborted at any point then buffer
table B is simply abandoned. If the form is subsequently reopened, the
original ‘constant’ data is recopied into the freshly cleared buffer table B,
effectively flushing all the form controls and hence avoiding the
‘fundamental flaw’. Tables P, Q, R and S thus remain protected behind a
Chinese wall.

One problem at a time.

The flaw I was talking about has nothing to do with bound or
unbound tables. I was trying to point out that your
original example only set specific controls' Enabled =
False, but it ignored the other controls that need to have
Enabled = True

I just noticed that my code is enabling when it should be
disabling and vice versa. Change it to:

ctl.Enabled = Not (";" & ctl.Tag & ";") Like ("*;" & grp &
";*")

Referring back to the flaw, note that line of code not only
disables controls that are in the "group", but also enables
controls that are not in the group. This is a very
important point that I failed to catch in the discussion
about fall through case statements.

 

[tina]

I have so far failed to successfully

pass the controls as an argument, from the sub-routine 'Sub Flush' to such a
subsequent sub-routine. Both Form and Control parameters need to be passed
if the sub-sub-routine is to identify the appropriate controls but VBA is
having none of it. The only version the compiler will accept without
complaint does precisely nothing. A 'tagged collection' solution would
probably render this sort approach unnecessary but it would nevertheless be
useful to know how to do it. Any thoughts?

okay, Peter, if this is the question you're were still hoping for an answer
about, i'll try to address it. i have a feeling that you're not asking what
i think you're asking...so if i'm off the mark, sorry in advance!

if you're passing form and control references from one procedure to another
within a single form module...well, you shouldn't need to pass a form
reference in that case, and a control reference would be

Private Sub MySubRoutine(ByVal ctl As Control)

and the argument in the calling code would be

Me!MyControl

but if you're calling a procedure in a standard module - well, i haven't
done this in awhile, but i think the last time i did it, i just passed the
object names into the procedure, something like

Public Sub MySubRoutine(ByVal strCtl As String, strFrm As String)

Dim ctl As Control, frm As Form

Set frm = Forms(strFrm)
Set ctl = frm.Controls(strCtl)

as for passing a collection, oh boy, that's been awhile too. but i think you
can pass a populated collection to another procedure as

Private Sub GetMyCollection(ByVal col As Collection)

well, if anyone is still watching this thread, Peter, the above should get
some responses correcting my mistakes, at least, and hopefully give you the
help you need.

hth

 

[Peter Hallett]

Sorry if I am a bit late in responding. The local farmer drove his
cultivator through the cable that provides my internet connection and I have
had to spend most of the day repairing it.

Now I see your point about the ‘fundamental flaw’. In fact, it is not
necessary, nor desirable to re-enable all controls that are not disabled.
Again, the devil is in the details that I did not supply, for the sake of
trying to keep what is a fairly complex discussion as simple as possible.
(To paraphrase Tina’s latest comment, “Is anyone else still awake on this
thread?â€)

The thing works like this:-

When the form is opened, it invokes the flushing routine from the beginning,
clearing and disabling all the controls apart from the first one into which
data is to be entered. The AfterUpdate event of the latter then reinvokes
the clearance routine, starting with the next control in the list, and then
enables the latter. The process continues, control by control, offering a
sort of rolling input mask, forcing the user to enter data in the required
sequence. This is important because the data validation rules and or options
associated with later controls (one or two are combo boxes, for example) can
depend on the data entered in earlier controls. In those cases, as well as
invoking the clearance sub-routine and enabling the next control, the
AfterUpdate events of these earlier controls trigger Requery commands on the
relevant later controls. This ensures that data is not entered into a
control unless the appropriate options have been set. At least, that would
be the case if users never amended earlier entries during the course of data
input but, of course, that is exactly what users do and they would be very
upset if controls were locked or disabled after data entry, to prevent them
doing it.

The fall-through logic of the clearance routine takes care of this. Data
entry to, or amendment of, any control causes all subsequent data entries to
be deleted and forces them to be re-input, in sequence, control by control.

It may be objected that this sort of ‘catch all’ approach could result in
some controls being flushed unnecessarily but a few lines of exception code
will generally offer a solution in these cases. In fact, the client’s data
processing manager takes a hard line on this. He insists that users be
forced to redundantly re-input data, rather than be given the opportunity to
enter an inconsistent set. Almost needless to say, it was the not infrequent
recurrence of the latter that gave rise to the need for this rather draconian
input régime, endorsing the old adage that if mistakes can be made then they
will be. In practice, it must be said that the users have raised few
objections to the result.

Now I am off to apply what I have learned. Thanks to you and the other
contributors I have enough to keep me out of mischief for some time.

 

[Peter Hallett]

Thanks again for offering assistance, Tina, but I found the solution today,
and it turned out to be a lot simpler than I had imagined. It was the fact
that I was overcomplicating matters, combined with a coding error (mine!)
that (a) led to a belief that the required solution was more involved than it
turned out to be and (b) caused me to assume that my first effort did not
work.

You will recollect that the form was passed to the first sub-routine as an
argument – InForm. Within that sub-routine, controls could then be
referenced thus:-

With InForm
.Control_X = Something or other
.Control_X.Enabled = False
Etc.
.Control_Y = Something or other
.Control_Y.Enabled = False
Etc.
.Control_Z = Something or other
.Control_Z.Enabled = False
Etc.
Etc.
End With

The next step was to pass these controls to a sub-sub-routine and I
mistakenly assumed that I would need to pass both the form and the control to
the latter. I underestimated VBA, however. It proved quite capable of
working out to which form the control belonged using the simple syntax:-
With InForm
Process .Control_X
Process .Control_Y
Process .Control_Z
Etc.
End With

In conjunction with the sub-sub-routine

Public Sub Process(InControl As Control)
InControl = Something or other
InControl.Enabled = True
Etc.
End Sub

It was Occam, the man with the razor, who famously counselled that it was
unwise to look for complicated answers before simpler ones have been
thoroughly evaluated. I would add to that by recommending an initial review
of one’s own code to ensure that it does not contain silly mistakes!

I think we can now put this one to bed. My code now looks a lot more
sophisticated and, thanks to everyone’s help, I have significantly added to
my VBA arsenal. It might not be the most elegant programming language but it
can certainly deliver.

 

[tina]

you're welcome, Peter, glad you got it working!