Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

Copy an existing Record via Form

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
seamus9909
Asked:
seamus9909
  • 5
  • 4
  • 2
1 Solution
 
PatHartmanCommented:
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
 
Gustav BrockCIOCommented:
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
 
seamus9909Author Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
PatHartmanCommented:
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
 
Gustav BrockCIOCommented:
> ..  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
 
seamus9909Author Commented:
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
 
Gustav BrockCIOCommented:
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
 
seamus9909Author Commented:
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
 
Gustav BrockCIOCommented:
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
 
seamus9909Author Commented:
ok I did all that and it failed at this line.
Debug says field can not be updated.


                rstInsert.Fields(.Name).Value = .Value
0
 
Gustav BrockCIOCommented:
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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now