2nd combobox rowsource based on 1st combobox in continuous form or datasheet

[Cyberwolf000]

I have a form that contains a subform. In this subform I have 2 combo box. The 1st box is the primary reason that is then supposed to filtered the list of secondary reasons for the primary reason. My issue is that when the 2nd combo boxes rowsource changes the values on the other lines disappear. Both box are bound to field in my main table. Here is the code I use to update the 2nd boxes rowsource.

    strSQL = "SELECT SecondReasonID, SecondReason " & _
             "FROM SecondaryReasons " & _
             "WHERE ReasonID=" & Me.cmbReasonID.Column(0) & ";"
    Set rec = CurrentDb().OpenRecordset(strSQL)
        If rec.RecordCount > 0 Then
            rec.MoveFirst
            Me.cmbSecondaryReasonID.RowSource = ""
            Do Until rec.EOF
                Me.cmbSecondaryReasonID.RowSource = Me.cmbSecondaryReasonID.RowSource & rec!SecondReasonID & "," & rec!SecondReason & ";"
            rec.MoveNext
            Loop
        End If
    rec.Close
    Set rec = Nothing

The table that drive the secondary reason looks like this

SecondaryReasonID <auto number=""> PK
ReasonID <number> FK
SecondReason <text>


<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>SecondaryReasons</caption> <thead> <tr> <th bordercolor="#000000" bgcolor="#c0c0c0">SecondReasonID</th> <th bordercolor="#000000" bgcolor="#c0c0c0">ReasonID</th> <th bordercolor="#000000" bgcolor="#c0c0c0">SecondReason</th> <th bordercolor="#000000" bgcolor="#c0c0c0">Active</th> </tr> </thead> <tbody> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">1</td> <td bordercolor="#c0c0c0" align="right">10</td> <td bordercolor="#c0c0c0">Instructions</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">2</td> <td bordercolor="#c0c0c0" align="right">4</td> <td bordercolor="#c0c0c0">Waste During OK</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">3</td> <td bordercolor="#c0c0c0" align="right">4</td> <td bordercolor="#c0c0c0">Customer Error</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">4</td> <td bordercolor="#c0c0c0" align="right">12</td> <td bordercolor="#c0c0c0">Paper</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">5</td> <td bordercolor="#c0c0c0" align="right">12</td> <td bordercolor="#c0c0c0">Labels</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">6</td> <td bordercolor="#c0c0c0" align="right">12</td> <td bordercolor="#c0c0c0">Glue</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">7</td> <td bordercolor="#c0c0c0" align="right">3</td> <td bordercolor="#c0c0c0">WRS</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">8</td> <td bordercolor="#c0c0c0" align="right">3</td> <td bordercolor="#c0c0c0">Port Equipment</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">9</td> <td bordercolor="#c0c0c0" align="right">6</td> <td bordercolor="#c0c0c0">Convert Error</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">10</td> <td bordercolor="#c0c0c0" align="right">6</td> <td bordercolor="#c0c0c0">W/B' & Tension</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">11</td> <td bordercolor="#c0c0c0" align="right">6</td> <td bordercolor="#c0c0c0">Jam Ups</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">12</td> <td bordercolor="#c0c0c0" align="right">6</td> <td bordercolor="#c0c0c0">Chips</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">13</td> <td bordercolor="#c0c0c0" align="right">6</td> <td bordercolor="#c0c0c0">Glue</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">14</td> <td bordercolor="#c0c0c0" align="right">11</td> <td bordercolor="#c0c0c0">Preprint</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">15</td> <td bordercolor="#c0c0c0" align="right">11</td> <td bordercolor="#c0c0c0">Convert</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">16</td> <td bordercolor="#c0c0c0" align="right">11</td> <td bordercolor="#c0c0c0">Data</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">17</td> <td bordercolor="#c0c0c0" align="right">14</td> <td bordercolor="#c0c0c0">Data</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">18</td> <td bordercolor="#c0c0c0" align="right">15</td> <td bordercolor="#c0c0c0">Sorts</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">19</td> <td bordercolor="#c0c0c0" align="right">16</td> <td bordercolor="#c0c0c0">Crushed Core</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">20</td> <td bordercolor="#c0c0c0" align="right">16</td> <td bordercolor="#c0c0c0">Slab Waste</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">21</td> <td bordercolor="#c0c0c0" align="right">16</td> <td bordercolor="#c0c0c0">Damaged Roll</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">22</td> <td bordercolor="#c0c0c0" align="right">2</td> <td bordercolor="#c0c0c0">Quality</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> <tr valign="top"> <td bordercolor="#c0c0c0" align="right">23</td> <td bordercolor="#c0c0c0" align="right">2</td> <td bordercolor="#c0c0c0">Computer</td> <td bordercolor="#c0c0c0" align="right">-1</td> </tr> </tbody> <tfoot></tfoot> </table>

TIA</text></number></auto>

 

[Cyberwolf000]

RESOLVED: Re: 2nd combobox rowsource based on 1st combobox in continuous form or datasheet

I used a dlookup on a locked text box