Combo to Combo problem **Resolved**

[GlenW]

Thanks jdc2000 your solution was perfect.

Please bear with me!


I've got 3 combos.
Combo1 gets a list of experiments from a db.
When an experiment is selected in combo1, combo2 gets filled with the possible leg numbers of that experiment, combo3 with the possible descriptions.
A leg matches to a description. Together they are unique.
So the exact leg - description combination can be got by either
selecting from combo2 or combo3. With me so far?
Now, if I select the leg, I want the description to be automatically shown in combo3 and vice versa.
But, heres the problem, if the description is chosen I want leg to be auto shown.
This gets me into an infinite loop type thing. (top english!)
Combo2 tells combo3 what to show but then combo3 tells combo2 what to show so combo2 tells combo3...... etc. etc.

Any ideas.

[jcfowl]

Post the code, hard to tell the problem w/o it.

[GlenW]

OK this code uses BatchNumber and ReplicateNumber instead of leg and description, but the ideas the same.
As you can see I disable the other combo so things work, but I want the users to be able to switch between selection methods. So how can I make this work and leave the other enabled.

Private Sub cboRep_Click()
    
    cboBatch.Enabled = False
    
    If (Not (cboRep.Text = "")) Then
        cnnCon.Open dataText
        rsRes.Open "SELECT DISTINCT BatchNumber " & _
                   "FROM tblShadeDetails " & _
                   "WHERE ProjectTitle = " & Chr(34) & _
                                       cboProject.Text & Chr(34) & _
                   " AND ExperimentID = " & Chr(34) & _
                                       cboExperiment.Text & Chr(34) & _
                   " AND LegNumber = " & Val(cboLeg.Text) & _
                   " AND ReplicateNumber = " & Chr(34) & _
                                      cboRep.Text & Chr(34), _
                    cnnCon, _
                    adOpenKeyset, _
                    adLockReadOnly, _
                    adCmdText
                    
        With rsRes
            If (Not (.BOF And .EOF)) Then
                .MoveFirst
                While (Not .EOF)
                    cboBatch.Text = .Fields("BatchNumber").Value
                    .MoveNext
                Wend
            End If
        End With
        
        rsRes.Close
        cnnCon.Close
        
    End If
    
    setWashCycles
    
    
End Sub

[jdc2000]

The way to avoid your infinite loop problem is to not simply update the combo box text automatically, but to check what is in it first to see if it is already correct. If the value is already the correct one (meaning that it has already been updated), then do not update the contents. This will prevent re-triggering the event that signals the update. If you could post the event subs, we could show you what needs to be done to them.

[GlenW]

Excellent idea jdc2000

If(cboBatch.Text = .Fields("BatchNumber").Value)Then
    Exit Sub
Else
    cboBatch.Text = .Fields("BatchNumber").Value
End If

This should do it you think?

[jcfowl]

cboBatch.Text = .Fields("BatchNumber").Value
won't this Just overwrite the FirstItem in the list so that
only the Last Item in your Record Set will be the only
Item in your ComboBox??

Maybe
cboBatch.Aditem(.Fields("BatchNumber").Value)
Instead??

[GlenW]

No it only sets the text property.
The list is unaffected.
As the list in the other combo is created at the same time as this one, that is exactly what I want.