Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    19

    Unanswered: Copy value to new record using VBA

    I have a form for the user to complete but each time they click on a cmd button to taek them to a new record I want to speed up the process of filling in the blank form by automatically placing certain values from the previous record in to the new. An example would be the users name and their location.

    I have tried various solutions posted on these forums but no luck so far.

    I have something similar that works from one form to a different form but not simply a new record. This is how far I have got so far:

    Code:
    Private Sub Command13_Click()
    'Go to a new record
    DoCmd.GoToRecord , , acNewRec
    Forms!frminter!User = Me.User
    End Sub
    I realise this doesn't work but I am sure there is a simple answer.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    One solution consists in:
    1. Store the values you want to propagate into member variables.
    2. Create a new record.
    3. Copy the values stored in 1. into their related controls.
    Example:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_strUser As String
    
    Private Sub Command_NewRecord_Click()
    
        m_strUser = Nz(Me.User.Value, "")
        DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
        Me.User = m_strUser
        
    End Sub
    If you don't want to use member variables, you can also:
    1. Instanciate a new Recordset using the RecordsetClone method and position its current record to the form current record.
    2. Create a new record.
    3. Copy the values you want to propagate, from the clone to the new record.
    Example:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Command_NewRecord_Click()
    
        Dim rst As DAO.Recordset
        
        Set rst = Me.RecordsetClone
        rst.Bookmark = Me.Bookmark
        DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
        Me.User.Value = rst!User
        Set rst = Nothing
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Posts
    19

    Thumbs up Copy value to new record

    Cheers very much. This works an absolute treat.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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