Fill in the blanks

[thenoisydrum]

Hi all,
I have found a handful of instances on the web where people have asked about this and I even found what appears to be a solution at https://sfmagazine.com/post-entry/october-2015-access-fill-down/
However I cannot get it to work........ This is an example of my data;

Does anybody know how I can fill in the blank cells in MS Access?
I have a Sage .csv output report that has this type of structure to it. I'm currently using Excel to convert it and along with other things, fill in the blanks by doing the Go To/Special/Blanks etc (I'm sure you are all familiar with this)

What I want to do is import the report into a database and do all of the formatting in there. I thought I may be able to do something along the lines of adding an AutoNumber ID to give each row a reference. Maybe then and AutoNumber ID+1 to a copy of the table. That would enable me to update to the cell above but wouldn't quite do the trick

Does anybody have a solution please?

Thanks

Drum

 

[MajP]

That would be easy to read the recordset and write in the missing numbers assuming it imports correctly. Do you have a test csv we can see.

 

[MajP]

Public Sub FillBlanks()
  Const TblName = "[tblAccounts]"
  Const AcctField = "[Account Number]"
  Dim rs As DAO.Recordset
  Dim oldAccountNumber As String
  Set rs = CurrentDb.OpenRecordset("select " & AcctField & " FROM " & TblName)
  'If there is a field like a date stamp to keep sorted properly add that to the select statement
  oldAccountNumber = rs.Fields(AcctField)
  Do While Not rs.EOF
    If Trim(rs.Fields(AcctField) & " ") = "" Then
      rs.Edit
        rs.Fields(AcctField) = oldAccountNumber
      rs.Update
    Else
      oldAccountNumber = rs.Fields(AcctField)
    End If
    rs.MoveNext
  Loop
End Sub

Works for me. Change tblName and AcctField constants as needed. If there is a field that naturally sorts this table then sort by that field. If not add an autonumber field. It may work without a sort field, but it is not guarnateed
Change the code to
Set rs = CurrentDb.OpenRecordset("select " & AcctField & " FROM " & TblName & " ORDER BY YourFieldName")

 

[thenoisydrum]

Thanks for your response. Do I literally copy and paste your code into a macro, changing the table and field names?

 

[MajP]

Yes. Here is a more generic version. It solves the problem shown in the link where you have multiple fields.
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​

Here is the code to use

' 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

To use. Go into the vba window and insert a new "Standard Module" not a class module. In the module drop the code. Then drop this code

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

The first argument should be the name of your table. [] are needed when names have spaces. The second is your sort field name. The rest are the names of any fields to fill in.

In VBA hit the run button or pick "Run". Here is my output. A lot easier in my opinion and should work on any table with any amount of fields. As long as there are values in the first row.
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​

 

[onur_can]

Hello
Why didn't you define the Account Number field as a mandatory field from the beginning?

 

[MajP]

@onur_can

I have a Sage .csv output report that has this type of structure to it.

 

[Gasman]

I would have thought the easiest option to start with would be to get the report output without the 'blank when duplicate' option.?
So go back to your source and ask for some decent output.
If then that is not possible, well MajP has solved you problem. Best not to have that problem in the first place.?

 

[thenoisydrum]

Yes. Here is a more generic version. It solves the problem shown in the link where you have multiple fields.
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​

Here is the code to use

' 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

To use. Go into the vba window and insert a new "Standard Module" not a class module. In the module drop the code. Then drop this code

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

The first argument should be the name of your table. [] are needed when names have spaces. The second is your sort field name. The rest are the names of any fields to fill in.

In VBA hit the run button or pick "Run". Here is my output. A lot easier in my opinion and should work on any table with any amount of fields. As long as there are values in the first row.
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​

Not sure what I'm doing wrong here.
This is my table;

.... here is the error that I get;

What am I doing wrong here?

Thanks in advance

Drum

 

[MajP]

The intent was not to change the code, but change the value of the constants only. That is the value of using constants as placeholders.
So just change the value of the constants like you did, but leave the rest of the code as is.

This line is looking for the constants AcctField and TblName, which are like variables but can only be set once. So keep this line as is.
Set rs = CurrentDb.OpenRecordset("select " & AcctField & " FROM " & TblName)

 

[thenoisydrum]

Oh I see, I will try again - thank you

 

[thenoisydrum]

It worked!! I love you!!

 

[Pat Hartman]

One thing to keep in mind is that this method works IF and ONLY IF, you have a unique identifier that will sort the recordset into the order that will cause the blanks to be filled with the correct information.

In relational databases, tables and queries are unordered sets. If you do not specifically sort them, you cannot depend on the sequence in which records will be returned. That would leave you with the only option being to write the macro to fill in the gaps in Excel which is NOT a relational database and which will always retain physical record order UNLESS you sort.