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
Solved

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

Posted on 2008-06-25
14
730 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
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.
0
 

Author Comment

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

Expert Comment

by:MikeToole
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()
    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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21880712
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
ID: 21881412
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
ID: 21884614
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
 
LVL 27

Expert Comment

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

0
 

Author Comment

by:nemesis7
ID: 21896047
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
ID: 21896054
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
ID: 21896078
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
ID: 21897273
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
ID: 21897992
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

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

839 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