combobox cascade effect not working for different

Question

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

Answer 1

in your first piece of code, shift the code to the on_click event. I am not sure what you are trying to achieve with the reference to ItemData but I think it is unnecessary, comment out that line.

Similarly the third to last line in the Form_current event, replace with a requery.

Answer 2

From your description, you are using a continuous form. While it looks like a continuous form has many rows, from the point of view of coding you can consider it to have just one row, the current row. I suspect that the control source for combo 2 is a hidden, numeric column in the combo, when you change the row source for the combo, the visible row can no longer be found, so it cannot be displayed. You will either have to provide a pop-up form for editing, or a textbox to store the value for the form and a slightly dodgy combo to edit that value. You can do a little to improve the appearance with conditional formatting.