Easiest Way to Enable Groups of Controls?

[DBQueen]

I have a form with lines of controls. On some of the lines there are 3
controls (call them A,B,C); other lines have only control A. The controls
have been numbered sequentially (Q20, Q21....Q76) and they were put onto the
form in the numerical sequence. I have given the A's with B & C's on the
same line a tag=1; B.tag=2, C,tag=2)

I am trying to write 2 modules so that the following occurs (on Form_Current
and 'A' Control_Before or After Update Events).

For each line, Control A will always be enabled.
For each line if there are B & C controls, if A is not null, enable B&C,
otherwise disable them.

The only way I have found of making this work seems awfully cumbersome,
and it won't work on the Before/After _Update events because you can't send
the focus to a disabled control to enable it. Is there a simpler way to
accomplish this? I've included some of my questions in the comment lines.

My method is: On _Form Current:

Public Sub EnableBC(frmcurrent As Form, fldCurr As Variant, fldCurrName As
Variant, fldMax As Variant)
'this is for Q1-Q99
On Error GoTo Err_EnableBC

Dim fldB As Variant 'the number part of control B's name
Dim fldC As Variant 'the number part of control B's name
Dim fldBName As Variant 'B control fieldname
Dim fldCName As Variant 'C control fieldname
Dim fldCtrl1 As Control 'Actual B control
Dim fldCtrl2 As Control 'Actual C Control
Dim fldCtrlCurr As Control 'Actual A Control
Dim ocontrol As Control
Dim icount As Integer

Debug.Print "fldCurr= "; fldCurr
Debug.Print "fldMax= "; fldMax

'Enable all B and C Controls
For Each ocontrol In frmcurrent
If TypeOf ocontrol Is ComboBox And ocontrol.Tag = 2 Then
ocontrol.Enabled = True
End If

If TypeOf ocontrol Is TextBox And ocontrol.Tag = 2 Then
ocontrol.Enabled = True
End If
Next ocontrol
For Each ocontrol In frmcurrent
If TypeOf ocontrol Is ComboBox Or TypeOf ocontrol Is TextBox Then
If ocontrol.Tag = 1 Then
If IsNull(ocontrol) = True Or ocontrol = 0 Then
Debug.Print "in the tag=1 loop"
Debug.Print "oControl.name= "; ocontrol.Name
fldB = Right$(ocontrol.Name, 2) + 1
Debug.Print "fldB = "; fldB
fldC = Right$(ocontrol.Name, 2) + 2
Debug.Print "fldC = "; fldC
fldBName = "Q" & fldB
Debug.Print "fldBName= "; fldBName
fldCName = "Q" & fldC

'go to the control you want to disable. This is the ONLY way I could get
this to work, unless I'm majorly missing the boat 'on the technique here.
However I can't disable a control that has the focus, I can only lock it.
Can I disable the control 'without going to it first? Until this point in
the loop I have been dealing with the B and C controls NAMES only - not the
'actual control. But I can't lock/disable a NAME, only a control. And when
I use this type of routine in the Before/After 'Update event, it won't allow
me to GOTO a disabled control, of course.

DoCmd.GoToControl fldBName
Set fldCtrl1 = Screen.ActiveControl
With fldCtrl1
.Locked = True
.Value = ""
End With

DoCmd.GoToControl fldCName
Set fldCtrl2 = Screen.ActiveControl

With fldCtrl2
.Locked = True
.Value = ""
End With
Else
Debug.Print "oControl.name= "; ocontrol.Name
fldB = Right$(ocontrol.Name, 2) + 1
Debug.Print "fldB = "; fldB
fldC = Right$(ocontrol.Name, 2) + 2
Debug.Print "fldC = "; fldC
fldBName = "Q" & fldB
Debug.Print "fldBName= "; fldBName
fldCName = "Q" & fldC
DoCmd.GoToControl fldBName
Set fldCtrl1 = Screen.ActiveControl

With fldCtrl1
.Locked = False
End With

DoCmd.GoToControl fldCName
Set fldCtrl2 = Screen.ActiveControl
With fldCtrl2
.Locked = False
End With
End If
End If
End If
Next ocontrol

'I put the focus on a field which will never be affected so I can now
disenable the controls I want disenabled.

frmcurrent!Q58A.SetFocus

'Now I can loop through and disable the controls which are locked. Then,
for some reason, it didn't turn them to the usual 'gray, so I had to force
the color to indicate they are disabled.

For Each ocontrol In frmcurrent
If TypeOf ocontrol Is TextBox Or TypeOf ocontrol Is ComboBox Then
Debug.Print "in the final loop"
Debug.Print "ocontrol= "; ocontrol.Name

If ocontrol.Locked = -1 Then
ocontrol.Enabled = False
ocontrol.BackColor = 8421504 'gray
Else
ocontrol.Enabled = True
ocontrol.BackColor = 16777215 'white
End If
End If
Next ocontrol

Err_EnableBC:
Exit Sub

End Sub

Now I KNOW there has to be a more reasonable way to accomplish this.
PLEASE, someone, show me the path to take on this.

Thanks!

Andi

[Tony D'Ambra]

This is not correct: "because you can't send the focus to a disabled control
to enable it." A disabled control does not need to have the focus to enable
it. What you can't do is disable a control that has the focus. So the first
thing you must do is set the focus to an enabled control that is not a party
to the routine...

--

Tony D'Ambra
Web Site: aadconsulting.com
Web Blog: accessextra.net

"DBQueen" <ir******@bellsouth.net> wrote in message
news:6h******************@bignews6.bellsouth.net.. .

I have a form with lines of controls. On some of the lines there are 3
controls (call them A,B,C); other lines have only control A. The controls
have been numbered sequentially (Q20, Q21....Q76) and they were put onto
the
form in the numerical sequence. I have given the A's with B & C's on the
same line a tag=1; B.tag=2, C,tag=2)

I am trying to write 2 modules so that the following occurs (on
Form_Current
and 'A' Control_Before or After Update Events).

For each line, Control A will always be enabled.
For each line if there are B & C controls, if A is not null, enable
B&C,
otherwise disable them.

The only way I have found of making this work seems awfully cumbersome,
and it won't work on the Before/After _Update events because you can't
send
the focus to a disabled control to enable it. Is there a simpler way to
accomplish this? I've included some of my questions in the comment lines.

My method is: On _Form Current:

Public Sub EnableBC(frmcurrent As Form, fldCurr As Variant, fldCurrName As
Variant, fldMax As Variant)
'this is for Q1-Q99
On Error GoTo Err_EnableBC

Dim fldB As Variant 'the number part of control B's name
Dim fldC As Variant 'the number part of control B's name
Dim fldBName As Variant 'B control fieldname
Dim fldCName As Variant 'C control fieldname
Dim fldCtrl1 As Control 'Actual B control
Dim fldCtrl2 As Control 'Actual C Control
Dim fldCtrlCurr As Control 'Actual A Control
Dim ocontrol As Control
Dim icount As Integer

Debug.Print "fldCurr= "; fldCurr
Debug.Print "fldMax= "; fldMax

'Enable all B and C Controls
For Each ocontrol In frmcurrent
If TypeOf ocontrol Is ComboBox And ocontrol.Tag = 2 Then
ocontrol.Enabled = True
End If

If TypeOf ocontrol Is TextBox And ocontrol.Tag = 2 Then
ocontrol.Enabled = True
End If
Next ocontrol
For Each ocontrol In frmcurrent
If TypeOf ocontrol Is ComboBox Or TypeOf ocontrol Is TextBox Then
If ocontrol.Tag = 1 Then
If IsNull(ocontrol) = True Or ocontrol = 0 Then
Debug.Print "in the tag=1 loop"
Debug.Print "oControl.name= "; ocontrol.Name
fldB = Right$(ocontrol.Name, 2) + 1
Debug.Print "fldB = "; fldB
fldC = Right$(ocontrol.Name, 2) + 2
Debug.Print "fldC = "; fldC
fldBName = "Q" & fldB
Debug.Print "fldBName= "; fldBName
fldCName = "Q" & fldC

'go to the control you want to disable. This is the ONLY way I could get
this to work, unless I'm majorly missing the boat 'on the technique here.
However I can't disable a control that has the focus, I can only lock it.
Can I disable the control 'without going to it first? Until this point in
the loop I have been dealing with the B and C controls NAMES only - not
the
'actual control. But I can't lock/disable a NAME, only a control. And
when
I use this type of routine in the Before/After 'Update event, it won't
allow
me to GOTO a disabled control, of course.

DoCmd.GoToControl fldBName
Set fldCtrl1 = Screen.ActiveControl
With fldCtrl1
.Locked = True
.Value = ""
End With

DoCmd.GoToControl fldCName
Set fldCtrl2 = Screen.ActiveControl

With fldCtrl2
.Locked = True
.Value = ""
End With
Else
Debug.Print "oControl.name= "; ocontrol.Name
fldB = Right$(ocontrol.Name, 2) + 1
Debug.Print "fldB = "; fldB
fldC = Right$(ocontrol.Name, 2) + 2
Debug.Print "fldC = "; fldC
fldBName = "Q" & fldB
Debug.Print "fldBName= "; fldBName
fldCName = "Q" & fldC
DoCmd.GoToControl fldBName
Set fldCtrl1 = Screen.ActiveControl

With fldCtrl1
.Locked = False
End With

DoCmd.GoToControl fldCName
Set fldCtrl2 = Screen.ActiveControl
With fldCtrl2
.Locked = False
End With
End If
End If
End If
Next ocontrol

'I put the focus on a field which will never be affected so I can now
disenable the controls I want disenabled.

frmcurrent!Q58A.SetFocus

'Now I can loop through and disable the controls which are locked. Then,
for some reason, it didn't turn them to the usual 'gray, so I had to force
the color to indicate they are disabled.

For Each ocontrol In frmcurrent
If TypeOf ocontrol Is TextBox Or TypeOf ocontrol Is ComboBox Then
Debug.Print "in the final loop"
Debug.Print "ocontrol= "; ocontrol.Name

If ocontrol.Locked = -1 Then
ocontrol.Enabled = False
ocontrol.BackColor = 8421504 'gray
Else
ocontrol.Enabled = True
ocontrol.BackColor = 16777215 'white
End If
End If
Next ocontrol

Err_EnableBC:
Exit Sub

End Sub

Now I KNOW there has to be a more reasonable way to accomplish this.
PLEASE, someone, show me the path to take on this.

Thanks!

Andi

[Tony D'Ambra]

This is not correct: "because you can't send the focus to a disabled control
to enable it." A disabled control does not need to have the focus to enable
it. What you can't do is disable a control that has the focus. So the first
thing you must do is set the focus to an enabled control that is not a party
to the routine...

--

Tony D'Ambra
Web Site: aadconsulting.com
Web Blog: accessextra.net

"DBQueen" <ir******@bellsouth.net> wrote in message
news:6h******************@bignews6.bellsouth.net.. .

I have a form with lines of controls. On some of the lines there are 3
controls (call them A,B,C); other lines have only control A. The controls
have been numbered sequentially (Q20, Q21....Q76) and they were put onto
the
form in the numerical sequence. I have given the A's with B & C's on the
same line a tag=1; B.tag=2, C,tag=2)

I am trying to write 2 modules so that the following occurs (on
Form_Current
and 'A' Control_Before or After Update Events).

For each line, Control A will always be enabled.
For each line if there are B & C controls, if A is not null, enable
B&C,
otherwise disable them.

The only way I have found of making this work seems awfully cumbersome,
and it won't work on the Before/After _Update events because you can't
send
the focus to a disabled control to enable it. Is there a simpler way to
accomplish this? I've included some of my questions in the comment lines.

My method is: On _Form Current:

Public Sub EnableBC(frmcurrent As Form, fldCurr As Variant, fldCurrName As
Variant, fldMax As Variant)
'this is for Q1-Q99
On Error GoTo Err_EnableBC

Dim fldB As Variant 'the number part of control B's name
Dim fldC As Variant 'the number part of control B's name
Dim fldBName As Variant 'B control fieldname
Dim fldCName As Variant 'C control fieldname
Dim fldCtrl1 As Control 'Actual B control
Dim fldCtrl2 As Control 'Actual C Control
Dim fldCtrlCurr As Control 'Actual A Control
Dim ocontrol As Control
Dim icount As Integer

Debug.Print "fldCurr= "; fldCurr
Debug.Print "fldMax= "; fldMax

'Enable all B and C Controls
For Each ocontrol In frmcurrent
If TypeOf ocontrol Is ComboBox And ocontrol.Tag = 2 Then
ocontrol.Enabled = True
End If

If TypeOf ocontrol Is TextBox And ocontrol.Tag = 2 Then
ocontrol.Enabled = True
End If
Next ocontrol
For Each ocontrol In frmcurrent
If TypeOf ocontrol Is ComboBox Or TypeOf ocontrol Is TextBox Then
If ocontrol.Tag = 1 Then
If IsNull(ocontrol) = True Or ocontrol = 0 Then
Debug.Print "in the tag=1 loop"
Debug.Print "oControl.name= "; ocontrol.Name
fldB = Right$(ocontrol.Name, 2) + 1
Debug.Print "fldB = "; fldB
fldC = Right$(ocontrol.Name, 2) + 2
Debug.Print "fldC = "; fldC
fldBName = "Q" & fldB
Debug.Print "fldBName= "; fldBName
fldCName = "Q" & fldC

'go to the control you want to disable. This is the ONLY way I could get
this to work, unless I'm majorly missing the boat 'on the technique here.
However I can't disable a control that has the focus, I can only lock it.
Can I disable the control 'without going to it first? Until this point in
the loop I have been dealing with the B and C controls NAMES only - not
the
'actual control. But I can't lock/disable a NAME, only a control. And
when
I use this type of routine in the Before/After 'Update event, it won't
allow
me to GOTO a disabled control, of course.

DoCmd.GoToControl fldBName
Set fldCtrl1 = Screen.ActiveControl
With fldCtrl1
.Locked = True
.Value = ""
End With

DoCmd.GoToControl fldCName
Set fldCtrl2 = Screen.ActiveControl

With fldCtrl2
.Locked = True
.Value = ""
End With
Else
Debug.Print "oControl.name= "; ocontrol.Name
fldB = Right$(ocontrol.Name, 2) + 1
Debug.Print "fldB = "; fldB
fldC = Right$(ocontrol.Name, 2) + 2
Debug.Print "fldC = "; fldC
fldBName = "Q" & fldB
Debug.Print "fldBName= "; fldBName
fldCName = "Q" & fldC
DoCmd.GoToControl fldBName
Set fldCtrl1 = Screen.ActiveControl

With fldCtrl1
.Locked = False
End With

DoCmd.GoToControl fldCName
Set fldCtrl2 = Screen.ActiveControl
With fldCtrl2
.Locked = False
End With
End If
End If
End If
Next ocontrol

'I put the focus on a field which will never be affected so I can now
disenable the controls I want disenabled.

frmcurrent!Q58A.SetFocus

'Now I can loop through and disable the controls which are locked. Then,
for some reason, it didn't turn them to the usual 'gray, so I had to force
the color to indicate they are disabled.

For Each ocontrol In frmcurrent
If TypeOf ocontrol Is TextBox Or TypeOf ocontrol Is ComboBox Then
Debug.Print "in the final loop"
Debug.Print "ocontrol= "; ocontrol.Name

If ocontrol.Locked = -1 Then
ocontrol.Enabled = False
ocontrol.BackColor = 8421504 'gray
Else
ocontrol.Enabled = True
ocontrol.BackColor = 16777215 'white
End If
End If
Next ocontrol

Err_EnableBC:
Exit Sub

End Sub

Now I KNOW there has to be a more reasonable way to accomplish this.
PLEASE, someone, show me the path to take on this.

Thanks!

Andi

[Andi Plotsky]

Thanks for your reply, Tony. I understand what you are saying, but I still
don't know how to refer to the control that I want to enable (or disable).
The part of my code before the DoCmd.GoToControl, finds the NAME of the
controls I want to enable. But I can't find a way to get the actual CONTROL
enabled without first jumping to it, capturing it to a Control Variable,
jumping away from it and then changing it's status. Besides seeming awkward
to me, it makes my form have a sort of small "seizure" as the GoToControl
function hops around.

What I'm looking for is a simple way to say: "For any control with a Tag of
1 whose value is not null, enable the next 2 controls after it" ?

I tried to capture the value of the current control in the Controls "array"
and then go to the next 2 controls in line:
For i=0 to 49
if ocontrol=Forms(frmCurrent).Controls(i) then
fldctrl1=Forms(frmCurrent).Controls(i+1)
fldctrl2=Forms(frmCurrent).Controls(i+2)
fldctrl1.enabled=true
fldctrl2.enabled=true
end if
next i

.....but I got a type mismatch on the IF line - I guess my ocontrol isn't the
same datatype as Forms(frmCurrent).Controls(i) .

Can you offer any other insights?

Thanks!

Andi

"Tony D'Ambra" <td*****@swiftdsl.com.au> wrote in message
news:41***********************@news.syd.swiftdsl.c om.au...

This is not correct: "because you can't send the focus to a disabled control to enable it." A disabled control does not need to have the focus to enable it. What you can't do is disable a control that has the focus. So the first thing you must do is set the focus to an enabled control that is not a party to the routine...

--

Tony D'Ambra
Web Site: aadconsulting.com
Web Blog: accessextra.net

"DBQueen" <ir******@bellsouth.net> wrote in message
news:6h******************@bignews6.bellsouth.net.. .

I have a form with lines of controls. On some of the lines there are 3
controls (call them A,B,C); other lines have only control A. The controls have been numbered sequentially (Q20, Q21....Q76) and they were put onto
the
form in the numerical sequence. I have given the A's with B & C's on the same line a tag=1; B.tag=2, C,tag=2)

I am trying to write 2 modules so that the following occurs (on
Form_Current
and 'A' Control_Before or After Update Events).

For each line, Control A will always be enabled.
For each line if there are B & C controls, if A is not null, enable
B&C,
otherwise disable them.

The only way I have found of making this work seems awfully cumbersome, and it won't work on the Before/After _Update events because you can't
send
the focus to a disabled control to enable it. Is there a simpler way to
accomplish this? I've included some of my questions in the comment lines.
My method is: On _Form Current:

Public Sub EnableBC(frmcurrent As Form, fldCurr As Variant, fldCurrName As Variant, fldMax As Variant)
'this is for Q1-Q99
On Error GoTo Err_EnableBC

Dim fldB As Variant 'the number part of control B's name
Dim fldC As Variant 'the number part of control B's name
Dim fldBName As Variant 'B control fieldname
Dim fldCName As Variant 'C control fieldname
Dim fldCtrl1 As Control 'Actual B control
Dim fldCtrl2 As Control 'Actual C Control
Dim fldCtrlCurr As Control 'Actual A Control
Dim ocontrol As Control
Dim icount As Integer

Debug.Print "fldCurr= "; fldCurr
Debug.Print "fldMax= "; fldMax

'Enable all B and C Controls
For Each ocontrol In frmcurrent
If TypeOf ocontrol Is ComboBox And ocontrol.Tag = 2 Then
ocontrol.Enabled = True
End If

If TypeOf ocontrol Is TextBox And ocontrol.Tag = 2 Then
ocontrol.Enabled = True
End If
Next ocontrol
For Each ocontrol In frmcurrent
If TypeOf ocontrol Is ComboBox Or TypeOf ocontrol Is TextBox Then
If ocontrol.Tag = 1 Then
If IsNull(ocontrol) = True Or ocontrol = 0 Then
Debug.Print "in the tag=1 loop"
Debug.Print "oControl.name= "; ocontrol.Name
fldB = Right$(ocontrol.Name, 2) + 1
Debug.Print "fldB = "; fldB
fldC = Right$(ocontrol.Name, 2) + 2
Debug.Print "fldC = "; fldC
fldBName = "Q" & fldB
Debug.Print "fldBName= "; fldBName
fldCName = "Q" & fldC

'go to the control you want to disable. This is the ONLY way I could get this to work, unless I'm majorly missing the boat 'on the technique here. However I can't disable a control that has the focus, I can only lock it. Can I disable the control 'without going to it first? Until this point in the loop I have been dealing with the B and C controls NAMES only - not
the
'actual control. But I can't lock/disable a NAME, only a control. And
when
I use this type of routine in the Before/After 'Update event, it won't
allow
me to GOTO a disabled control, of course.

DoCmd.GoToControl fldBName
Set fldCtrl1 = Screen.ActiveControl
With fldCtrl1
.Locked = True
.Value = ""
End With

DoCmd.GoToControl fldCName
Set fldCtrl2 = Screen.ActiveControl

With fldCtrl2
.Locked = True
.Value = ""
End With
Else
Debug.Print "oControl.name= "; ocontrol.Name
fldB = Right$(ocontrol.Name, 2) + 1
Debug.Print "fldB = "; fldB
fldC = Right$(ocontrol.Name, 2) + 2
Debug.Print "fldC = "; fldC
fldBName = "Q" & fldB
Debug.Print "fldBName= "; fldBName
fldCName = "Q" & fldC
DoCmd.GoToControl fldBName
Set fldCtrl1 = Screen.ActiveControl

With fldCtrl1
.Locked = False
End With

DoCmd.GoToControl fldCName
Set fldCtrl2 = Screen.ActiveControl
With fldCtrl2
.Locked = False
End With
End If
End If
End If
Next ocontrol

'I put the focus on a field which will never be affected so I can now
disenable the controls I want disenabled.

frmcurrent!Q58A.SetFocus

'Now I can loop through and disable the controls which are locked. Then, for some reason, it didn't turn them to the usual 'gray, so I had to force the color to indicate they are disabled.

For Each ocontrol In frmcurrent
If TypeOf ocontrol Is TextBox Or TypeOf ocontrol Is ComboBox Then
Debug.Print "in the final loop"
Debug.Print "ocontrol= "; ocontrol.Name

If ocontrol.Locked = -1 Then
ocontrol.Enabled = False
ocontrol.BackColor = 8421504 'gray
Else
ocontrol.Enabled = True
ocontrol.BackColor = 16777215 'white
End If
End If
Next ocontrol

Err_EnableBC:
Exit Sub

End Sub

Now I KNOW there has to be a more reasonable way to accomplish this.
PLEASE, someone, show me the path to take on this.

Thanks!

Andi