cascade combobox problem.

[HiGu]

I have two comboboxes on a subform.The first combobox is used to populate the second combobox.These are placed in the detail section of the form.I want them to work this way:when I select any value from the first combobox,I want the second combobox of the same row to get populated by relevant value.
As of now, I have tried to implement this and as I select any value from the first combobox of row 1 I see the second combobox of the same row gets populated but as I go on selecting values from the first set of comboboxes I see that the values in the second set of the comboboxes above changing or becoming null.
Here's the code:
The 1st combobox is cboRCMTask:

Private Sub cboRCMTask_AfterUpdate()
    Me.cboRCMTaskOptions.RowSource = "SELECT ID, RCMTaskOptions FROM tblRCMTaskOptions WHERE RCM_ID=" & Me.cboRCMTask.Column(0) & ";"
    Me.cboRCMTaskOptions = Me.cboRCMTaskOptions.ItemData(0)
    Me.cboRCMTaskOptions.Requery
End Sub

cboRCMTaskOptions is the second combobox.

The form_current event:

Private Sub Form_Current()
    Me.cboRCMTask.RowSource = "SELECT ID, RCMTask FROM tblRCMTask;"
    If IsNull(txtRCM_ID) Then
        Me.cboRCMTask = Me.cboRCMTask.ItemData(0)
        'Me.cboRCMTask = Null
    End If
    Me.cboRCMTaskOptions.RowSource = "SELECT ID, RCMTaskOptions FROM tblRCMTaskOptions WHERE RCM_ID=" & Me.cboRCMTask.Column(0) & ";"
    If IsNull(txtRCMOption_ID) Then
        Me.cboRCMTaskOptions = Me.cboRCMTaskOptions.ItemData(0)
    End If
End Sub

[MikeTheBike]

Hi

I have three obsevations:

1. You have not said whether the comboboxes are bound to a control in the details section, if so, this could be the problem for cboRCMTaskOptions if the new Row Source does not contain the current (bound) value.

2. I would place the Form_Current code you have in the Form_Load event as, as fare a I can tell, this is not record dependant!!?

3. I think I would requery cboRCMTaskOptions BEFORE assigning it a value!?

Without knowing more about the data/table relations/binding this could all be irrelevant!!

MTB

[HiGu]

1.cboRCMTast is bound to RCM_ID and cboRCMTaskOptions is bound to RCMOptionID.
2.I have written the code in the form_current event thinking that since the rowsource of cboRCMTask Options has to change on selection of value from cboRCMTask.
3.I am changing the rowsource of cboRCMTaskOptions in the afterupdate event of cboRCMTask

This was how I thought,there could be a better way of doing this.Please let me know if I'm mistaken.

[HiGu]

Isn't there any soultion to this?

[MikeTheBike]

Hi again

My only other thought is this

Private Sub cboRCMTask_AfterUpdate() 
    Me.cboRCMTaskOptions.RowSource = "SELECT ID, RCMTaskOptions FROM tblRCMTaskOptions WHERE RCM_ID=" & Me.cboRCMTask.Column(0) & " Or  RCM_ID=" & Me.RCM_ID & ";" 
    Me.cboRCMTaskOptions = Me.cboRCMTaskOptions.ItemData(0) 
    Me.cboRCMTaskOptions.Requery 
End Sub 

ie I has added
Or RCM_ID=" & Me.RCM_ID & "
to the where condition which will guarantee that the current record value is available for display irrespective of the combobox selection.

Don't know if that helps!

MTB

[HiGu]

That didn't work. :(

[Rabbit]

Did you set the limit to list to no? If it's set to yes, the data in the other records won't show because it's not on the list. You need to set it to no.

[HiGu]

I tried setting Limit to list property to NO but it seems to be not working.

[Rabbit]

I'm out of ideas, the only thing left is for you to attach the database so we can look at it.