Solved

Copy an existing Record via Form

Posted on 2014-01-04
11
433 Views
Last Modified: 2014-01-17
I have a form, that contains multiple fields for a record.  I want to enable the user via a button to copy all the data from the existing record and create a new one however I want one field to be automatically created as part of the insert.

The field that uniquely identifies each record is  FileNo

The field is created as part of the before insert of the form is :

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
    Me.FileNo = Year(Date) & "1" & Format(Me.InvoiceID, "00000")
End If
0
Comment
Question by:seamus9909
  • 5
  • 4
  • 2
11 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39756550
Whenever someone asks how to copy data, my antennae go up since there is almost always an underlying design flaw in the schema.  I would put the error rate at over 90%. Think about how your table is structured.  Should there be a table "above" it that has the common information.  Think about an order entry application.  You could have one table for the order and create a separate record for each item.  You would need to copy the customer shipping and billing information as well as any promo code, etc in each record.  The unique part would be the item number and quantity.  A better design would split the order into two tables with all the common information going in the Order table and the individual items being listed in the Order Details table.

That said, the most efficient way to copy a record is to use an append query.  Start with the QBE and create a query that selects a row from the table and appends it to the same table.  Switch to SQL View and copy the string into your procedure.  You will need to modify the part that assigns a value to FileNo and you'll have to add selection criteria to select the ID of the current record.
Dim strSQL as String
    strSQL = "INSERT INTO tblsomename ( InvoiceID, FileNo, fld2, fld3 ) SELECT "
    strSQL = strSQL & " InvoiceID, Year(Date()) & "1" & Format(InvoiceID, "00000"), fld2, fld3"
    strSQL & strSQL & " FROM tblsomename WHERE FileNo = Forms!frmyourform!FileNo;"
    
    If Me.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    DoCmd.RunSQL strSQL

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39756637
It is way faster to use DAO that SQL here.
Here is how:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27863827.html

That code excludes the field "FK" and uses a custom value.
Use the same method to insert your value for field "FileNo":

    rstInsert.Fields(.Name).Value = Year(Date) & "1" & Format(Me.InvoiceID, "00000")

/gustav
0
 

Author Comment

by:seamus9909
ID: 39756682
gustav   when using that DAO it creates a new record but the FIleNo is repeated, in other words the same fileno is used for the duplicate records. SO I assume the RstInsert is used somewhere.  

where do I use thia

 rstInsert.Fields(.Name).Value = Year(Date) & "1" & Format(Me.InvoiceID, "00000")?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39756992
It is way faster to use DAO that SQL here
A code loop is not faster than an action query.  Running the query with a DAO .execute may be faster but I'm not sure you could measure the difference.  The code loop is measurably slower.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39757044
> ..  it creates a new record but the FIleNo is repeated ..

Of course. You didn't state it should be different.
I guess you wish to add 1 to the invoide number? If so:

    rstInsert.Fields(.Name).Value = Year(Date) & "1" & Format(Me.InvoiceID + 1, "00000")


> A code loop is not faster than an action Query

That I didn't state. But using DAO and the RecordsetClone here is way faster for the complete task as the form is updated instantly.

/gustav
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:seamus9909
ID: 39757278
Where do I put this line?  in the macro?  or in the form?

rstInsert.Fields(.Name).Value = Year(Date) & "1" & Format(Me.InvoiceID + 1, "00000")
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39757321
In the function:

              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              ElseIf .Name = "FileNo" Then
                ' Insert new number.
                rstInsert.Fields(.Name).Value = Year(Date) & "1" & Format(Me.InvoiceID + 1, "00000")
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If

/gustav
0
 

Author Comment

by:seamus9909
ID: 39757411
ok but as I followed your instructions I created the duplicate records button, which is an underlying macro. so what is the next step to invoke the function?

Sorry Im a novice at this
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39757464
Remove the macro. Go to Events for the button, select the OnClick event, select EventProcedure and click the small button.
The code window opens with an empty sub function. Remove that and insert this:
Private Sub btnCopy_Click()

  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
  
  If Me.NewRecord = True Then Exit Sub
    
  Set rstInsert = Me.RecordsetClone
  Set rstSource = rstInsert.Clone
  With rstSource
    If .RecordCount > 0 Then
      ' Go to the current record.
      .Bookmark = Me.Bookmark
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              ElseIf .Name = "FileNo" Then
                ' Insert new number.
                rstInsert.Fields(.Name).Value = Year(Date) & "1" & Format(Me.InvoiceID + 1, "00000") 
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
        ' Go to the new record and sync form.
        .MoveLast
        Me.Bookmark = .Bookmark
        .Close
      End With
    End If
    .Close
  End With
  
  Set rstInsert = Nothing
  Set rstSource = Nothing
  
End Sub

Open in new window

Adjust "btnCopy_Click" if the name of your button not is btnCopy.

/gustav
0
 

Author Comment

by:seamus9909
ID: 39757487
ok I did all that and it failed at this line.
Debug says field can not be updated.


                rstInsert.Fields(.Name).Value = .Value
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39757552
And you can add new records?

If so, then exclude that field:

            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
             ElseIf .Name = "FieldNameThatCannotBeUpdated" Then
                ' Exclude read-only field.
             ElseIf .Name = "FileNo" Then
                ' Insert new number.
                rstInsert.Fields(.Name).Value = Year(Date) & "1" & Format(Me.InvoiceID + 1, "00000")
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With

Replace FieldNameThatCannotBeUpdated with the actual field name.

/gustav
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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

19 Experts available now in Live!

Get 1:1 Help Now