Problem with duplicating records

[caferacer]

Hi All,

Anyone got any ideas about this?

Access 2010.

I have a form with a duplicate record button.

Sometimes when duplicating a record the duplicate is created but the form remains in edit mode (small pencil in the form margin).

I also have a manual record counter on the form which does not refresh to the newest record number following the duplication? Both issues happen together and not independently.

I now have the standard MS Access record counter visible on the form to see what’s happening and this always jumps correctly to a new record number when duplication is triggered, so when the problem happens the MS Access counter is showing one more than my own record counter.

If I use the standard MS Access record navigation buttons and go back and then forward by one record, the duplicated record is then corrected. The edit mode pencil is gone and both record counters read the same, which I assume has happened this has forced a save to happen

So, I thought by using –

If Me.Dirty
Then Me.Dirty = False
End If

- in the current event of the form, would solve the problem, but it doesn’t make any difference at all, it’s still no better, or no worse than before?

Using CTRL + S removes the pencil, but doesn't correct my bespoke record counter?

Thanks in advance?

Regards

Mark

 

[caferacer]

Hi,

No comments on my question as yet. Even just some confirmation that I haven't missed something silly or something obvious would be helpful?

Thanks

 

[Mihail]

Tell us more:
What is the code under your records counter ?
How you update it ?
What event trigger the update procedure ?
etc.

From your description, the record with troubles remain in edit mode.
So, the record is not (yet) saved on the table. => Your records counter work correctly. The number of records is not (yet) changed.
=> Try to save the new created record before to run the procedure that update the counter.

 

[youyiyang]

Hi, Caferacer,
What i am using is Access 2003 not Access 2010 you are using. Also its version is Chinese. I would download an English version right now.
But your question is something I was encountered before.
From your description, I think you use a duplication button to duplicate record when you click it. But the form cannot go to the newest record (the duplicated one) in time, right?
However, when you use the record navigation button and go back and then forward, the form seems to be saved and the duplicated record is corrected.
I once also encounter the same scenario when i was doing something else and the current event code you wrote is useless indeed.
I think this question is due to the characteristics of Access itself.
Due to my knowledge, I don't know which event would be helpful to realize your goal.
But apparently, the current event is helpless.
Maybe other veterans can help you. Or you can search the documentations in MSDN.
BTW, as i have not used Access 2010, i feel it is strange that the form changes to edit mode when you click the duplicate button.

 

[caferacer]

Hi Mihail and Youyiyang,

Thank you for taking the time to reply.

The code for the record counter is below. What is strange is I use the same code elsewhere in the same database on other forms and subforms and with other record duplicaiton buttons and there is no problem??

I have tried to make sure I have duplicated everything where I can to ensure consistency, but no luck with this one form? The code is placed in the 'On Current' event.

If Me.RecordsetClone.RecordCount = 0 Then
Me.EqptPriceRecordNo = "No Records"
ElseIf Not Me.NewRecord Then
Me.EqptPriceRecordNo = "Record " & CurrentRecord & " of " & RecordsetClone.RecordCount
Else: Me.EqptPriceRecordNo = "Record " & CurrentRecord & " of " & (RecordsetClone.RecordCount + 1)
End If

Thanks

 

[youyiyang]

Hi, Caferacer,
Try to add 2 lines below to your duplicate button code:

DoCmd.Requery
DoCmd.RunCommand acCmdRecordsGoToLast

For example, the code of duplicate button is like this:
Private Sub duplicate_record_Click()
On Error GoTo Err_duplicate_record_Click


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

Exit_duplicate_record_Click:
Exit Sub

Err_duplicate_record_Click:
MsgBox Err.Description
Resume Exit_duplicate_record_Click

End Sub

Then becomes to this:
Private Sub duplicate_record_Click()
On Error GoTo Err_duplicate_record_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
DoCmd.Requery ' add new line
DoCmd.RunCommand acCmdRecordsGoToLast ' add new line
Exit_duplicate_record_Click:
Exit Sub

Err_duplicate_record_Click:
MsgBox Err.Description
Resume Exit_duplicate_record_Click

End Sub

Hope this will help...

 

[caferacer]

Hi, Youyiyang,

In A2010, it seems to create command buttons using macros to do the work instead of a VBA event procedure as in A2003, but there is an option to convert the macro into VBA, which I did. The converted code which A2010 created wasn't the same as in your example, but I tried to place the code in different positions, but unfortunately no luck, the problem still exists.

I think I will just have to accept the problem exists and make the users aware of the problem and look for a solution when I have more time.

Thank you for you help though.

Regards
Mark


No change adding the two lines of code.