duplicate record VB help

[aftershokk]

I have a form and a subform. The DB was pre-existing so I am trying to work with it. I used the wizard to create a duplicate record button to cut down on entry data for the user.

When viewing a certain record I want to be able to copy and paste the data in the form and subform into a new record and populate the form and subform. I can get the main form to work using the code below but not the subform.

Private Sub copy_rec_command_button_Click()
On Error GoTo Err_previous_record_button_Click

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend


Exit_previous_record_button_Click:
Exit Sub

Err_previous_record_button_Click:
MsgBox Err.Description
Resume Exit_previous_record_button_Click

End Sub

 

[OlcayM]

Hi,

You need to know the link field between main and subform. If they are non duplicate key fields, possibly you will get an error message. Will you give a little more detail?

OlcayM

 

[aftershokk]

reply

The main form and subform is linked. When I add a new record it works fine. I want to be able to copy the current record to a new one and at the same time assign the new key. It works for the main form but not the sub-form.

 

[OlcayM]

Hi,

I recommend you another way of doing it

1) Make a query to append on the current record of main form, except link field. It seems you are assigning it. So during the run of the query, query must take the link field's value from somewhere you prefer (an unbound field in your form, or an input form etc...)
2) Make another query to append the current record of the subform, except link field. During the run of the query, query must take the link field's value from somewhere you prefer (an unbound field in your form, or an input form etc...)

This will do it.

HTH
OlcayM

 

[aftershokk]

reply

How to I duplicate though just the current record that the user is viewing?
Right now I can duplicate the main form record and the id is assigned for the main form and subform. I just will not copy the subform to subform. I am not sure how to modify the VB to copy the subform elements also.

thanks

 

[OlcayM]

"How to I duplicate though just the current record that the user is viewing?"
This is not a problem
Do you know how to create an append query? If not, send your database (if you feel comfortable and only the part with the problem) and I 'll try to solve and send back.

 

[aftershokk]

reply

sorry cannot send

I know how to create an append query but only want to copy/paste for the current record chosen by the user while viewing a single form.

thanks

 

[ajetrumpet]

Shok,

Questions, if you don't mind. You say that the tables are linked. Are you trying to carry the current record's data forward to create a new record with the same data? Is that what you mean by wanting to reduce user data input? Are you trying to do this for both forms? If so, are the tables joined somehow?

 

[aftershokk]

reply

yes on all questions, tables are linked by a key

the main table has a autonumber field

thanks