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")
'////// 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)
!rs.Fields(i) = varPctShip