Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Copy an existing Record via Form

Posted on 2014-01-04
11
Medium Priority
?
489 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 39

Accepted Solution

by:
PatHartman earned 2000 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 51

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 39

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 51

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 51

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 51

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 51

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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 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