Increment Loop

PC User

PC User

Registered User.
Local time
Today, 13:14
Joined
Jul 28, 2002
Messages
193
I'm having trouble with my loop indexing. I can't seem to get the counter "j" to number the array correctly. The numbering should be sequential. Can someone help?

Code: 
Public Function InsertLabels()
    Dim intCount As Integer
    Dim intRecCount1 As Integer, intRecCount2 As Integer
    Dim intChemicalID As Integer, intComponentID As Integer
    Dim strComponentName As String
    Dim i As Integer, j As Integer
   
    Set Db = CurrentDb()

    Set rst1 = Db.OpenRecordset("tabUNIDOCSfields", dbOpenDynaset) 'Source/Target
 
        rst1.MoveLast
        rst1.MoveFirst
        intRecCount1 = rst1.RecordCount
             j = 0
             
    rst1.MoveFirst
    Do Until rst1.EOF
            intChemicalID = rst1!ChemicalID
            
                strSelect = "SELECT [tabUNIDOCSfields].* "
                strFrom = "FROM [tabUNIDOCSfields] "
                strWhere = "WHERE tabUNIDOCSfields.ChemicalID = " & intChemicalID
                strSQL = strSelect & strFrom & strWhere
                
                Debug.Print "ChemicalID = " & intChemicalID

                Set rst2 = Db.OpenRecordset(strSQL)  'Subsource
                rst2.MoveLast
                rst2.MoveFirst
                intRecCount2 = rst2.RecordCount
                intCount = DCount("HazardousComponentID", "tabUNIDOCSfields", "ChemicalID = " & intChemicalID)
                intChemicalID = rst1!ChemicalID
                Debug.Print "Number of Components = " & intCount
                Debug.Print "Records of Components = " & intRecCount2
                    Do Until rst2.EOF
                        intComponentID = rst1!HazardousComponentID
                        strComponentName = rst1!COMPONENT_NAME
                        Debug.Print "ComponentID = " & intComponentID & " " & strComponentName
                            j = j + 1
                            rst1.Edit
                            rst1![HEADING1] = "COMPONENT" & (j) & "_PERCENT"
                            rst1![HEADING2] = "COMPONENT" & (j) & "_NAME"
                            rst1![HEADING3] = "COMPONENT" & (j) & "_EHS"
                            rst1![HEADING4] = "COMPONENT" & (j) & "_CAS"
                            rst1.Update
                        rst2.MoveNext
                    Loop
            j = 0
    rst1.MoveNext
    Loop
    
    rst1.Close
    Set rst1 = Nothing
    Db.Close
    Set Db = Nothing

End Function
Thanks,
PC
 
Why even do it manually?

Code: 
j = 1000

For i = 1 to j
   'Do something
Next i

Or even faster if you want iterate over everything in recordset:

Code: 
Do until rst.EOF=true
   'Do Something
   rst.MoveNext   'This should be the last thing in the loop
Loop
 
and just so you know what you had wrong in your original code - You reset j to 0 each time by using:
Code: 
          [color=red] j = 0[/color]
    rst1.MoveNext
    Loop

It SHOULD be (if using this method):
Code: 
           [color=blue]j = j + 1[/color]
    rst1.MoveNext
    Loop
 
Thanks for your replies. I made the change and the counter is counting right; however, the loop is not looping the way I intended. Instead of looping through each of the components, its repeating the same component the number of times equal to the number of components. See code and note.
Code: 
Public Function InsertLabels()
    Dim intCount As Integer
    Dim intRecCount1 As Integer, intRecCount2 As Integer
    Dim intChemicalID As Integer, intComponentID As Integer
    Dim strComponentName As String
    Dim i As Integer, j As Integer
   
    Set Db = CurrentDb()

    Set rst1 = Db.OpenRecordset("tabUNIDOCSfields", dbOpenDynaset) 'Source/Target
              
    rst1.MoveFirst
    Do Until rst1.EOF = True
            intChemicalID = rst1!ChemicalID
            j = 1
                strSelect = "SELECT [tabUNIDOCSfields].* "
                strFrom = "FROM [tabUNIDOCSfields] "
                strWhere = "WHERE tabUNIDOCSfields.ChemicalID = " & intChemicalID
                strSQL = strSelect & strFrom & strWhere
                
                Debug.Print "ChemicalID = " & intChemicalID

                intCount = DCount("HazardousComponentID", "tabUNIDOCSfields", "ChemicalID = " & intChemicalID)
                intChemicalID = rst1!ChemicalID
                Debug.Print "Number of Components = " & intCount

                    Do Until rst2.EOF = True
                        intComponentID = rst1!HazardousComponentID
                        strComponentName = rst1!COMPONENT_NAME
                        Debug.Print "ComponentID = " & intComponentID & " " & strComponentName
                            
                            rst1.Edit
                            rst1![HEADING1] = "COMPONENT" & (j) & "_PERCENT"
                            Debug.Print "HEADING1 = " & rst1![HEADING1]
                            rst1![HEADING2] = "COMPONENT" & (j) & "_NAME"
                            rst1![HEADING3] = "COMPONENT" & (j) & "_EHS"
                            rst1![HEADING4] = "COMPONENT" & (j) & "_CAS"
                            rst1.Update
                            j = j + 1
                    rst2.MoveNext
                    Loop

    rst1.MoveNext
    Loop
    
    rst1.Close
    Set rst1 = Nothing
    Db.Close
    Set Db = Nothing

End Function
ChemicalID = 474
Number of Components = 5
ComponentID = 462 Propane
HEADING1 = COMPONENT1_PERCENT
ComponentID = 462 Propane
HEADING1 = COMPONENT2_PERCENT
ComponentID = 462 Propane
HEADING1 = COMPONENT3_PERCENT
ComponentID = 462 Propane
HEADING1 = COMPONENT4_PERCENT
ComponentID = 462 Propane
HEADING1 = COMPONENT5_PERCENT

ChemicalID = 474
Number of Components = 5
ComponentID = 463 Butane
HEADING1 = COMPONENT1_PERCENT
ComponentID = 463 Butane
HEADING1 = COMPONENT2_PERCENT
ComponentID = 463 Butane
HEADING1 = COMPONENT3_PERCENT
ComponentID = 463 Butane
HEADING1 = COMPONENT4_PERCENT
ComponentID = 463 Butane
HEADING1 = COMPONENT5_PERCENT

ChemicalID = 474
Number of Components = 5
ComponentID = 464 Heavy Naphthenic Petroleum Oil
HEADING1 = COMPONENT1_PERCENT
ComponentID = 464 Heavy Naphthenic Petroleum Oil
HEADING1 = COMPONENT2_PERCENT
ComponentID = 464 Heavy Naphthenic Petroleum Oil
HEADING1 = COMPONENT3_PERCENT
ComponentID = 464 Heavy Naphthenic Petroleum Oil
HEADING1 = COMPONENT4_PERCENT
ComponentID = 464 Heavy Naphthenic Petroleum Oil
HEADING1 = COMPONENT5_PERCENT

ChemicalID = 474
Number of Components = 5
ComponentID = 465 Mineral Spirits
HEADING1 = COMPONENT1_PERCENT
ComponentID = 465 Mineral Spirits
HEADING1 = COMPONENT2_PERCENT
ComponentID = 465 Mineral Spirits
HEADING1 = COMPONENT3_PERCENT
ComponentID = 465 Mineral Spirits
HEADING1 = COMPONENT4_PERCENT
ComponentID = 465 Mineral Spirits
HEADING1 = COMPONENT5_PERCENT

ChemicalID = 474
Number of Components = 5
ComponentID = 466 Hexane
HEADING1 = COMPONENT1_PERCENT
ComponentID = 466 Hexane
HEADING1 = COMPONENT2_PERCENT
ComponentID = 466 Hexane
HEADING1 = COMPONENT3_PERCENT
ComponentID = 466 Hexane
HEADING1 = COMPONENT4_PERCENT
ComponentID = 466 Hexane
HEADING1 = COMPONENT5_PERCENT
ChemicalID = 476
Click to expand...
When it does this, the component label keeps getting written over and the end result is a label with the highest value of the iteration. What I need to get are results like this.
ChemicalID = 474
Number of Components = 5
ComponentID = 462 Propane
HEADING1 = COMPONENT1_PERCENT


ChemicalID = 474
Number of Components = 5
ComponentID = 463 Butane
HEADING1 = COMPONENT2_PERCENT

ChemicalID = 474
Number of Components = 5
ComponentID = 464 Heavy Naphthenic Petroleum Oil
HEADING1 = COMPONENT3_PERCENT


ChemicalID = 474
Number of Components = 5
ComponentID = 465 Mineral Spirits
HEADING1 = COMPONENT4_PERCENT


ChemicalID = 474
Number of Components = 5
ComponentID = 466 Hexane
HEADING1 = COMPONENT5_PERCENT
Click to expand...
However, I'm not sure how to change my code to get this. Can anyone help?

Thanks,
PC
 
Its amazing what a little self determination can come up with.
Code: 
Public Function InsertLabels()
    Dim intCount As Integer
    Dim intRecCount1 As Integer, intRecCount2 As Integer
    Dim intChemicalID As Integer, intComponentID As Integer
    Dim strComponentName As String
    Dim i As Integer, j As Integer
   
    Set Db = CurrentDb()

    Set rst1 = Db.OpenRecordset("tabUNIDOCSfields", dbOpenDynaset) 'Source/Target
 
        rst1.MoveLast
        rst1.MoveFirst
        intRecCount1 = rst1.RecordCount
             
    rst1.MoveFirst
    Do Until rst1.EOF
            intChemicalID = rst1!ChemicalID
            
                strSelect = "SELECT [tabUNIDOCSfields].* "
                strFrom = "FROM [tabUNIDOCSfields] "
                strWhere = "WHERE tabUNIDOCSfields.ChemicalID = " & intChemicalID
                strSQL = strSelect & strFrom & strWhere
                
                Debug.Print "ChemicalID = " & intChemicalID

                Set rst2 = Db.OpenRecordset(strSQL)  'Subsource
                rst2.MoveLast
                rst2.MoveFirst
                intRecCount2 = rst2.RecordCount
                intCount = DCount("HazardousComponentID", "tabUNIDOCSfields", "ChemicalID = " & 

intChemicalID)
                intChemicalID = rst1!ChemicalID
                Debug.Print "Number of Components = " & intCount
                Debug.Print "Records of Components = " & intRecCount2

                     For j = 1 To rst2.RecordCount
                        intComponentID = rst1!HazardousComponentID
                        strComponentName = rst1!COMPONENT_NAME
                        Debug.Print "ComponentID = " & intComponentID & " " & strComponentName
                            
                            rst2.Edit
                            rst2![HEADING1] = "COMPONENT" & (j) & "_PERCENT"
                            Debug.Print "HEADING1 = " & rst2![HEADING1]
                            rst2![HEADING2] = "COMPONENT" & (j) & "_NAME"
                            rst2![HEADING3] = "COMPONENT" & (j) & "_EHS"
                            rst2![HEADING4] = "COMPONENT" & (j) & "_CAS"
                            rst2.Update
                            
                        rst2.MoveNext
                     Next j

    rst1.MoveNext
    Loop
    
    rst1.Close
    Set rst1 = Nothing
    Db.Close
    Set Db = Nothing

End Function
 
You must log in or register to reply here.

Similar threads

A
Solved checkbox placed in frame by a query, not responding
Replies
1
Views
810
tvanstiphout
T
B
Solved Error 6 Overflow
2
Replies
26
Views
2,130
Gasman
Gasman
K
Solved Opening a recordset and filtering it
Replies
14
Views
1,874
CJ_London
C
P
Exit Do loop
Replies
8
Views
1,448
gemma-the-husky
gemma-the-husky
P
Solved Adding blank rows to Grouped Report
Replies
14
Views
1,735
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom