The difficult I do immediately, the impossible takes a little bit longer.
In this example, the first field has value ABC, next is blank, next is blank and next has value DEF etc...
We want the blanks to be filled so it looks like this...
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)
'Only if we have something to copy down
If Nz(vCopyDown, "") <> "" Then
rec(pstrField) = vCopyDown
MsgBox Error, vbCritical, "Copy Fields Down Records"
CopyFieldRecords = False
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...
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...
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...
?CopyFieldRecords(pstrRST, pstrFieldToCopy, pstrID)
pstrRST = " YourTableName "
pstrFieldToCopy = " YourFieldName " 'The name of the field that you want to copy down
pstrID = " IDField "
In Access 2007 and Access 2010 Ctrl + G brings up the *Choose Builder* window (pictured below). Simply select Code Builder to get to the VB Editor.
P.S. Don't forget the quotation marks!