Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code to pull existing info into new record (Access 2003)

    I have created a database and would like user's to be able to create a new record that pulls information from several fields in the currently displayed record (in a form) so that the user's do not have to retype information over and over when adding new info for different contacts in the same company. Does anyone have a sample of a macro or code that will do this?

    Thanks - Satiria.

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

    Re: Code to pull existing info into new record (Access 2003)

    If you want to copy only specific fields, you can use the method described in How to fill record with data from previous record automatically in Access 2000 (it applies to later versions too). The MSKB article has a link to download a sample database with a working example.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code to pull existing info into new record (Access 2003)

    The only problem is that it would not necessarily be the "previous" record. More like "current" record as they will be doing searches (simple Finds right now, but could create a keyword query for them if need be) of the DB to see which contact information we already have in there for a specific client, and then possibly adding multiple new contact information for the same client.

    I'll read the link, though, and see if I can manipulate the example they've given. Thanks, Hans.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code to pull existing info into new record (Access 2003)

    If you want to copy most of the fields, another possiblilty is to duplicate the current record:

    RunCommand acCmdSelectRecord
    RunCommand acCmdCopy
    RunCommand acCmdPasteAppend

    and then clear or modify some fields as needed. If you want to copy only a few fields, you could store their values in variables and use code like this:

    Dim varField1, varField2
    ' Store some values
    varField1 = Me.Field1
    varField2 = Me.Field2
    ' Go to new record
    RunCommand acCmdRecordsGoToNew
    ' Set values
    Me.Field1 = varField1
    Me.Field2 = varField2

  5. #5
    Lounger
    Join Date
    Nov 2002
    Location
    Farnborough, Hampshire, England
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code to pull existing info into new record (Access 2003)

    In case it's useful to anyone, I've got the following code for copying all fields (except for AutoNumber):

    <pre> Dim rst As Recordset
    Dim fld As Field

    Set rst = Me.RecordsetClone
    rst.AddNew
    For Each fld In rst.Fields
    If (fld.Attributes And dbAutoIncrField) = False Then
    Debug.Print fld.Name & "(" & fld.Type & ") = " & Me.Controls(fld.Name)
    rst.Fields(fld.Name) = Me.Controls(fld.Name)
    End If
    Next fld
    rst.Update
    </pre>


    It seems to work okay, though I haven't tested it fully yet.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code to pull existing info into new record (Access 2003)

    For others reading MarkU's reply: the code requires a reference (in Tools | References) to the Microsoft DAO 3.6 Object Library.
    To avoid confusion with the ADODB objects of the same name, I would declare rst and fld explicitly as DAO objects:

    Dim rst As DAO.Recordset
    Dim fld As DAO.Field

Posting Permissions

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