Solved

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

Posted on 2008-06-25
14
725 Views
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.
0
Comment
Question by:nemesis7
  • 5
  • 4
  • 3
14 Comments
 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
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.
0
 

Author Comment

by:nemesis7
Comment Utility
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.
0
 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
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()

    SetCurrentAsDefault

End Sub
 

Private Sub Form_Current()

    SetCurrentAsDefault

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)

        Else

            .DefaultValue = """" & .Value & """"

        End If

    End With

End Sub

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
nemesis7,

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?

JeffCoachman


0
 

Author Comment

by:nemesis7
Comment Utility
MikeTool/Boaq2000

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 it...it 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.
Copy-of-Office-Database.zip
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
nemesis7,

I did as you specified:
<First, navigate to an existing record, then Duplicate it...it 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.

JeffCoachman
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
<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,
Mike

0
 

Author Comment

by:nemesis7
Comment Utility
boaq2000:

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.
0
 

Author Comment

by:nemesis7
Comment Utility
MikeToole:
Using the default value will not work, because User may want to duplicate any different case (i.e, Case 10 or Case 99).
0
 

Author Comment

by:nemesis7
Comment Utility
I took out the error handling and am attaching a screenshot of the error message
AccessError.bmp
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 35 total points
Comment Utility
nemesis7,

Again, this is working fine when I try it.

Download the same database you uploaded here:
http://filedb.experts-exchange.com/incoming/2008/06_w26/36606/Copy-of-Office-Database.zip
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?

JeffCoachman

0
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 35 total points
Comment Utility
nemesis7,
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now