Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2010

    Question Unanswered: Populate field from previous record

    First, I am running Windows 7 and working in Access 2007.

    Can someone tell me how I can populate (auto-fill) a field in a form with a number from a previous record?

    i.e., When I go to a New Record, I want one of the fields to be populated by a number from another field that was in the previous record - as illustrated below

    (Ending: in previous record - to Starting: in New Record)

    With appreciation,

  2. #2
    Join Date
    Jun 2007
    Maitland NSW,Australia
    This is usually done by assigning the current value of a control to the control's Default value. The next new record will automatically have this value until the value is manually changed or until the form is closed.

    Private Sub YourNumericControlName_AfterUpdate()
    If Not IsNull(Me.YourNumericControlName.Value) Then
    YourNumericControlName.DefaultValue = Me.YourNumericControlName.Value
    End If
    End Sub

  3. #3
    Join Date
    Oct 2012
    Hi there,
    I have the same question concerning auto-fill of date, selection from a combo-box and text (all in separate fields of course).
    The fields are just date and text fields, in the same table except combo-box.

    Have not been working with databases since dB IV, and that was maybe 30 years ago, maybe even more.

  4. #4
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    As Poppa Smurf suggested, you can use the AfterUpdate event of the Control holding your data to set the DefaultValue for the Field. From that time forward, until you either manually change the data or close your form, the data will be entered automatically in each New Record.

    Private Sub YourControlName_AfterUpdate()
       Me.YourControlName.DefaultValue = """" & Me.YourControlName.Value & """"
    End Sub

    which is valid for Text, Number and Date Datatypes. Never tried it on a Yes/No Field!

    You’ll need to do this for each Control that you want to ‘carry forward

    If you have a slew of Fields, you can use the Tag Property to mark the ones to be defaulted and then loop through them, setting the DefaultValue.

    • Go into Form Design View
    • Select all Controls you want to 'carry forward,' by pressing and holding down the <Shift> Key and clicking on each one.
    • Go to Properties - Other
    • In the Tag Property enter CarryForward (without quotes)

    Now use this code:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ctl As Control
    For Each ctl In Me.Controls
      If ctl.Tag = "CarryForward" Then
        ctl.DefaultValue = """" & ctl.Value & """"
      End If
    Next ctl
    End Sub

    Each of the "tagged" Controls' values will be carried forward into a New Record until you either Edit the data in a given Control or Close the Form or Access itself.

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Oct 2012
    Thank you.
    Everything seems to work just fine, except that it did NOT work in my case. The reaon seems to be the following:
    I imported two large excel sheets of data into my project. So far so good. I created a new table, into which data was brought by using combo boxes from the two imported tables. Everything else worked ok, except the After update events, even if the fields where I tried the code were from the table originally created in Access. Maybe the reason could be that some of the key fields contain keywords with "umlaut" letters?
    I found this, after having recreated all the tables bit by bit (sigh) in Access, then the After update worked just fine. Did not however test this all the way yet, just eith a small fraction of the data.
    Excusevthe spelling, this written on a Pad. Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts