Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    leaving data in a form when adding multiple record (2007)

    I was asked this question, and I don't have an answer:

    I want to enter multiple records through a form. Many of the records contain similar data in the fields. Is there a way to not clear the fields from record to record, so that I can simply change the fields that are different from record to record.

    (I know I can do this with VBA code, but there are bonus points involved for anyone that has a solution that doesn't involve too much VBA code.)

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: leaving data in a form when adding multiple record (2007)

    If you want to avoid VBA, you can do the following:
    - Enter data in a new record, or go to an existing record.
    - Select Edit | Select Record. (If the record selector is displayed in the form, you can click that instead).
    - Select Edit | Copy or click the Copy button on the toolbar or press Ctrl+C.
    - Select Edit | Paste Append. This will paste a copy of the record as a new record. You can modify it as needed.

    If you don't mind a bit of VBA: put a command button on the form named cmdDuplicate, with caption "Duplicate Record". The On Click code for the button looks like this:

    Private Sub cmdDuplicate_Click()
    RunCommand acCmdSelectRecord
    RunCommand acCmdCopy
    RunCommand acCmdPasteAppend
    End Sub

    This performs the three steps described above in one go. The code could be improved by adding an error handler:

    Private Sub cmdDuplicate_Click()
    On Error GoTo ErrHandler
    RunCommand acCmdSelectRecord
    RunCommand acCmdCopy
    RunCommand acCmdPasteAppend
    Exit Sub

    ErrHandler:
    If Not Err = 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

  4. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: leaving data in a form when adding multiple record (2007)

    I think it depends on how many fields you have and how many fields stay the same. If only a few fields stay the same, I would hit <ctrl> <'> to copy the field from the previous record.

Posting Permissions

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