Testing for multible conditions in VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RobUCSD - 30 May 2007 16:45 GMT

With the code below I'm looping through my form's txtBox controls and tags.
If the control is a txtBox and has the tag of GrpA then I want those controls
to not be enabled. problem is, it doesn't work.

How can I test for multiple conditions and then change the control's
properites.

thanks, Rob

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

For Each ctl In Me
If ctl.ControlType = acTextBox And ctl.Tag = "GrpA" Then
ctl.Enabled = False
End If
Next ctl

Reply to this Message

Klatuu - 30 May 2007 17:09 GMT

Just a little syntax problem

Dim ctls As Controls
Dim ctl As Control

Set ctls = Me.Controls
For Each ctl In ctls
If ctl.ControlType = acTextBox And ctl.Tag = "GrpA" Then
ctl.Enabled = False
End If
Next ctl

Signature

Dave Hargis, Microsoft Access MVP

> With the code below I'm looping through my form's txtBox controls and tags.
> If the control is a txtBox and has the tag of GrpA then I want those controls
[quoted text clipped - 12 lines]
> End If
> Next ctl

Reply to this Message

Douglas J. Steele - 30 May 2007 17:10 GMT

For Each ctl In Me.Controls

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> With the code below I'm looping through my form's txtBox controls and
> tags.
[quoted text clipped - 14 lines]
> End If
> Next ctl

Reply to this Message

RobUCSD - 30 May 2007 17:34 GMT

Thanks Doug and Klatu,

In my orginal post I said I wanted to lock txtBoxes, really it was locking
checkBoxes. What I really need is to lock the checkBoxes if it is a new
record. Here's the code I have and it does not work, when I open the form
there are two (out of 12) chkBoxes that are not locked (mysterious). Also,
when the form is opened for the first time it allows the new record to have
data entered. I want to lock the controls until the edit cmdBtn is pressed as
I have code behing the edit but that checks if the user has the right to edit
the form.

Here's the code I have now
********************************************************************************************
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Error

Dim ctls As Controls
Dim ctl As Control

Set ctls = Me.Controls
For Each ctl In ctls
If ctl.ControlType = acCheckBox And ctl.Tag = "GrpA" And Me.NewRecord Then
ctl.Enabled = False
End If
Next ctl

Me.AllowEdits = False

On Error GoTo 0
Exit Sub

Form_Open_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Form_Open of VBA Document Form_frmAblationAFib"

End Su
********************************************************************************************************
Thanks, Rob

> For Each ctl In Me.Controls
>
[quoted text clipped - 16 lines]
> > End If
> > Next ctl

Reply to this Message

Douglas J. Steele - 30 May 2007 17:44 GMT

Hate to ask the obvious, but is the Tag property set properly for those 2
check boxes?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks Doug and Klatu,
>
[quoted text clipped - 59 lines]
>> > End If
>> > Next ctl

Reply to this Message

RobUCSD - 30 May 2007 17:53 GMT

Yes, they are. And if the record is a new record all the controls are
enabled. I need them to not be enabled. thanks for helping. Rob

> Hate to ask the obvious, but is the Tag property set properly for those 2
> check boxes?
[quoted text clipped - 62 lines]
> >> > End If
> >> > Next ctl

Reply to this Message

Douglas J. Steele - 30 May 2007 18:41 GMT

There doesn't appear to be anything wrong with your code.

You could likely make it more efficient by not combining all of the checks
into one:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Error

Dim ctl As Control

If Me.NewRecord Then
For Each ctl In ctls
If ctl.ControlType = acCheckBox Then
If ctl.Tag = "GrpA" Then
ctl.Enabled = False
End If
End If
Next ctl
End If
Me.AllowEdits = False

Form_Open_Exit:
On Error GoTo 0
Exit Sub

Form_Open_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure Form_Open of VBA Document Form_frmAblationAFib"
Resume Form_Open_Exit

End Sub

Note the change I made to your error handling. You really need the Resume in
there to clear the error.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Yes, they are. And if the record is a new record all the controls are
> enabled. I need them to not be enabled. thanks for helping. Rob
[quoted text clipped - 73 lines]
>> >> > End If
>> >> > Next ctl

Reply to this Message

Maurice - 30 May 2007 18:58 GMT

Rob,

In that case it's back to the drawing board and test every aspect of your
code. Why not breaking it up into pieces which you can check one by one.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Error

Dim ctls As Controls
Dim ctl As Control

Set ctls = Me.Controls
For Each ctl In ctls
If ctl.ControlType = acCheckBox then
msgbox "check"
end if

if ctl.Tag = "GrpA" then
msgbox "check"
end if
Next ctl

if Me.NewRecord Then
msgbox "new record"
End If

Me.AllowEdits = False

On Error GoTo 0
Exit Sub

So in the above I wouldn't check to see if it's a new record because that
will return the same value in the loop anyway.
By comparing the "checks" from the controltype with the "checks" from the
tag you can exclude the probality of a problem in that area.
By placing the check for the new record outside the for each you can exclude
this portion as well.

Sometimes it's handy to check all the controlnames and their tags in the
immediate window before cranking your head on something very obvious you
might have overseen.

Signature

Maurice Ausum

> Yes, they are. And if the record is a new record all the controls are
> enabled. I need them to not be enabled. thanks for helping. Rob
[quoted text clipped - 65 lines]
> > >> > End If
> > >> > Next ctl

Reply to this Message

David W. Fenton - 30 May 2007 19:11 GMT

> if the record is a new record all the controls are
> enabled. I need them to not be enabled. thanks for helping.

Might I suggest using a custom collection so that you set it up once
and then include in it only the checkbox controls? This is *much*
faster than walking the Controls collection, and my guess is that
you're going to want to enable/disable these a lot, so the fewer
controls you have to go through the better.

It might seem that it wouldn't be much of a difference, but the
performance improvement is actually noticeable to the user.

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message

RobUCSD - 30 May 2007 20:59 GMT

Thanks for your response, I'm not sure how to set up a custum collection. Any
tips. thanks rob

>> if the record is a new record all the controls are
>> enabled. I need them to not be enabled. thanks for helping.
[quoted text clipped - 7 lines]
>It might seem that it wouldn't be much of a difference, but the
>performance improvement is actually noticeable to the user.

Reply to this Message

David W. Fenton - 31 May 2007 22:09 GMT

> I'm not sure how to set up a custum collection.

In the declarations section of the form you're using, type this:

Dim mcolMyControls As Collection

Then in you're form's OnOpen event, do this:

Dim ctl As Control

For Each ctl In Me.Controls
If [whatever test you have is met] Then
mcolMyControls.Add ctl, ctl.Name
End If
Next ctl

Then you can act on the controls this way:

Dim ctl As Control

For Each ctl In mcolMyControls
ctl.Enabled = True
Next ctl

Or whatever you want to do.

Now, if your code (like mine) is not bulletproof and occasionally
resets itself, you might want to package up the initialization of
the collection as its own subroutine:

Private Sub IntializeCollection()
Dim ctl As Control

For Each ctl In Me.Controls
If [whatever test you have is met] Then
mcolMyControls.Add ctl, ctl.Name
End If
Next ctl
End Sub

and call that before you do anything with the collection:

Dim ctl As Control

Call InitializeCollection()
For Each ctl In mcolMyControls
ctl.Enabled = True
Next ctl

You could also wrap the collection in a class module and make it
self-healing, but that's more complicated, and I never do it myself,
to be honest. I'd only do that for a multi-attribute collection,
which requires an Array() anyway (collections can only have a value
and a key).

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message