Edit a Table by VBA (1 Viewer)

[Beerbrewer]

I have made a linked table to a text file (which is continuously updated). Unfortunately the file is formatted as report. This means that the first values are omitted if they are double. For a proper processing I must put those values "back". So far I know there is no solution by SQL since if the first value of a record Is Null, its value depends on the first (not empty) value of a record above (in the same table).
I would like to let VBA loop trough the table. When it encounters a Null-value it should add the value of the above record.
It looks like this:
1 <Record1>
<Record2>
2 <Record3>
3 <Record4>
<Record5>
<Record6>
4 <Record7>
etc.
It should look like this:
1 <Record1>
1 <Record2>
2 <Record3>
3 <Record4>
3 <Record5>
3 <Record6>
4 <Record7>
etc.
Is there a SOLUTION?

Many thanks!

 

[Tim K.]

Try this code out. Make sure you have back up your table before running it.

Private Sub FillBlankFields()
Dim dbs As Database, rst2 As Recordset
Dim intValue1 As Integer

Set dbs = CurrentDb
Set rst2 = dbs.OpenRecordset("YourTargetTableNameHere")

rst2.MoveFirst
' I assume the Null/blank field is called field1
intValue1 = rst2!field1
Debug.Print intValue1

With rst2
.MoveNext
Do Until .EOF
' Add the previous value to it if Null or blank.
' Assume the first record is not Null or blank.
If IsNull(!field1) Or !field1 = "" Then
.Edit
!field1 = intValue1
Debug.Print intValue1
.Update
Else
' Remember the value
intValue1 = !field1
Debug.Print intValue1
End If
.MoveNext
Loop
End With

rst2.Close
dbs.Close
Set rst2 = Nothing
Set dbs = Nothing

End Sub

 

[Beerbrewer]

Sounds genious! Thanks!
Unfortunately I got an error: on Dim dbs as Database ("Compile error: User-defined Type not Defined"). It seems "Database is not a valid type?
I also have antother question. Should the code be put in a module or can it also be put behind a form?
Thanks again.

 

[Tim K.]

Make sure you have set DAO 3.6 in References. In Module design view, go to Tools menu>References>look for Microsoft DAO Object Library 3.6, then click to select it.

Then modify the code in this part.

...
Dim dbs As DAO.Database, rst2 As DAO.Recordset
...

You can put the code behide form or in Module, depending on how often you have to run this code.

 

[Beerbrewer]

Man this is brilliant! Please accept my humblest gratitude.