Use VBA to change cell values in recordset

Access 2007 using VBA:  Have a table built with a cross-tab query with qty values in columns whose names change constantly.  The ROW always contains in the first column, Column(0), the Customer.  The second column, Column(1) is always the total shipped and each column thereafter represents a city to which the qty is shipped, i.e....

CUSTOMER         TTL SHIPPED          CITY_1       CITY_2      CITY_3      CITY_(i)
Customer_1            1200                                     600                            600
Customer_2              300                    300
Customer_3            1000                      25                            975
Customer_(x)              10                                       5                                 5

OBJECTIVE:  Cycle through the recordset by column and change the current value from what it is to current value/total shipped to get a percentage value relative to the ROW TTL_SHIPPED not the sum of the column.  I am using the following code.  I get an "Invalid use of NULL" error.  The (i) does increment but I can't get by the value change.      

Private Sub Form_Load()
 
    '////// For recalculating quantities as a percent of Total Shipped.  ///////
    Dim rs As Object, varPctShip As Single, varCurrVal As Single
    
    varPctShip = 0
    varCurrVal = 0
 
    Set rs = CurrentDb.OpenRecordset("tbl_temp_freight_sort_cost")
    
    With rs
        
        rs.MoveFirst
 
            '////// Cycle through columns, regardless of name and number.  //////////
            '       1st Column is referenced as column zero.
            
            For i = 2 To rs.Fields.Count - 1
            
                varCurrVal = rs.Fields(i).Value
                
                If varCurrVal <> 0 Then
                
                varPctShip = varCurrVal / (rs.Fields(1).Value)
                
                .Edit
                !rs.Fields(i) = varPctShip
                .Update
                
                End If
            
            Next
 
        rs.MoveNext
        
        End With
 
End Sub

Open in new window

dynamic-columns.jpg
jdfullerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jdfullerAuthor Commented:
Maybe this is available in the cross-tab query that builds the table in the first place.  I couldn't get that to work because I needed the total calculated prior to running the query i nthe first place.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You need to check for NULL or 0 values:

For i = 2 To rs.Fields.Count - 1
  '/Fields(i) and Fields(1) should be both NOT NULL and <> 0
  If Nz(rs.Fields(i),0) <> 0 AND Nz(rs.Fields(1),0) <> 0 Then
    .Edit
    !rs.Fields(i) = rs.Field(i) / rs.Fields(1)
    .Update
  End If

Next i
           
0
jdfullerAuthor Commented:
The code fails at line 20 before the valuation of the variable.

The Bang after the .Edit gives an error of item not found so I removed it and this worked BUT for only one record.  

I followed the code for the first record and it cycles fine until it gets to the last column then goes to rs.MoveNext and fails to move to the next record and cycle again.  

I have added a check on the recordset to make sure it doesn't run out until it's done but this has sent it into an endless loop for some reason.  I got it to stop and it doesn't seem to like the 'method' referenced in 'recordset_2'.  I have no idea where it came up with that comment.

New code follows...
Private Sub Form_Load()
 
    '////// For recalculating quantities as a percent of Total Shipped.  ///////
    Dim rs As Object, varPctShip As Single, varCurrVal As Single
    
    varPctShip = 0
    varCurrVal = 0
 
    Set rs = CurrentDb.OpenRecordset("tbl_temp_freight_sort_cost")
    
    With rs
        
        Do While (Not (rs.EOF))
        
        rs.MoveFirst
 
            '////// Cycle through columns, regardless of name and number.  //////////
            '       1st Column is referenced as column zero.
            
            For i = 2 To rs.Fields.Count - 1
            
                If IsNull(rs.Fields(i).Value) = False Or rs.Fields(i).Value <> 0 Then
            
                varCurrVal = rs.Fields(i).Value
                
                varPctShip = varCurrVal / (rs.Fields(1).Value)
                
                .Edit
                rs.Fields(i) = varPctShip
                .Update
                
                End If
            
            Next
 
        rs.MoveNext
        
        Loop
        
     End With
 
End Sub

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

jdfullerAuthor Commented:
HOLD THE BUS!  I have it running backa nd forth to the first record!  Doh!!  Let me try moving the MoveFirst outside the loop!

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I just noticed that also ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jdfullerAuthor Commented:
Swap line 13 and 15 in the second code submission and it works fabulously!!  Thanks LSM for the push over the edge!

JDF
0
jdfullerAuthor Commented:
Sometimes it takes another pair of eyes.  Thanks LSM.  Your answer to check the null prior to setting the value worked.  I had to get the Bang (!) out of the item reference in line 27 for the field to get the edit to work properly but you get the full credit for the NULL thing.  The BORG is alive!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.