By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,295 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

Create a duplicate record

P: n/a
Ray
I have a data input form and need to automatically duplicate the existing
record as a new record by clicking a button. The main purpose to duplicate
the record is that the new record is very similar to the existing record
with minor differences. It will save the data input operator a lot of
afford by editing the minor difference only. However, the new record is not
allowed to be saved if the operator forgets to make the modification for
whatever reasons to avoid duplicate records. Your guidance to accomplish it
is appreciated.

Thanks,

Ray

Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Here's ADO code for a copy button I use. This is a bound form with a
listbox containing the list of addresses, with the bound column being the
primary key. Start Date is the only change required.
================================
Private Sub btnCopy_Click()
On Error GoTo handle_error
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset
If IsNull(Me.lstRecords.Value) Then
MsgBox "No item selected", , "Selection Needed"
Else
With rs
.Open "address", cnxn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
.Index = "PrimaryKey"
.Seek Me.txtId.Value, adSeekFirstEQ
If Not .EOF Then
DoCmd.RunCommand acCmdRecordsGoToNew
Me("add_off_id") = .Fields("add_off_id")
Me("add_state_id") = .Fields("add_state_id")
Me("add_residtype_id") = .Fields("add_residtype_id")
Me("add_house_no") = .Fields("add_house_no")
Me("add_apt") = .Fields("add_apt")
Me("add_street_id") = .Fields("add_street_id")
Me("add_zip_id") = .Fields("add_zip_id")
Me("add_loc_id") = .Fields("add_loc_id")
Me("add_map_id") = .Fields("add_map_id")
End If
.Close
End With
Set rs = Nothing
Me.txtStartDate.SetFocus
End If
Exit Sub
handle_error:
LogError Err.Number, "AddressForm.btnCopy", Err.Description '
Error-logging sub
End Sub
============================
You could add code that stored the old start date, and compared it to the
new start date, and did not allow a save if they were the same.
Darryl Kerkeslager
"Ray" <No**************@Yahoo.com.hk> wrote in message
news:33*************@individual.net...
I have a data input form and need to automatically duplicate the existing
record as a new record by clicking a button. The main purpose to duplicate the record is that the new record is very similar to the existing record
with minor differences. It will save the data input operator a lot of
afford by editing the minor difference only. However, the new record is not allowed to be saved if the operator forgets to make the modification for
whatever reasons to avoid duplicate records. Your guidance to accomplish it is appreciated.

Nov 13 '05 #2

P: n/a
rkc
Ray wrote:
I have a data input form and need to automatically duplicate the existing
record as a new record by clicking a button. The main purpose to duplicate
the record is that the new record is very similar to the existing record
with minor differences. It will save the data input operator a lot of
afford by editing the minor difference only. However, the new record is not
allowed to be saved if the operator forgets to make the modification for
whatever reasons to avoid duplicate records. Your guidance to accomplish it
is appreciated.


If you mean you want to carry the current values of the controls
over into a new record to be entered using the same form then:

Look up DefaultValue using the Help system.
The easiest way to do that is to open a code module,
type defaultValue, place the cursor over the word and
hit F1.

After you have read that go here:

http://www.mvps.org/access/forms/frm0012.htm

then here:

http://www.mvps.org/access/forms/frm0027.htm
After you have read that try this:

Private Sub cmdSetDefaultValues_Click()
Dim ctl As Access.Control

On Error Resume Next

For Each ctl In Me.Controls
ctl.defaultValue = """" & ctl.Value & """"
Next

If Not ctl Is Nothing Then Set ctl = Nothing

End Sub

Nov 13 '05 #3

P: n/a
This is the problem I have with Access. Look at this answer!
Private Sub btnCopy_Click()
On Error GoTo handle_error
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset
If IsNull(Me.lstRecords.Value) Then
MsgBox "No item selected", , "Selection Needed"
Else
etc.etc.etc.

When I used Lotus Approach, there was an icon and a dropdown command to
duplicate the record presently being displayed. That was it!

I saved a reply from a while back to the same question I posed:
For instance, duplicating a record is a handy button to have, but there is

none

Here's the reply I got:

"There is one...just click the Command Button icon on the Toolbox and pick
the Records Category and choose Duplicate Record from the right side."

But, how do I get the Tool Box to appear? When I have a record open in Forms, I
see no icon for Toolbox, I see it in no dropdown menu, and when I enter
"toolbox" in Help, there is nothing about how to access it. When I finally got
the toolbox to appear, from some options menu (I think), it was completely
grayed when I had a record open in Forms.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com
Nov 13 '05 #4

P: n/a
I just found that the Edit menu can be used: select the record, copy, Enter
(for new blank record), paste. After that you have to hit Paste Append for a
third duplicate and after. Not just one click, but better than nothing. Why
isn't this in the Help system?

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com
Nov 13 '05 #5

P: n/a
nh******@aol.comnojunk (Nhmiller) wrote:
That Duplicate Record Button is GREAT -

BUT I get error messages, linked to this:
Private Sub Combo94_AfterUpdate()
Me.FilterOn = False
DoCmd.GoToControl "[IDae]"
DoCmd.FindRecord Forms![Grant3AEModified]![Combo94]
End Sub

The FilterOn is highlighted as the offending statement -
Another combo box event interferes too.
I've got some "required' fields in the record.

If I go to the Table select the record; copy; paste append - it works
without errors.
Any ideas?

Here's the reply I got:

"There is one...just click the Command Button icon on the Toolbox and pick
the Records Category and choose Duplicate Record from the right side."

But, how do I get the Tool Box to appear? When I have a record open in Forms, I
see no icon for Toolbox, I see it in no dropdown menu, and when I enter
"toolbox" in Help, there is nothing about how to access it. When I finally got
the toolbox to appear, from some options menu (I think), it was completely
grayed when I had a record open in Forms.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

-warning e-mail address altered- arthureNOSPACE@
Nov 13 '05 #6

P: n/a
I have a problem with the duplicate record button however...

when I do minor changes on the duplicate, it erases the values of the
original. how can I make the two records independent of each other?

is there some sort of "ok, i'm done copying, now break the
relationship" kind of code?

help!

Nov 13 '05 #7

P: n/a
Information Queen wrote:
I have a problem with the duplicate record button however...

when I do minor changes on the duplicate, it erases the values of the
original. how can I make the two records independent of each other?

is there some sort of "ok, i'm done copying, now break the
relationship" kind of code?

help!

Usually the record should be saved first.

You might want to look at the Runcommand method of Docmd. I don't know
if these will work...you may want to look at the constants.
Docmd.Runcommand acCmdSaveRecord
Docmd.Runcommand acCmdDuplicate 'unsure of this one
You could put this code behind a command button or make a new menu bar
option.
Nov 13 '05 #8

P: n/a
yup, i ran into the "the duplicate command is not available now.

here is the code behind the duplicate button..

Private Sub cdmDuplicate_Click()
On Error GoTo Err_cdmDuplicate_Click
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

Exit_cdmDuplicate_Click:
Exit Sub

Err_cdmDuplicate_Click:
MsgBox Err.Description
Resume Exit_cdmDuplicate_Click

End Sub

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.