Solved

Use VBA to change cell values in recordset

Posted on 2009-06-29
7
1,466 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:jdfuller
  • 5
  • 2
7 Comments
 

Author Comment

by:jdfuller
ID: 24740455
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
 
LVL 84
ID: 24744064
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
 

Author Comment

by:jdfuller
ID: 24748099
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
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.

 

Author Comment

by:jdfuller
ID: 24748203
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 24748280
I just noticed that also ...
0
 

Author Comment

by:jdfuller
ID: 24748347
Swap line 13 and 15 in the second code submission and it works fabulously!!  Thanks LSM for the push over the edge!

JDF
0
 

Author Closing Comment

by:jdfuller
ID: 31598133
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

914 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now