Solved

Copy an existing Record via Form

Posted on 2014-01-04
11
457 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 35

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 35

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
 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

810 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