Access Fill Down to Emulate Excel Fill Down

[MajP]

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

ID	Group 1	Group 2	Group 3	Account	Amount
1​	Assets	Assets	Current Assets	Cash	$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 Equipment	Building	$1,500,000.00​
6​				Equipment	$1,687,000.00​
7​				Accumulated Depr - Building and Equipment	($937,500.00)​
8​	Liabilities and Stockholder's Equity	Liabilities	Current Liabilities	Accounts Payable	$161,400.00​
9​				Interest Payable	$0.00​
10​				Income Tax Payable	$145,155.00​
11​			Long Term Liabilities	Bonds Payable	$997,600.00​
12​				Loans Payable	$0.00​
13​		Stockholders' Equity	Stockholders' Equity	Common 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.

Public Sub TestFillDown()
  AccessFillDown "[Balance Sheet]", "ID", "[Group 1]", "[Group 2]", "[Group 3]"
End Sub

Results
Balance Sheet

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

Code needed

' ----------------------------------------------------------------
' 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