Solved

Access VBA Fill Record with Previous Value

Posted on 2016-09-02
5
28 Views
Last Modified: 2016-09-02
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
0
Comment
Question by:shieldsco
  • 3
5 Comments
 
LVL 19
ID: 41781936
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
 

Author Comment

by:shieldsco
ID: 41781955
It's an csv import file
0
 
LVL 19
ID: 41782089
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 300 total points
ID: 41782123
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
 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 200 total points
ID: 41782134
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now