Copy record. Help!

[Guest]

I have a Main form with a subform. The subform has a query
called Details as its record source. It has a counter or
Autonumber (AccountCode)as its primary field and is also
the link to the main form. I want a simple way to copy all
of the information in the current onschreen Subform record
into a new record in the same Query/underlying table. So
that the subform now has two records the same. The user
can then change the one or two fields nessasary and save
inputting all the duplicates. I also have another form and
subform withe the same structure but from different
table/Queries. How do I copy a record from one table/Query
to another? If I cannot solve this problem soon, all my
equipment will be on the floor outside my bedroom window.

 

[Arvin Meyer]

I copy this from a far more complex routine I had, so I hope everything
works:

Private Sub cmdDuplicate_Click()
On Error GoTo Error_Handler

Dim lngNewID As Long ' ID of new record
Dim rst As DAO.Recordset ' RecordSet for Main record
Dim db As DAO.Database ' Current Database

lngOldID = Me.txtTariffEventID

Set db = CurrentDb
Set rst = db.OpenRecordset("tblYourTable")

With rst
.MoveLast
.AddNew
!Field1 = Me.txtBox1
!Field2 = Me.txtBox2
!Field3 = Me.txtBox3
!Field4 = Me.txtBox4
.Update

.Bookmark = .LastModified

'Get the new ID value
lngNewID = !ID
End With


'Requery the form and go to the new record
Me.Requery

Me.RecordsetClone.FindFirst "ID = " & lngNewID

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

Exit_Here:

' Close recordsets and set them to nothing
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer]

lngOldID = Me.txtTariffEventID

This line will keep it from compiling. Delete it.

 

[Guest]

Thanks Arvin

This is not what I expected. I thought something
like "SELECT From, where" or perhaps "INSERT into".But
hey! if it works!! who cares.This also apears to Paste
record into form. What I was hoping was that I could say
the equivelent of copy current record and stick it at the
end of the records and hope that the auto number would
look after itself.

Can you explain why I can't just move to last record in
the set assuming that is where the new record would
be "Pasted"

Thanks again

 

[Arvin Meyer]

You can use an insert statement. Using a DAO recordset is a bit easier
though.

You are moving to the last record (rst.MoveLast) and inserting a new record
(rst.AddNew)
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Derek]

Thanks again Arvin.

I'm sorry to be such a plonker but is there no way I can
simply copy what I would do if looking directly at the
current record in the query, select it copy it and paste
append. The reason I ask is simply that I am vot sure
where the
With rst
.. movelast
..AddNew
!Field1 = Me.txtBox1
!Field2 = Me.txtBox2
!Field3 = Me.txtBox3
!Field4 = Me.txtBox4

fits in or why it is nessasary. If I am trying to copy one
record to a blank record in the query? Is this effectively
copying the original record into a blank form, if so will
I get the same errors that occur when I manualy copy the
curent record whilst in the form, go to new record in the
form and try to paste it. When I do this I get
error "Update or cancel update without AddNew or Edit"

Life sure gets tedious man!!

 

[Arvin Meyer]

You can manually copy and paste the record. There are several steps and you
don't get any feedback if there is an error. If you use code, it's 1 step
(click a button) and you can do all the validation BEFORE you try to add the
record and/or handle the error afterwards. There is no easier way, although
it may seem that way. The code is not that difficult, just replace the field
names in the code with the actual field names in your table. Likewise,
replace Me.txtBox1 with the actual name of the textbox in your form. "Me" is
a keyword that refers to the active form you are running the code from.

The autonumber key for the new record is filled in automatically, so you do
not need to try and paste it in. Just create a button (cmdDuplicate) and
paste the ENTIRE code in a module and delete the line: lngOldID =
Me.txtTariffEventID as it will cause an error (it came from the original
code I wrote). Now make sure you have good names for all your controls so
that you won't conflict with field names or keywords (i.e. never name a
field or a textbox: Date, use DateScheduled and txtDateScheduled for the
field and textbox names).

If you are still having trouble, post back and include both the field names
in the table, and the textbox names from the form, and I will walk you
through each line of code explaining what it does and how it works. Be
patient. You'll eventually get it and look back on this with a smile.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Derek]

Thanks again Arvin

I have posted a further question on the newsgroup related
to this one which I have just found is not good practice.
So my appologies. The new question simular to this is
simply how do I copy accross forms based on different
tables. Is there no facility to simply say go to forms
underlying query, copy current record into memory, close
form and open new form and paste append into that forms
query? Am I expecting too much. I have customers that all
need slightly different fields so if I do it in code I
have to remember to alter underlying code every time. If I
could just copy record this would be unnessassary.