Help! Problem Duplicate Record Command Button

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a database used to track error information. Within this
database I created a form that is used to enter Quality Control information;
such as any item that is QC'd or checked by another employee in order to
locate any errors that might exist.

Occasionally, more than one error is found on a particular item. For
simplicity, I created a duplicate record command button in order to copy the
current record to a new record for editing purposes. However, it is beyond
me why it is not working. It seemingly copies the record and takes you to
the new copy, but when you click on any field to edit the new copy, it
immediately takes you back to the original error, and any changes made are
made to the original error. Also, the copy disappears. A new record is
saved, but all the information that was supposedly copied has disappeared and
the new record is blank. I have compared the VBA code to the code on a
different database where the Duplicate Record button works, and I cannot find
any difference in the code. I tried a couple of other ways to copy a record
within a form but have been unsuccessful in my attempts. I created an append
query and tied it to a command button. That worked fine except I could not
figure out how to view the new copy (within the form) without closing and
reopening the form. I tried using a refresh button, but it didn’t work
either.

I am fairly new to Access, and this may be something simple that I just have
not thought of yet. Any help with getting the duplicate record button to
work will be greatly appreciated.

Tiffany
 
Please post the code you are using to cerate the duplicate record and your
refresh procedure to the newsgroup.

Also, what are you using for the recordsource for your form? Is it a table
or a query?

Jason
 
I am very new to the wide world of VB and will be learning for quite some
time. I created the code (see below) based on the help files and things I
have learned in the newsgroups. The record source for this form is a table.
The code below works fine except I don’t know how to get the form to navigate
to the new copy of the original record – it goes to the first record once the
copy and requery has taken place. Is the second variable I declared below a
necessary first step in the navigation process? If so, should the variable
be named after the table it refers to instead of being named rstRecordset?
The table name (created before I knew what naming conventions were all about)
is named Error Report Table.

Private Sub Copy_Record_Click()
On Error GoTo Err_CopyRecord_Click

Dim stDocName As String
Dim rstRecordset As Recordset

stDocName = "CopyToNewRecordQuery"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.Requery

Exit_Copy_Record_Click:
Exit Sub

Err_Copy_Record_Click:
MsgBox Err.Description
Resume Exit_Copy_Record_Click

End Sub

Thanks for taking a look at this for me.

Tiffany
 
The code that was not functioning on the Duplicate Record Command Button that
was created with a wizard is pasted below:

Private Sub CopyToNewRecord_Click()
On Error GoTo Err_CopyToNewRecord_Click

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

Exit_CopyToNewRecord_Click:
Exit Sub

Err_CopyToNewRecord_Click:
MsgBox Err.Description
Resume Exit_CopyToNewRecord_Click

End Sub

I forgot to include this in my last reply because I had decided to go a
different route. However, if this is easier to fix using this then that is
certainly fine with me. Any help is greatly appreciated.

Tiffany
 
I will get this right eventually...here is the refresh procedure.

Private Sub Refresh_Click()
On Error GoTo Err_Refresh_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Refresh_Click:
Exit Sub

Err_Refresh_Click:
MsgBox Err.Description
Resume Exit_Refresh_Click

End Sub

Sorry for not getting all of this is the same posting.

Tiffany
 
I looked at the method that you created with the wizard as well as the method
of using an append query. I did not have any issues with either method.
Also since the form has a table as a recordsource, you should not need to
refresh.

Assuming that you are not sorting the data in the form, the duplicated
record should be the last record in your table/form after your append
procedure. You can navigate to it by using:

Me.Recordset.MoveLast


I could not replicate the problem you are having with the wizard based
procedure. It worked for me as I am sure you expect it to. There must be
something else going on that is blanking out the record. To help you find
the problem you can step through the code line by line as it executes. By
monitoring the results of each line of code, you should be able to see when
the problem occurs.

As I mentioned, you should not have to refresh your data if you are using a
table for your recordsource for your form, but here is one way to refresh
your form data:

Me.Recordset.Requery

This will refresh the data without having to close/reopen your form.

HTH
Jason
 
Back
Top