Solved

Copy an existing Record via Form

Posted on 2014-01-04
11
474 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 36

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 50

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 36

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 50

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 50

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 50

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 50

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

749 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