Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more


Error in Duplicating record on MS Access Form (VBA) due to Unbound Control

Posted on 2008-06-25
Medium Priority
Last Modified: 2013-11-28
I have an MS Access form frmA tied to a table tblA. All of the controls except 2 new ones that I recently added, are tied to some field from tblA (i.e. their Data Source) is some field in tblA. The 2 new controls I added are not bound to the table, they are combo boxes to simply apply a Filter or Sort By, on the records in frmA. Before adding the 2 new controls, I was able to Duplicate an existing record via a command button I added using the MS Access Wizard (code below):

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Now since adding the control, I get the error message that it was unable to paste & the errors have been added to a table Paste Errors. I think that when I use the Duplicate command, Access tries to paste selections for the combo boxes (2 new controls) into tblA, which however, does not have a field assocaited with the control.

My question is, using the same Duplicate command button, how can I specify or restrict these 2 controls from being copied, while copying everything else using the same code above.
Question by:nemesis7
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
  • 3
LVL 27

Expert Comment

ID: 21863586
The code generated by the Wizard is not the best starting point (It hasn't changed since Access 95).  
Since it's unlikely that you want two rows in your table to be exactly alike, the question is what are you trying to achieve? It's probable that there are other ways of going about it.

Author Comment

ID: 21871878
MikeToole: Many of the records in the database are duplicate, hence, to make data entry faster, client wants to be able to duplicate an existing record & then modify the new record where necessary/different. One way would be to do a SQL insert statement, but I would rather avoid that.
LVL 27

Expert Comment

ID: 21873075
The approach I would suggest is to set default values for controls in the form's Current event. This means that whenever a row is made current, it's values are reflected in the New Row line. I've appended a snippet that shows the technique for three controls on a form.

Private Sub Form_AfterUpdate()
End Sub
Private Sub Form_Current()
End Sub
Private Sub SetCurrentAsDefault()
    SetDefault Me.NumCol
    SetDefault Me.TextCol
    SetDefault Me.DateCol, True
End Sub
Private Sub SetDefault(ctl As Control, Optional IsDate As Boolean = False)
    With ctl
        If Trim(Nz(.Value, "")) = "" Then
            .DefaultValue = ""
        ElseIf IsDate Then
            ' Convert the date to the internal representation - no problems with international settings then.
            .DefaultValue = CDbl(.Value)
            .DefaultValue = """" & .Value & """"
        End If
    End With
End Sub

Open in new window

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21880712

I have done exactly what you have described a few times and I could not duplicate your issue.

If you just run you code it will duplicate the current Record.
Being that the combobox is unbound, it will just sit there at the same value, no matter what you do. (until you change it)
Can you explain exactly how you "added this combobox to the form. (any associated code?)
The duplicate record wizard code assumes that you have a primary key field that is an autonumber, so that it will increment automatically, without fear of creating duplicates, when you copy a record.
Do you have an autonumber field as a primary key?

1. If you do not have a system to advance the key then the duplicate code you are using will give you an error about Duplicate indexes.
Is this what is happening in your case?

2. The "Paste Errors" error sounds like what happens when you import the wrong datatype into a column.
So the question is, are you validating the fields to make sure the correct data is entered?



Author Comment

ID: 21881412

I am attaching the zipped Access DB, please carefully reference the description of the problem above. When you open the DB, click on CASES which will open the form I am talking about. Notice the Duplicate command button in the bottom left (between Pencil & Binocular icons) & the 2 Drop Downs in the botton right, ORDER BY and FILTER BY. First, navigate to an existing record, then Duplicate happens sucessfully. Next, either select something in the ORDER BY Dropdown, and then try to duplicate a record after form has been sorted, it does not duplicate the record. FYI :  have written some functions right after the Duplicate command, to create entries for additional subforms etc on the same subform. ...just FYI. Same thing happens when I choose something from the FILTER BY icon. please help.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21884614

I did as you specified:
<First, navigate to an existing record, then Duplicate happens successfully. Next, either select something in the ORDER BY Dropdown, and then try to duplicate a record after form has been sorted, it does not duplicate the record.>
...and it DID duplicate the record.
(No errors)

This leads us back to MikeTooles' first post:
<The code generated by the Wizard is not the best starting point (It hasn't changed since Access 95). >
This is correct.
This is a very basic "duplicate a record" code.
It does not take into account: Sorts (Which can force a requery), filters, Recalcs, Refreshes.
All of which are included in your code.

I would take a step back and analyze this design as a whole.

If I wanted to duplicate a record I would take a serious look at using Recordset Code, as this offers more options with regard to the amount of control you have over the process.

LVL 27

Expert Comment

ID: 21885875
<If I wanted to duplicate a record I would take a serious look at using Recordset Code, as this offers more options with regard to the amount of control you have over the process.>

Jeff, agreed. Or, for the simplest of cases use the Default values approach where you don't need any buttons to get the contents of the current record into the New record row. As soon as the user types one character in any field of the row the new record is established.
Have a good weekend,


Author Comment

ID: 21896047

it does create a new record, however, if you notice, none of the values from the previous record (i.e. record you are duplicating FROM) are in the newly created record. What good is it, if you have to type evertything again. Please test carefuly.

Author Comment

ID: 21896054
Using the default value will not work, because User may want to duplicate any different case (i.e, Case 10 or Case 99).

Author Comment

ID: 21896078
I took out the error handling and am attaching a screenshot of the error message
LVL 74

Accepted Solution

Jeffrey Coachman earned 140 total points
ID: 21897273

Again, this is working fine when I try it.

Download the same database you uploaded here:
Open it and then open tblMain.
It will contain 149 records
Note the record with a MainID of 362 and a CaseNo of 142.
(This is the record we will copy)
Close the table and open the Form.
Navigate to Case number 142 (It is easier if you right-click the CaseNo box and sort)
Once you locate CaseNO 142, note some of the field values.
Now click the Duplicate record button 10 times.
(We don't see the record number increase because you have, for some reason, chosen NOT to use the standard navigation buttons and use your own system?)
But the records ARE being duplicated.
Close the Form and re-open tblMain.
There are now *159* records. (Ten more than when we started)
If you scroll down to the last 10 records you will see that *ALL* the info from MainID 362/CaseNo 142 IS duplicted 10 times. (except for the CaseNo)
Please confirm.

Am I going crazy or what?


LVL 27

Assisted Solution

MikeToole earned 140 total points
ID: 21897992
Setting the default values would work - I've used it successfully in the past.
<User may want to duplicate any different case (i.e, Case 10 or Case 99)>
My proposal was to set the defaults in the Current event of the form, if Case 10 is the current record, then Case 10 supplies the defaults, if it's Case 99, then the defaults are from that.
Having looked at your code it would probably be better to set Defaults in cmdDuplicate, replacing the three DoCmd lines. However, since setting the defaults doesn't actually add a new record, I suspect that the susequent lines would then need to be moved to the form's BeforeInsert event. They would then be executed when the user begins to edit the new record. You'd also need to reset the defaults to blank (or whatever) in the AfterInsert event.
That said, I'll leave you to pursue the other route.

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

648 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