Access VBA Fill Record with Previous Value

I have a table with five fields. I would like to fill the next blank records automatically with values from the previous record. Can you help with the code. Thanks
shieldscoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President Online Computer Svcs, WNY IncCommented:
Not sure if this will help or not (is this copying at import or through a form?), but it might save some effort if your doing this in a form.

Code below copies a record from the last, previous, or record with criteria matching what you specify.

About to leave, so I won't be commenting past this.

Jim.

Function AutoFillNewRecord(frm As Form, strCriteria As String)

   ' Picked up from MSKB Q210236
   ' Needs control on form called AutoFillNewRecordFields, which
   ' is a semi-colon delimited list of fields to fill.
   
   Dim rs As DAO.Recordset
   Dim ctl As Control
   Dim strFillFields As String
   Dim intFillAllFields As Integer
   
   On Error Resume Next
   
   ' Exit if not on the new record.
   If Not frm.NewRecord Then Exit Function
   
   ' Jump to the correct record (to autofill form).
   Set rs = frm.RecordsetClone
   
   If strCriteria = "Last" Then
     rs.MoveLast
   Else
     If strCriteria = "Prev" Then
       rs.MovePrevious
     Else
       rs.FindFirst strCriteria
       If rs.NoMatch Then Exit Function
     End If
   End If
   
   ' Exit if you cannot move to the last record (no records).
   If Err <> 0 Then Exit Function
   
   ' Get the list of fields to autofill.
   strFillFields = ";" & frm![txtAutoFillNewRecordFields] & ";"
   
   ' If there is no criteria field, then set flag indicating ALL
   ' fields should be autofilled.
   intFillAllFields = Err <> 0
   
   frm.Painting = False
   
   ' Visit each field on the form.
   For Each ctl In frm
      ' Fill the field if ALL fields are to be filled OR if the
      ' ...ControlSource field can be found in the strFillFields list.
      If intFillAllFields Or InStr(strFillFields, ";" & (ctl.Name) & ";") > 0 Then
         ctl = rs(ctl.ControlSource)
      End If
   Next
   
   frm.Painting = True
   
End Function
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
I know you asked for code, which I don't have enough information to write. Meanwhile, you can press Ctrl-' (single quote) to copy the value from the field of the previous record. btw, this works in Excel too :)

out of curiosity, why are you copying values?
0
 
shieldscoAuthor Commented:
It's an csv import file
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
OOPS! I accidentally gave you Excel code ... need more information to write for Access:
1. What are the fieldnames?
2. And are you using a form to look at data (tables can't have code)

I decided to leave this in here anyway...
assuming you are in a blank row , you can run this code, which will need to be stored in another workbook, like your Personal Macro Workbook since CSV files can't have code:
Sub CopyPreviousValues_Col_AtoE()
'160902
   Dim nRow As Long
   nRow = ActiveCell.Row
   With ActiveSheet
      .Cells(nRow, 1) = .Cells(nRow - 1, 1)
      .Cells(nRow, 2) = .Cells(nRow - 1, 2)
      .Cells(nRow, 3) = .Cells(nRow - 1, 3)
      .Cells(nRow, 4) = .Cells(nRow - 1, 4)
      .Cells(nRow, 5) = .Cells(nRow - 1, 5)
   End With

End Sub

Open in new window

0
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
assuming what you want to fill is the 1st 5 fields in the underlying table, here is code for Access:
'160902 strive4peace
   'copy the first 5 fields from the selected record to a new record
   'save current record
   If Me.Dirty Then Me.Dirty = False
   With Me.RecordsetClone
      .AddNew
      .Fields(0) = Me.Recordset.Fields(0)
      .Fields(1) = Me.Recordset.Fields(1)
      .Fields(2) = Me.Recordset.Fields(2)
      .Fields(3) = Me.Recordset.Fields(3)
      .Fields(4) = Me.Recordset.Fields(4)
      .Update
   End With

Open in new window


it must be triggered from a form and assumes you are ON the record you want to copy. It would be better to have the field names.

I see Jim just posted also
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.