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...
These are String values so don't forget the quotation marks!
The difficult I do immediately, the impossible takes a little bit longer.
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 
In Access 2007 Ctrl + G brings up the *Choose Builder* window (pictured below).  Simply select Code Builder to get to the VB Editor.
VBA
Tips (Main)
Home
Creating a Multi-Value field using Alphabet
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Create a Table with Dynamic Field Names
Snippets for Exporting to Excel
Log Field Changes
Log Record Deletions
Check for Duplicate Values
ValidateData()
ClearClipboard()
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
Automatically Send eMail Notifications
fFindBookmark()
Looping Records to Send eMail
fxlFindReplace()
Click to send feedback...
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.