?
Solved

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

Posted on 2013-01-21
3
Medium Priority
?
335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

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

;-))))))))))
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

771 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