Access - Record Set For Statement loop... Incorrect number of records being created.

Hello All

As some here know I am changing some coding to get my code to create records instead of using Form Based creation of the new records...  With that I am changing some of the code to Recordset..... (not sure if that is the right terminology)

Anyway, my code (as I have attempted it) is not creating the right number of records.

For the 1st Recordset (starting at Line 24) rst (from ctldta_ShiftCountType) I expected it to create 4 records in that table in the field [ShiftCountType]  They should have been ShiftCountTypeID#s 2 and 3 EACH WITH CurrencyTypeID#s 1 and 2.  

It only is creating with ShiftCountTypeID#1 (yes # 1 instead of what I expected #2 and #3) with CurrencyTypeID#s 1 and 2.....


For the 2nd Recordset (Starting at Line 41) rst2 (from dta_ShiftCashCountDetails)  I expected for now 4 sets of 7 records to be created.  What I got was only 1 set of 7 records.

It should have created a set of 7 records for
ShiftCountTypeID # X (where X is the record created in Recordset 1 above)
ShiftCountTypeID # X +1
ShiftCountTypeID # X +2
ShiftCountTypeID # X +3

To accomplish that I moved the First Recordset's NEXT (to line 65) stmt down past the Recordset 2s record add... Thought that would work.......guess not.......

This is my code.......

    Dim iActiveShiftCountTypes As Integer, iMaxShiftCountTypes As Integer
    Dim iActiveCurrType As Integer, iMaxCurrType As Integer
    Dim iActiveCoinDenoms As Integer, iActiveCurrDenoms As Integer, iMaxCoinDenoms As Integer, iMaxCurrDenoms As Integer
    Dim iNewShiftCountTypeID As Integer
    Dim lngShiftCountTypes As Long
    Dim lngCoinDenoms As Long, lngCurrDenoms As Long
    Dim rst As Recordset, rst2 As Recordset
    
    iActiveShiftCountTypes = 1
    iActiveCurrType = 1
    iActiveCoinDenoms = 1
    iActiveCurrDenoms = 1
    
    iMaxShiftCountTypes = DCount("ShiftCountTypeID", "sysctl_ShiftCountType", "ShiftCountTypeInactive = 0 AND ShiftCountTypeID > 1") ' Count the number of Shift Count Types - Need an input Grandchild for Each Type
    iMaxCurrType = DCount("CurrencyTypeID", "sysctl_CurrencyType", "CurrencyTypeActive = 0") ' Count the number of Currency Types - Coin / Currency
    iMaxCoinDenoms = DCount("CurrencyDenominationsID", "sysctl_CurrencyDenominations", "CurrencyDenominationInactive = 0 AND CurrencyTypeID = 1") ' Count Number of Coin Denominations active
    iMaxCurrDenoms = DCount("CurrencyDenominationsID", "sysctl_CurrencyDenominations", "CurrencyDenominationInactive = 0 And CurrencyTypeID = 2") ' Count the Number of Currency Denominations active
    
    Me.TabCtlCashCount.Visible = True
    Me.tsfrm_ShiftCashCount_TempCoins.SetFocus
    Me.cmdNoTempFund.Visible = False
    Me.cmdTempFund.Visible = False
    
    Set rst = CurrentDb.OpenRecordset("SELECT * From ctldta_ShiftCountType")
' **** Create Each Active Shift Count Type ****
    For iActiveShiftCountTypes = iActiveShiftCountTypes To iMaxShiftCountTypes
        
    ' **** Create Each Active Currency Denomination TYPE ****
        For iActiveCurrType = iActiveCurrType To iMaxCurrType
            rst.AddNew
            rst!ShiftCashCountID = Me.ShiftCashCountID
            Me.cboShiftCountTYpe.RowSource = "SELECT ShiftCountTypeID, ShiftCountType, ShiftCountTypeInactive FROM sysctl_ShiftCountType WHERE (((ShiftCountTypeID)>1) AND ((ShiftCountTypeInactive)=0)) "
            Me.cboShiftCountTYpe = Me.cboShiftCountTYpe.ItemData(iActiveShiftCountTypes - 1)
            lngShiftCountTypes = Me.cboShiftCountTYpe.ItemData(iActiveCurrType - 1)
            rst!ShiftCountType = iActiveShiftCountTypes
            rst!CurrencyTypeID = iActiveCurrType
            rst.Update
            iNewShiftCountTypeID = DMax("ShiftCountTypeID", "ctldta_ShiftCountType", "ShiftCountTypeID")
            
        ' **** Create all Denoms for this Count Type ***
                Set rst2 = CurrentDb.OpenRecordset("SELECT * From dta_ShiftCashCountDetails")
                    
                    For iActiveCoinDenoms = iActiveCoinDenoms To iMaxCoinDenoms
                        rst2.AddNew
                        rst2!ShiftCountTypeID = iNewShiftCountTypeID
                        Me.cboDenomsCoins.RowSource = "SELECT CurrencyDenominationsID, CurrencyDenominations, CurrencyAmt, CurrencyTypeID, CurrencyDenominationInactive, SeqID FROM qry_sysctl_CurrencyDenominations_CoinsActiveSeq WHERE (((CurrencyTypeID)=1) AND ((CurrencyDenominationInactive)=0)) "
                        Me.cboDenomsCoins = Me.cboDenomsCoins.ItemData(iActiveCoinDenoms - 1)
                        lngCoinDenoms = Me.cboDenomsCoins.Column(0)
                        rst2!CurrencyDenominationsID = lngCoinDenoms
                        rst2!CountAmt = 0
                        rst2.Update
                    Next
                    
                    For iActiveCurrDenoms = iActiveCurrDenoms To iMaxCurrDenoms
                        rst2.AddNew
                        rst2!ShiftCountTypeID = iNewShiftCountTypeID
                        Me.cboDenomsCurrency.RowSource = "SELECT CurrencyDenominationsID, CurrencyDenominations, CurrencyAmt, CurrencyTypeID, CurrencyDenominationInactive, SeqID FROM qry_sysctl_CurrencyDenominations_CurrencyActiveSeq WHERE (((CurrencyTypeID)=2) AND ((CurrencyDenominationInactive)=0)) "
                        Me.cboDenomsCurrency = Me.cboDenomsCurrency.ItemData(iActiveCurrDenoms - 1)
                        lngCurrDenoms = Me.cboDenomsCurrency.Column(0)
                        rst2!CurrencyDenominationsID = lngCurrDenoms
                        rst2!CountAmt = 0
                        rst2.Update
                    Next
        ' **** End Create all Denoms for this Count Type ***
        Next
    ' **** End Create Each Active Currency Denomination TYPE ****
    Next
' **** End Create Each Active Shift Count Type ****
                rst2.Close
                Set rst2 = Nothing
    rst.Close
    Set rst = Nothing
    
    Me.Form.Dirty = False
    
    Me.tsfrm_ShiftCashCount_TempCoins.Requery
    Me.tsfrm_ShiftCashCount_TempCurrency.Requery
    Me.tsfrm_ShiftCashCount_RemainingFundCoins.Requery
    Me.tsfrm_ShiftCashCount_RemainingFundCurrency.Requery
    
    Me.tsfrm_ShiftCashCount_TempCoins!tsfrm_dta_ShiftCashCountDetails_TempPayoutCoins.Requery
    Me.tsfrm_ShiftCashCount_TempCurrency!tsfrm_dta_ShiftCashCountDetails_TempPayoutCurrency.Requery

Select allOpen in new window