Continuous subform using a DAO recordset/ Access 2000

Hello Experts,
I am stumped on this one, and I would appreciate your expert assistance.  The problem with my current code is that, although the continuous subform in question runs without error, the underlying recordset code does not execute the looping porton of the code.  It processes the first record in the loop, and then stops.

The subform with the problem, is the second of 2 embedded continuous subforms.  The way that it is supposed to work is as follows:  the main form has two cascading combo boxes, which combine to display (on the first subform) a filtered list of account codes associated to a selected line item (example- all general ledger codes for a line item code of 108 entitled “Accounts Receivable”).  The second subform, is intended to enable the user to select from another combo box (one combo for each record displayed on the continuous subform) so that users can change the value of the bound column (line item code) to which each of the accounts are associated, with the change being automatically propagated to all accounts on the filtered list after the first selection is made.  Although I can change each account one by one by making a selection in each combo box, I have not been successful in taking the first selection and propagating it to all of the account codes on the filtered list.  Here is the pertinent code for the second subform.

The SQL code that was used to create the recordset for subform2:
----------------------------------------------------------------------------------------------------------------

strSQLSF2 = "SELECT " & "[tblChartOfAccts]" & "." & varAcctClass & "," & "[tblChartOfAccts]" & "." & varAcctCode2
strSQLSF2 = strSQLSF2 & "," & "[tblChartOfAccts]" & "." & varCafrLink2 & " From [tblChartOfAccts]"
strSQLSF2 = strSQLSF2 & "  WHERE [tblChartOfAccts]" & "." & varAcctCode2 & " IN " & "(Select " & varAcctCode & " From TempQry)"
strSQLSF2 = strSQLSF2 & " ORDER BY " & varAcctCode2


-------------------------------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit

Dim iOldBoundColumnValue As Integer
Dim iNewBoundColumnValue As Integer
Dim sNewBoundColumnDescr As String

Private Sub cboAcctClass_Change()
On Error GoTo Error_Routine

Dim dBs As DAO.Database
Dim rst As DAO.Recordset

Dim iRecCount As Integer             'record count
Dim intReturn As Integer               'return from message box

Me.Parent!cboInquiryFinder.Requery
Me.Parent!frmChartUpdate_DetailCtl.Form![txtAcctTitle].Requery

'open recordset clone to handle combo box processing
Set dBs = CurrentDb()
Set rst = Forms("frmChartUpdate_Main").Controls("frmChartUpdate_DetailCtl2").Form.RecordsetClone

'get record count
iRecCount = rst.RecordCount

If Not (rst.BOF Or rst.EOF) Then
   With rst
        rst.MoveFirst
        'capture selection from combo box
        Me.cboAcctClass.SetFocus
        iNewBoundColumnValue = Me!cboAcctClass.Value
        sNewBoundColumnDescr = Me!cboAcctClass.Column(1)

        intReturn = MsgBox("You are about to change the report classification to " & sNewBoundColumnDescr & " for all " & iRecCount & " records.", vbOKCancel)
        If intReturn = vbCancel Then
            MsgBox "You must use the Chart of Accounts to make selective changes."
             cboAcctClass.Undo
            Resume Exit_Continue
        Else
            'rst.MoveNext
            'change all rows to new value of cboSelectIdx
            Do Until rst.EOF
               rst.Edit
                   With cboAcctClass
                             'Retrieves the value of the bound column which may or may not be
                                    'displayed in the list box
                             If iOldBoundColumnValue <> iNewBoundColumnValue Then
                                  Me!cboAcctClass.Value = iNewBoundColumnValue
                             End If
                    End With
               rst.Update
               Me!cboAcctClass.Requery
              ' Forms("frmChartUpdate_Main").Controls("frmChartUpdate_DetailCtl2").Form.Requery
               If iRecCount > 1 Then
                    rst.MoveNext
               Else
                    Exit Do
               End If
            Loop
       End If
   End With
End If
rst.Close
Exit_Continue:
    'clean up
    Set rst = Nothing
    iRecCount = 0
    iOldBoundColumnValue = Empty
    iNewBoundColumnValue = Empty
    sNewBoundColumnDescr = Empty
    intReturn = 0
    Exit Sub
Error_Routine:
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue
End Sub

Private Sub cboAcctClass_Enter()
On Error GoTo Error_Routine
     If Not IsNull(Me!cboAcctClass.Value) Then
         iOldBoundColumnValue = Me!cboAcctClass.Value
     End If
   
Exit_Continue:
        Exit Sub
Error_Routine:
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue

End Sub
Start Free Trial