Populate duplicate blank fields (1 Viewer)

I hope you can point me in the right direction,
I need code to populate the blank values in field1 with the value above.
i.e. 008HN is populated for each value in field 4 until 0121P
See attached
I've got code to do it in excel but not access.
Thanks Ciaran.

Hi -

Looks like this is connected to importing data? If so, you might want to try to catch it at the import stage, rather than deal with it internally to Access.

The one potential issue that lurks here is how to determine which records lie "above" other records. At the time of import, this is pretty well defined. However, my understanding is that you can not necessarily rely on the autonumber fields to preserve order as time goes on.

okay, enough of the cautions

1. You can declare a recordset based on the table and step through each record. Set a variable equal to "last value". If the current value is not blank, then update "last value". Otherwise, write last value into the field.

The code looks something like this:

Dim rs as DAO.Recordset
Dim strLastValue as String
Set rs = dbs.OpenRecordset("tblData", dbOpendynaset)

strLastValue = ""
While not rs.EOF
if rs!field1 <> "" then
strLastValue = rs!Field1
Else
rs.Edit
rs!Field1 = strLastValue
rs.Update
Endif
rs.MoveNext
Wend

rs.Close
Set rs = Nothing

2. Export the table to Excel, work your magic there and then re-import into Access.

hth,

- g

Thanks G... too busy at the mo ...later