Solved

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

Posted on 2013-01-21
3
317 Views
Last Modified: 2013-02-02
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

Open in new window

0
Comment
Question by:wlwebb
  • 2
3 Comments
 

Author Comment

by:wlwebb
ID: 38802280
Hey BUT THE GOOD PART....
I DIDN'T GET ANY ERRORS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

;-))))))))))
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 38803398
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.
0
 

Author Closing Comment

by:wlwebb
ID: 38847965
Thanks for the input... it set me off into the right direction
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now