Counting through vba

[ECEstudent]

Hi, I'm really stuck on this. Been looking at it for a few days now (even though I'm sure it's not that hard of a problem) and I would appreciate any help!

Here is my code:

                Set rstt = CurrentDb.OpenRecordset( _
                "Select Distinct RepId from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId", dbOpenDynaset, dbSeeChanges)
                  
                n = 0
                  
                  
                While rstt.EOF = False
                    ReDim Preserve Arry(n)
                    Arry(n) = rstt.Fields("RepId") 'Store RepIds in array
                    n = n + 1
                    rstt.MoveNext
                Wend
                y = Arry
              '  VldOrdrNbrDestination = 0
              '  OrderWOEditing = 0
                For Each varCod In y 'For each RepId in array
     
                    If DCount("[Location ID]", "[tbl_LBP_Sales Location Num]", "[Location ID] LIKE '*" & Left(varCod, 3) & "*' AND NOT [Rep Region Code] = 'INT' AND NOT [Rep Region Code] = 'inte'") > 0 Then  'Only count RepIds that exist in table 'tbl_LBP_Sales Location Num' and is not an international order
                                 
                                     
                                     VldOrdrNbrDestination = VldOrdrNbrDestination + 1 'Calculate total number of orders
                                     
                                    ' MsgBox " Rep = '" & varCod & "'"
                             
                                    ' MsgBox "Structure = '" & u & "'"
                                     
                                     Set ry = CurrentDb.OpenRecordset( _
                                     "SELECT OrderNumber, Item, RepId from CalculateTotal where ([RepId] = '" & varCod & "') AND ([Structure] like '*" & u & "*')", dbOpenDynaset, dbSeeChanges)
                                         
                                         
                                     
                                     
                                     While ry.EOF = False
                                         ReDim Preserve Arrk(e)
                                         ReDim Preserve Arra(e)
                                         ReDim Preserve ArrRep(e)
                                         Arrk(e) = ry.Fields("OrderNumber") 'Collect its order numbers
                                         Arra(e) = ry.Fields("Item")
                                         ArrRep(e) = ry.Fields("RepId")
                                         e = e + 1
                                         ry.MoveNext
                                     Wend
                                     q = Arrk
                                     v = Arra
                             
                    End If
                       
             Next varCod ' next RepId

So what's going on is that I'm first taking all the 'RepID' associated with some code from above (not important) and put them in an array...then I'm looping through that array and really only trying to find all the 'RepIds' that exist in table 'tbl_LBP_Sales Location Num', I should then start collecting all the order numbers and Items associated with those 'RepIds''. I understand I could have just collected all of those variables from the very beginning since they're using the same table but I am ONLY interested in the ones associated with the RepIds' that exist in table 'tbl_LBP_Sales Location Num'. And the code that comes after this step isn't important for this step.

My problem is that I have 2 things to count: 1) the number of RepIds' found in that table (and I'm calling that total 'VldOrdrNbrDestination') and 2) the OrderNumber + Item + RepId combination


Please notice that with SELECT DISTINCT at the top, I am getting the wrong RepId total and with out SELECT DISTINCT, I am getting the wrong combination total.


Please help. Thanks.

 

[Cronk]

Please notice that with SELECT DISTINCT at the top, I am getting the wrong RepId total and with out SELECT DISTINCT, I am getting the wrong combination total.

You can fix this by removing all fields except RepId from the GROUP list

Also, why do you store all the resulting values in an array in a loop and then loop through the array? Just put everything in the second loop in the first.

 

[ECEstudent]

i need the group by statement because it gets rid of the duplicate OrderNumber + Item + RepId combinations.

The reason why I am storing the RepIds in an array and then looping through them to collect the OrderNumbers and Items is because I only want the OrderNumbers and Items associated with RepIds that 'pass the test' with the test being that they exist in table [tbl_LBP_Sales Location Num] and that they do not have 'Rep Region Code' = 'INT'.

 

[Cronk]

Then why not base the initial recordset on tbl_LBP_Sales Location Num rather than a query.

The following will give all the values of RepID that you want based on your requirements in your post
SELECT RepID from tbl_LB_SalesLocationNum WHERE [Rep Region] <> 'INT' GROUP BY RepID

 

[ECEstudent]

lol I think I'm only confusing you...Maybe the rest of my code will help.

Private Sub Command1_Click()
Dim txtPartNumber As Variant
Dim rst As Recordset
Dim ry As Recordset
Dim rn As Recordset
Dim rstt As Recordset
Dim u As Variant
Dim i As Integer
Dim e As Long
Dim n As Integer
Dim Arr() As String
Dim Arry() As String
Dim Arrk() As String
Dim Arrt() As String
Dim Arra() As String
Dim x As Variant
Dim q As Variant
Dim v As Variant
Dim y As Variant
Dim varCode As Variant
Dim ItemQ As Variant
Dim varCod As Variant
Dim Order As Variant
Dim Rep As Variant
Dim ItemQuantity As Integer
Dim Count As Integer
Dim Count2 As Integer
Dim VldOrdrNbrDestination As Integer
Dim LResponse As Integer
Dim sum As Integer
Dim OrderWOEditing As Integer
Dim strSQL As String
Dim strSQL2 As String
Dim strTable As String
Dim b As Integer
Dim k As Variant
Dim Arrn() As String
Dim Arru() As String
Dim ArrRep() As String
Dim f As Variant
Dim h As Variant
Dim d As Integer
Dim OrderItem As Variant
Dim ItemOrder As Variant
Dim RepOrderItem As Variant
b = 0
MsgBox "Please wait. Processing"
txtPartNumber = BtnRun 'Textbox on SearchPartNumber form. User enters Part Number Value
strTable = "tblTest2" 'Table for storing values for viewing purposes
DoCmd.DeleteObject acTable, strTable 'Delete tblTest2 old values
CurrentDb.Execute "CREATE TABLE tblTest2 " _
& "(RepId CHAR, OrderNumber CHAR, Item CHAR, ProductNbr CHAR, Name CHAR, [Rep Region Code] CHAR);" 'Rebuild tblTest2 for use
If Not txtPartNumber = "" Then 'Proceed as long as there's a value in txtbox BtnRun
    VldOrdrNbrDestination = 0
    sum = 0
        
    If DCount("ChildProductNbr", "dbo_ProductStructure", "[ChildProductNbr] = '" & txtPartNumber & "'") > 0 Then 'Proceed if Child Product Number exists in table dbo_ProductStructure
       ' Count = DCount("ChildProductNbr", "dbo_ProductStructure", "[ChildProductNbr] = '" & txtPartNumber & "'") 'Count of existing Child Product Numbers
 
        Set rst = CurrentDb.OpenRecordset( _
        "Select * from dbo_ProductStructure where ChildProductNbr= '" & txtPartNumber & "'")
    
        While rst.EOF = False
            ReDim Preserve Arr(i)
            Arr(i) = rst.Fields("ParentProductNbr") 'Store Parent Product Numbers in array
            i = i + 1
            rst.MoveNext
        Wend
        x = Arr
            
       
            
        For Each varCode In x 'For each Parent Product Number in array
            sum = sum + 1
              
            d = 0
            e = 0
            
            varCode = Replace(varCode, "-", "*") 'Modify Parent Product Numbers for Structure search
            
            varCode = Trim(varCode) 'trim extra spaces from varCode
        
            u = varCode
            If DCount("Structure", "CalculateTotal", "[Structure] like '*" & u & "*'") > 0 Then 'Proceed if Structure exists in table CalculateTotal
                
                'Count2 = DCount("Structure", "CalculateTotal", "[Structure] like '*" & u & "*'") 'Count of existing Structures
                 
                Set rstt = CurrentDb.OpenRecordset( _
                "Select RepId from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId", dbOpenDynaset, dbSeeChanges)
                  
                n = 0
                  
                While rstt.EOF = False
                    ReDim Preserve Arry(n)
                    Arry(n) = rstt.Fields("RepId") 'Store RepIds in array
                    n = n + 1
                    rstt.MoveNext
                Wend
                y = Arry
              '  VldOrdrNbrDestination = 0
              '  OrderWOEditing = 0
                For Each varCod In y 'For each RepId in array
              '      OrderWOEditing = OrderWOEditing + 1
                        
                        
               If DCount("[Location ID]", "[tbl_LBP_Sales Location Num]", "[Location ID] LIKE '*" & Left(varCod, 3) & "*' AND NOT [Rep Region Code] = 'INT' AND NOT [Rep Region Code] = 'inte'") > 0 Then  'Only count RepIds that exist in table 'tbl_LBP_Sales Location Num' and is not an international order
                            
                                
                                VldOrdrNbrDestination = VldOrdrNbrDestination + 1 'Calculate total number of orders
                                
                               ' MsgBox " Rep = '" & varCod & "'"
                        
                               ' MsgBox "Structure = '" & u & "'"
                                
                                Set ry = CurrentDb.OpenRecordset( _
                                "SELECT OrderNumber, Item, RepId from CalculateTotal where ([RepId] = '" & varCod & "') AND ([Structure] like '*" & u & "*')", dbOpenDynaset, dbSeeChanges)
                                    
                                    
                                
                                
                                While ry.EOF = False
                                    ReDim Preserve Arrk(e)
                                    ReDim Preserve Arra(e)
                                    ReDim Preserve ArrRep(e)
                                    Arrk(e) = ry.Fields("OrderNumber") 'Collect its order numbers
                                    Arra(e) = ry.Fields("Item")
                                    ArrRep(e) = ry.Fields("RepId")
                                    e = e + 1
                                    ry.MoveNext
                                Wend
                                q = Arrk
                                v = Arra
                        
                    End If
                       
                Next varCod
                    
               ' MsgBox "After tbl Location Num search '" & VldOrdrNbrDestination & "'"
        
                
                
                Do Until d = e
                    
                    OrderItem = Arrk(d)
                    ItemOrder = Arra(d)
                    RepOrderItem = ArrRep(d)
                    
                    MsgBox "OrderItem = '" & OrderItem & "'"
                    MsgBox "ItemOrder = '" & ItemOrder & "'"
                    MsgBox "RepOrderItem = '" & RepOrderItem & "'"
                    
                     OrderItem = Trim(OrderItem)
                      
                     Set rn = CurrentDb.OpenRecordset("Select ItemQty From dbo_Item Where(OrderNumber LIKE '" & RepOrderItem & "*') AND (OrderNumber LIKE '*" & OrderItem & "') AND (ItemNumber = '" & ItemOrder & "')")
                      
                     While rn.EOF = False
                      
                        ReDim Preserve Arru(f)
                        Arru(f) = rn.Fields("ItemQty")
                        f = f + 1
                        rn.MoveNext
                        
                    Wend
                    h = Arru

                    d = d + 1
                    
               Loop
               
              
                
                If sum = 1 Then
              
                   ' strSQL = ("Select OrderNumber, Item, RepId INTO " & strTable & " from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId")
                Else
              
                strSQL = ("INSERT INTO " & strTable & " Select c.OrderNumber, c.Item, c.RepId, p.ProductNbr, p.Name, [tbl_LBP_Sales Location Num].[Rep Region Code] FROM CalculateTotal c, dbo_PartNew p, [tbl_LBP_Sales Location Num] WHERE ([Structure] like '*" & u & "*') AND ([ProductNbr] = '" & txtPartNumber & "')")
                CurrentDb.Execute strSQL
  
                End If
        
    
            End If
        
       Erase Arry
       Erase Arrk
       Erase Arra
       
      ' MsgBox "Item Quantity = '" & ItemQuantity & "'"
       
        Next varCode 'For each parent product number

                For Each ItemQ In h
                      
                    'MsgBox "ItemQ = '" & ItemQ & "'"
                    ItemQuantity = ItemQuantity + ItemQ
                    'MsgBox "ItemQuantity = '" & ItemQuantity & "'"
                         
                Next ItemQ
        
        MsgBox "Total Number of Orders = '" & VldOrdrNbrDestination & "'"
        
        MsgBox "Item Quantity = '" & ItemQuantity & "'"
        
        LResponse = MsgBox("Do you wish to view table?", vbYesNo, "")
            
        If LResponse = vbYes Then
            
            MsgBox "Chose to view table"
            DoCmd.OpenTable "tblTest2", acViewPreview
            'strSQL = ("Select OrderNumber, Item, RepId INTO " & strTable & " from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId")
            'CurrentDb.Execute strSQL
  
        Else
            
            MsgBox "Cancel table preview"
                
        End If
    Else
    
        MsgBox "Part Number Not Found"
    End If
        
Else
        MsgBox "Enter Part Number"
End If
End Sub

 

[Cronk]

"Select RepId from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId", dbOpenDynaset, dbSeeChanges)
is going to give the same RepID for every combination of OrderNumber and Item. Is that what you want?

Your use of arrays just complicates your code to an extent I can't follow it. (No doubt smarter people could.

You loop through a recordset, store all of the values in an array, give the array an obscure name and then loop through the array. Remove the array and put all of the code that you execute looping through the array in the original recordset loop.

 

[ECEstudent]

I think I got it figured out