Access Fill Down to Emulate Excel Fill Down (2 Viewers)

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:24
Joined
May 21, 2018
Messages
8,463
Recently there was a user trying to emulate the Excel fill down functionality based on imported data. I am re-posting this solution here since I could see some utility and this may be a common issue. The OP mentioned doing several web searches and not finding an easy solution. The solution posted seemed overly complex to me.
https://sfmagazine.com/post-entry/october-2015-access-fill-down/.

So the problem is that your import may have missing data in rows based on a spread sheet format. The example was
BalanceSheetBackup

IDGroup 1Group 2Group 3AccountAmount
1​
AssetsAssetsCurrent AssetsCash
$414,527.00​
2​
Accounts Receiveable, net
$482,995.00​
3​
Raw Materials Inventory
$99,663.00​
4​
Finished Goods Inventory
$128,285.00​
5​
Property, Plant and EquipmentBuilding
$1,500,000.00​
6​
Equipment
$1,687,000.00​
7​
Accumulated Depr - Building and Equipment
($937,500.00)​
8​
Liabilities and Stockholder's EquityLiabilitiesCurrent LiabilitiesAccounts Payable
$161,400.00​
9​
Interest Payable
$0.00​
10​
Income Tax Payable
$145,155.00​
11​
Long Term LiabilitiesBonds Payable
$997,600.00​
12​
Loans Payable
$0.00​
13​
Stockholders' EquityStockholders' EquityCommon Stock (100,000 shares outstanding)
$1,000,000.00​
14​
Retained Earnings
$1,070,815.00​

The function allows any amount of columns to fill down as long as you specify. To use the procedure pass in the table name, the sort field name, and the name of the columns to fill in.
ex.
Code:
Public Sub TestFillDown()
  AccessFillDown "[Balance Sheet]", "ID", "[Group 1]", "[Group 2]", "[Group 3]"
End Sub
Results
Balance Sheet

IDGroup 1Group 2Group 3AccountAmount
1​
AssetsAssetsCurrent AssetsCash
$414,527.00​
2​
AssetsAssetsCurrent AssetsAccounts Receiveable, net
$482,995.00​
3​
AssetsAssetsCurrent AssetsRaw Materials Inventory
$99,663.00​
4​
AssetsAssetsCurrent AssetsFinished Goods Inventory
$128,285.00​
5​
AssetsAssetsProperty, Plant and EquipmentBuilding
$1,500,000.00​
6​
AssetsAssetsProperty, Plant and EquipmentEquipment
$1,687,000.00​
7​
AssetsAssetsProperty, Plant and EquipmentAccumulated Depr - Building and Equipment
($937,500.00)​
8​
Liabilities and Stockholder's EquityLiabilitiesCurrent LiabilitiesAccounts Payable
$161,400.00​
9​
Liabilities and Stockholder's EquityLiabilitiesCurrent LiabilitiesInterest Payable
$0.00​
10​
Liabilities and Stockholder's EquityLiabilitiesCurrent LiabilitiesIncome Tax Payable
$145,155.00​
11​
Liabilities and Stockholder's EquityLiabilitiesLong Term LiabilitiesBonds Payable
$997,600.00​
12​
Liabilities and Stockholder's EquityLiabilitiesLong Term LiabilitiesLoans Payable
$0.00​
13​
Liabilities and Stockholder's EquityStockholders' EquityStockholders' EquityCommon Stock (100,000 shares outstanding)
$1,000,000.00​
14​
Liabilities and Stockholder's EquityStockholders' EquityStockholders' EquityRetained Earnings
$1,070,815.00​
Code needed
Code:
' ----------------------------------------------------------------
' Procedure Name: AccessFillDown
' Purpose: Mimics Excel FillDown
' Procedure Kind: Sub
' Procedure Access: Public
' Parameter TableName (String): Name of the table
' Parameter SortFieldName (String): Field on which to sort to ensure data is in the correct order
' Parameter FieldNames (Variant()): Array of just the fields to fill-in
' Author: MajP
' Date: 5/24/2020
' ----------------------------------------------------------------
Public Sub AccessFillDown(TableName As String, SortFieldName As String, ParamArray FieldNames() As Variant)
  On Error GoTo AccessFillDown_Error
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim fieldName As String
  Dim OldValue As Variant
  Dim i As Integer
 
  Set rs = CurrentDb.OpenRecordset("Select * from " & TableName & " ORDER BY " & SortFieldName)
 
  For i = 0 To UBound(FieldNames)
    fieldName = FieldNames(i)
    rs.MoveFirst
    Set fld = rs.Fields(fieldName)
    'Assumes that every field to fill down has a value in first row
    OldValue = fld.Value
    Do While Not rs.EOF
        If Trim(fld.Value & " ") = "" Then
          rs.Edit
            fld.Value = OldValue
          rs.Update
        Else
          OldValue = fld.Value
        End If
        rs.MoveNext
    Loop
  Next i
   Exit Sub
AccessFillDown_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure AccessFillDown, line " & Erl & "."
End Sub
 

Users who are viewing this thread

Top Bottom