Access Diva Copy fields down

ABC
blank
blank
DEF
blank
GHI
blank
blank
blank

We want the blanks to be filled so it looks like this...

ABC
ABC
ABC
DEF
DEF
GHI
GHI
GHI

Copy and paste the code below into a new Module, changing the name of YourTable to match your table. Remember, do not name your Module the same as the Function!

To call the Function, bring up the Immediate window (Ctrl + G) OR open any Module and on the Menu Bar select

View... Immediate Window. Then type...

?CopyFieldRecords(pstrRST, pstrFieldToCopy, pstrID)

pstrRST = "YourTableName"

pstrFieldToCopy = "YourFieldName" 'The name of the field that you want to copy down

pstrID = "IDField"

Copy fields down from above record...

Say we want to automatically fill fields with the value from the field above and we don't want to sit there all day (and night) to get it done.

In this example, the first field has value ABC, next is blank, next is blank and next has value DEF etc...

Function CopyFieldRecords(pstrRST As String, pstrField As String, pstrID As String) As Boolean
'Originally posted by David in the Newsgroups in 1999
Dim db As Database
Dim rec As Recordset
Dim vCopyDown As Variant
CopyFieldRecords = True
On Error GoTo err_copyrecords
 
vCopyDown = Null
Set db = CurrentDb()
Set rec = db.OpenRecordset("Select * FROM [" & YourTable & "]")
While Not rec.EOF
'If the field isn't blank then use this to copy down
If Nz(rec(pstrField), "") <> "" Then
     vCopyDown = rec(pstrField)
Else
       'Only if we have something to copy down
        If Nz(vCopyDown, "") <> "" Then
             rec.Edit
             rec(pstrField) = vCopyDown
             rec.Update
        End If
End If
     rec.MoveNext
Wend
 
exit_copyrecords:
Exit Function
 
err_copyrecords:
MsgBox Error, vbCritical, "Copy Fields Down Records"
CopyFieldRecords = False
GoTo exit_copyrecords
 
End Function