Testing for multible conditions in VBA

  • Thread starter Thread starter RobUCSD via AccessMonster.com
  • Start date Start date
R

RobUCSD via AccessMonster.com

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
 
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
 
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



Douglas J. Steele said:
For Each ctl In Me.Controls
Click to expand...
 
Hate to ask the obvious, but is the Tag property set properly for those 2
check boxes?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


RobUCSD said:
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 Sub
********************************************************************************************************
Thanks, Rob
Click to expand...
 
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
 
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.
 
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.
 
if the record is a new record all the controls are
enabled. I need them to not be enabled. thanks for helping.
Click to expand...

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.
 
Thanks for your response, I'm not sure how to set up a custum collection. Any
tips. thanks rob
 
I'm not sure how to set up a custum collection.
Click to expand...

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).
 
Back
Top