Say we want to automatically fill fields with the value from the field above -
First field has value ABC , next is blank, next is blank, next has value DEF etc
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
***START OF CODE
Function CopyFieldRecords(pstrRST As String, pstrField As String, pstrID As String) As Boolean
‘Originally posted by David 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
***END OF CODE
'Call the function like this
pstrRST = "YourTableName"
pstrFieldToCopy = "YourFieldName" 'name of the field that you want to copy down
pstrID = "IDField"
Call CopyFieldRecords(pstrRST, pstrFieldToCopy, pstrID)
