Help with copy record button

[aftershokk]

Hi all!

I have a copy button on a patient entry screen that allows the user to copy the record in current view to a new record. When the copy is made it appears Access does append a new row and key to the table. However, when I start to edit the new record it reverts back to the original record I copied so I am now editing the original record instead of the new copied record?

Example:

current view
id 139 John Doe
copy command button to
new id 725 John Does
When I start to edit the name, I am actually editing id 139 instead of 725

I am assuming it it probably a line of 2 of code that can be added to keep the new record active on the form?
thanks!

VBA code below:

Private Sub copy_rec_command_button_Click()
On Error GoTo Err_copy_rec_command_button_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append


Exit_copy_rec_command_button_Click:
Exit Sub

Err_copy_rec_command_button_Click:
MsgBox Err.Description
Resume Exit_copy_rec_command_button_Click

 

[Scooterbug]

My spider sense is tingling on this one....creating a duplicate paitent information sounds like you have normalization issues.

Not sure what all the MenuItems do...but what you should be doing is an Append Query, not a simple cut and paste.

I strongly suggest that you read up on normalization....Having a normalized database will make things easier for you in the long run.

 

[aftershokk]

it does create a new primary key and we like to copy the same info over and just edit it as most of it is the same. It is not really a patient DB just trying not to divulge to much.

If I perform this process now and then scroll back one record and then up one it works fine.

 

[Scooterbug]

You have to instruct Access to go to that record that was just created.

DoCmd.GoToRecord , , acNext

As for the Normalization....it's your database so it's your choice. But I would highly recommend reading up on it. Anyone here can contest to the fact that the bigger the db gets, the more nightmares you will run into.

 

[aftershokk]

thanks I actually figured that out just now
I understand normalization but this feature is a copy to next feature that is available in many health systems now and our users demand it. Plus, the DB is only 300 records

 

[aftershokk]

actually this works

DoCmd.GoToRecord acForm, "pho_dir_qry_form_view", acPrevious, 1
DoCmd.GoToRecord acForm, "pho_dir_qry_form_view", acNext, 1