Programming 3 Dependent Combo Boxes 1

[gyli84]

Does anyone know how to program 3 dependent combo boxes (where the values in the previous combo box limit the values in the next). I have tried a method you can use for 2 using queries as the rowsource but in trying it for 3 when I display them as part of records on a form the last comob box value always disappears whenever I switch to another record.

 

[Frink]

Hallo,

The basic idea is to put some code in the first combo box's AfterUpdate property to define the rowsource for the second combo box and run it's AfterUpdate property, which sets up the rowsource for the third combo box.
Initially the cboA should be set up with all the required data and cboB and cboC's rowsources should be blank.

sub cboA_AfterUpdate()
Me!cboB.RecordSource="SELECT Item FROM table WHERE link=" & cboA
'Put any setting of default value for cboB here
cboB_AfterUpdate
end sub

sub cboB_AfterUpdate()
Me!cboC.RecordSource="SELECT Item2 FROM table2 WHERE link2=" & cboB
'Put any setting of default value for cboC here
end sub

This does not address the question of initial values, but you get the idea. The key is to write the after update procedures so that the call the next one and the changes 'ripple down', so keeping the data consistent

- Frink

 

[scking]

To prevent controls B and C from being used first disable them. Then reenable them first thing in the AfterUpdate event of its primary control.

Initial State
ControlA = Enabled
ControlB = Disabled
ControlC = Disabled

Next State
ControlA = Enabled
ControlB = Enabled
ControlC = Disabled

Next State
ControlA = Enabled
ControlB = Enabled
ControlC = Enabled

Be sure the Form_Current property sets the controls to the initial state.

Steve King
Growth follows a healthy professional curiosity

 

[gyli84]

Does anyone have the Candace Tripp Cascading combo box demo they can send me. I've gone to the web site but there is a server error and I cannot download it. My e-mail is garyli@weconnor.com

Thanks

 

[gyli84]

Is there any easy way I can lock all the controls in the form if the checkbox Completed = True rather than listing them all?
Also, with regards to enabling and disabling combo boxes 2 and 3, the form also shows calls that have previously been logged and have not yet necessarily been completed, would it be possible to disable a combo control if the previous combo box was enabled but empty.

 

[Frink]

Hallo,

To disable all controls on a form use something like:
sub chkComplete_AfterUpdate()
Dim ctrControl As Control
If Me!chkComplete.Value then
For Each ctrControl In Me.Controls
With ctrControl
If .name <> &quot;chkComplete&quot; Then
.Enabled = False
End If
End With
Next ctrControl
end if

I could have used
.Enabled = (.name = &quot;chkcomplete)
instead of the centre If statement, but then it becomes harder to add other conditions if you want to not make all other controls disabled.
Anyway, you get the idea. You can use .ControlType to determine, er, the type of the control. That may be useful.

- Frink

 

[Frink]

Hallo,

Part 2:
To disable a combo box if another disabled, or enabled, but empty:

Me!cboX.Enabled = ((Me!cboY.Enabled) And (len(&quot;&quot; & Me!cboY) > 0))

Or you could use:
If Me!cboYEnabled then
If len(&quot;&quot; & Me!cboY) > 0 then
Me!cboX.Enabled = True
else
Me!cboX.Enabled = False
end if
else
Me!cboX.Enabled = False
end if

Any you could replace
len(&quot;&quot; & Me!cboY) > 0
with
Len(Nz(Me!cboY,&quot;&quot) > 0
or just
Nz(Me!cboY,&quot;&quot
choose the level at which you (and anyone else who'll have to read the code later) will understand

- Frink
- Frink

 

[gyli84]

With regards to the Disabling of controls if &quot;Completed&quot; = True, I have tried the code, modifying chkComplete to completed and it did not work, even when I placed it in Form_Current. What is ctrControl? Also is Me.Controls an expression for all controls on the form?

Thanks for your help.

 

[Frink]

Hallo,

Oh. In:
sub chkComplete_AfterUpdate()
Dim ctrControl As Control
If Me!chkComplete.Value then
For Each ctrControl In Me.Controls
With ctrControl
If .name <> &quot;chkComplete&quot; Then
.Enabled = False
End If
End With
Next ctrControl
end if

chkComplete should be replaced with your 'Completed' Check Box control name,
ctrControl is a local variable used to reference each control on the form, and
Me.Controls is the set of all the controls on the form.
Only chkComplete should be renamed and you should make sure that the code goes into the Completed check box After Update event procedure.

If your check box is called &quot;Completed&quot; then insert the following code into the After Update event procedure for 'Completed':
Dim ctrControl As Control
If Me!Completed.Value then
For Each ctrControl In Me.Controls
With ctrControl
If .name <> &quot;Completed&quot; Then
.Enabled = False
End If
End With
Next ctrControl
End If

In what way did it not work?

- Frink
(Just one this time)