Simulating Fill-Down in Access

elbastador

New Member
Joined
Oct 7, 2005
Messages
36
I'm importing some text files into Access. Some of the rows are missing data and I'm trying to copy the value from the row above into all the blank rows. I know I can do this in Excel but I'm trying to figure out how to do it in Access, as some of the files are too large to be imported into Excel. So essentially if I have a table with 3 columns (1st being an autonumber) like this:

1 1 35010
2
3
4 2 35015
5
6

End result is that I would like this:
1 1 35010
2 1 35010
3 1 35010
4 2 35015
5 2 35015
6 2 35015

Is something like this even possible? I'm thinking that I could do something like this in VB, but I'm not as familiar with VB in Access as I am in Excel so I'm not sure how I would set that up. Any thoughts? Thanks!
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi, this would need to be adapted to indicate your table name and the fields your interrogating. In this example theres a Table2 that has an autonum field called ID and Im wanting to sort in ID number order and if the text field repaired_By is blank, then populate the data from the previous row that had something in the repaired_by field. This code requires you to set a reference so when your in the VBE select Tools, References then select the Microsoft DAO Object library object indicated and this will have a tick against it. The code needs this library to access the recordset object. hth

Code:
Sub PopulateData()
Dim Rs As Recordset, strSQL As String, CurData As Variant
'Note: Set a reference to the Microsoft DAO Object library via Tools | References

'Amend this to indicate the table and sort by the autonumber id
strSQL = "SELECT * FROM TABLE2 ORDER BY ID"

'Opens a recordset (like a query) to the table
Set Rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)


'Loop through the records in the table and if the previous row didnt have data in the
'Repaired_By field then populate the previous Repaired_By data. Assumes the first record is
'populated with info
Rs.MoveLast
Rs.MoveFirst
Do
    If Rs!repaired_By = "" Or IsNull(Rs!repaired_By) Then
        'This field is blank so populate the info from the variable
        Rs.Edit
        Rs!repaired_By.Value = CurData
        Rs.Update
    Else
        'This field isnt blank so store the info in the variable in case the next record is blank
       CurData = Rs!repaired_By.Value
    End If
    Rs.MoveNext
Loop While Not Rs.EOF

'Remove the recordset object from memory
Rs.Close
Set Rs = Nothing
End Sub
 

CT Witter

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
This can also be done if SQL. My Table is "Table2"
Fields
ID1=Autonum
ID1,ID,Value


Code:
SELECT Table2.ID1, (Select Max(ID) from Table2 as X  WHERE X.Id1 <= Table2.ID1 order by  Table2.ID) AS IDFill, (Select Max(Value) from Table2 as X  WHERE X.Id1 <= Table2.ID1 order by  Table2.ID) AS ValueFill
FROM Table2;
Yields:
----------------------------------------------------------------
| ID1 | IDFill | ValueFill |
----------------------------------------------------------------
| 1 | 1 | 35010 |
----------------------------------------------------------------
| 2 | 1 | 35010 |
----------------------------------------------------------------
| 3 | 1 | 35010 |
----------------------------------------------------------------
| 4 | 2 | 35015 |
----------------------------------------------------------------
| 5 | 2 | 35015 |
----------------------------------------------------------------
| 6 | 2 | 35015 |
----------------------------------------------------------------

HTH,
CT
 

elbastador

New Member
Joined
Oct 7, 2005
Messages
36
parry:

Thanks for that code. That's essentially what I was thinking would have to be done, but I didn't know how to do it. Only problem is that on this line

"If Rs!repaired_By = "" Or IsNull(Rs!repaired_By) Then"

I get a Runtime error 3265: Item not found in this collection.

I checked and I have Microsoft DAO 3.6 Object Library enabled. Is there something else I need to run this?

Thanks!
 

CT Witter

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
You need to change "repaired_By" to whatever your field is called in your table.

HTH,
CT
 

navafolk

New Member
Joined
Jul 22, 2015
Messages
13
Hi guys,
Please show me how to do the same thing if there is no ID column. Many thanks
 

Forum statistics

Threads
1,082,276
Messages
5,364,195
Members
400,786
Latest member
ismi88

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top