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.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!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
            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!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
                    For iActiveCurrDenoms = iActiveCurrDenoms To iMaxCurrDenoms
                        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
        ' **** End Create all Denoms for this Count Type ***
    ' **** End Create Each Active Currency Denomination TYPE ****
' **** End Create Each Active Shift Count Type ****
                Set rst2 = Nothing
    Set rst = Nothing
    Me.Form.Dirty = False

Open in new window

Who is Participating?
Dale FyeConnect With a Mentor Commented:
What is the range of values likely to be for your outer and inner loops?

I generally prefer to work with a table of numbers for stuff like this.  I generally create tbl_Numbers with a single field (intNumber) and only 10 records, the values 0-9.  From this table I can generate a query which gives me any set of numbers.  For example, the following query gives me values from 0 to 99:

SELECT Tens.intNumber * 10 + Ones.intNumber as intNumber
FROM tbl_Numbers Tens, tbl_Numbers Ones

I prefer this method, as it keeps the size of tbl_Numbers down to a minimum, and allows me to create an infinitely large set of numbers.  However, the other method is to just create the table and add as many records as you need.

Or, you can create a query that gives you every combination of two sets of numbers that range from o to 9:

SELECT T1.intNumber, T2.intNumber
FROM tbl_Numbers T1, tbl_Numbers T2

This latter method could be used to generate your set of combinations, something like:

INSERT INTO yourDestinationTable (Field1, Field2)
SELECT T1.intNumber, T2.IntNumber
FROM tbl_Numbers T1, tbl_Numbers T2
WHERE T1.intNumber BETWEEN 2 and 6
AND T2.intNumber BETWEEN 3 and 9

This is kind of generic, but I'm working from my iPad, so don't have quite the functionality as if I was at my computer.
wlwebbAuthor Commented:
I DIDN'T GET ANY ERRORS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

wlwebbAuthor Commented:
Thanks for the input... it set me off into the right direction
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.