Copy old record data to new record

[Aussie58]

Hi all. First post. Thanks for all the useful info here.



Due to the type of the work my users do, they regularly enter new records that are more or less the same as old ones. To help facilitate this, I'd like to design a form that asks them to enter a record ID number to call the old record they want to copy, then they could hit an "OK" button and have the DB automatically copy the called record to a new record. I'd like the new record to appear in the standard entry form I long ago designed so they can edit the few fields that need editing. Make sense? Any ideas on the best, cleanest and easiest way to do this?

Thanks!

[GinaWhipp]

Perhaps this will help...

http://allenbrowne.com/ser-24.html

[Aussie58]

Thanks Gina. I think this is on the right track. However, the code displayed on the page you link to is specifically for carrying over/copying "the last record in the form," whereas I'm wanting to let my users specify which record to carry over/copy.

The part of the code that governs this seems to be the following lines:

If Not bCancel Then
'The last record in the form is the one to copy.
rs.MoveLast

Any idea how I might change rs.MoveLast to something like (pseudo code): Move Record Identified At Form X?

Again, I want my users to be able to identify a specific record to copy over by means of a unique identification number they enter on a separate form, which ties to a specific record.

Thanks.

[GinaWhipp]

Okay, first I thinking this...

http://www.allenbrowne.com/ser-57.html

...but then I see that you said...

they enter on a separate form

...so I am a little concerned is there a seperate form involved?

EDIT: I didn't ignore your question but thought the above link might work better because it does copy the record you are on.

[Aussie58]

No worries. I didn't think you ignored my questions. Yes, I want a separate form wherein my users can simply enter a unique ID number (that they'll know in advance), which ties to a specific record in the DB, press an "OK" type button, and then have the record duplicated. Again, the page you sent me to is on the right track, it's just that it copies the "last record" instead of a specified record.

[GinaWhipp]

Okay... let me play with that. Not my code so I want to be sure what it does before I speculate what to change...

[GinaWhipp]

Okay, it's more then just that section that says only the last record. Try...

Private Sub cmdYourCommandButton_Click()
'THANK YOU Allen Browne - Microsoft MVP

Dim db As DAO.Database
Dim strOrderID As String
Set db = DBEngine(0)(0)
If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the Record to duplicate!", vbInformation, "Duplicate"
Else
'Duplicate the main record
With Me.RecordsetClone
.AddNew
!oOrderDate = Date
!oCustomerID = Me.cboCustomerID
!oEndUseDescription = Me.cboEndUseDescription
!oSalesOrderNumber = Me.txtSalesOrderNumber
!oEndUserOrderNumber = Me.txtEndUserOrderNumber
!oOrderID = 0
.Update
.Bookmark = .LastModified
strOrderID = !oOrderID
'Display the duplicate.
Me.Bookmark = .LastModified
End With
End If
Set db = Nothing
End Sub

...changing the bolded field names to match yours. This will duplicate the last record you are on.

[Aussie58]

Hi Gina. Thank you very much. I think this is almost complete! I changed the code to refer to my fields, then assigned it to run at the press of a command button. Upon a button press, here's the error that gets thrown up:

"Run-time error '7951':
"You entered an expression that has an invalid reference to the RecordsetClone property."

Then when I debug it points me to:

With Me.RecordsetClone

Any ideas?

[GinaWhipp]

Does the form have a RecordSource or is it UNBOUND?

[Aussie58]

Yes, the form is bound to a table as its RecordSource. I think I'm past that issue now, and getting closer. I'm now getting an error where it seems to think (perhaps) my field names are improper in syntax (or something like that):

"Run-time error '3625':
"Item not found in this collection"

Then when I run the debugger it points me to my field names. Actually, I've reduced it down to testing only one field just to see if I can get it working. I've tried every iteration I can think of.

Question: is the "!o" required before the field name?

Sorry...my VBA is extremely limited. Thanks again!

[GinaWhipp]

No, just the *!* is required. Why don't you post what you have here and let's see if we can figure out what's going on.

[Aussie58]

Gina,

No need to, I just got it working. THANK YOU VERY MUCH. The main issue (aside from my VBA slack) was that I was trying to have the DB copy the key field, which it (obviously) won't do. Thanks again!

[GinaWhipp]

You're most welcome!