Att John Spencer (MVP) Copy/Duplicate record problem

[David]

John (or any on else)



I need some help regarding a piece of VBA code you supplied back on the
22/4/2003 "copy / duplicate problem"

I have tweaked the example (for use on Access 97) so that it will work from
a control button on my form.

(I have a number of tabs making up the form) I have got the code to work in
the respect of duplicating a form when I hit the button.(Other than I have
to hit F5 or the record section buttons to see it) However you included a
line of optional code ".Bookmark = Lastmodified" to move the form to the new
record this bit I can not get to work.

As what I need is once I have made a copy for the programme to move to and
displayed that copy so that it can be worked on, not just sit at the
original form/record.

If this is not how it should work any examples of what I am trying to
achieve would be most helpful.



You original code followed by my tweaked code.



Public Sub sDuplicateCurrentRecord(strFormName As String)'Duplicate the
current record on a formDim frmAny As FormDim rst As RecordsetDim
aVarContents() As VariantDim iCount As IntegerDim iFldCount As Integer On
Error GoTo Err_cmdDuplicateCurrent Set frmAny = Forms(strFormName)
If MsgBox("Duplicate the current record?", _ vbYesNo, "Duplicate") =
vbYes Then 'Save current record if needed If frmAny.Dirty
Then frmAny.Dirty = False 'Duplicate the current record on the form
into an array iFldCount = frmAny.RecordsetClone.Fields.Count - 1
ReDim aVarContents(iFldCount) 'resize the array Set rst =
frmAny.RecordsetClone rst.Bookmark = frmAny.Bookmark 'Get
contents of Current record For iCount = 0 To iFldCount
aVarContents(iCount) = rst.Fields(iCount) Next iCount 'This code
relies on the Primary key being an autonumber field With rst
..AddNew For iCount = 0 To iFldCount If
..Fields(iCount).Attributes _ And dbAutoIncrField Then
'Autonumber field so Skip and let it fill 'automatically
Else 'This is not primary key so populate field If
IsNull(aVarContents(iCount)) = False Then 'check for nulls,
else do it .Fields(iCount) = aVarContents(iCount)
End If End If Next iCount .Update'Optional line
to move form to new record .Bookmark = .LastModified End With
End If 'Do you want to duplicate Exit_cmdDuplicateCurrent: Exit Sub
Err_cmdDuplicateCurrent: MsgBox Err.Number & ": " & Err.Description, ,
"sDuplicateCurrentRecord" Resume Exit_cmdDuplicateCurrent End Sub My
tweaked code omitting error handling Private Sub cmdUpIssue_Click()


'Duplicate the current record on a form
Dim Log As Form
Dim rst As Recordset
Dim aVarContents() As Variant
Dim iCount As Integer
Dim iFldCount As Integer





Set Log = Forms("Log")

If MsgBox("Duplicate the current record?", _
vbYesNo, "Duplicate") = vbYes Then

'Save current record if needed
If Log.Dirty Then Log.Dirty = False

'Duplicate the current record on the form into an array
iFldCount = Log.RecordsetClone.Fields.Count - 1

ReDim aVarContents(iFldCount) 'resize the array

Set rst = Log.RecordsetClone
rst.Bookmark = Log.Bookmark

'Get contents of Current record
For iCount = 0 To iFldCount
aVarContents(iCount) = rst.Fields(iCount)
Next iCount

'This code relies on the Primary key being an autonumber field
With rst
.AddNew
For iCount = 0 To iFldCount
If .Fields(iCount).Attributes _
And dbAutoIncrField Then
'Autonumber field so Skip and let it fill
'automatically

Else 'This is not primary key so populate field
If IsNull(aVarContents(iCount)) = False Then
'check for nulls, else do it
.Fields(iCount) = aVarContents(iCount)
End If
End If
Next iCount
.Update
'Optional line to move form to new record
.Bookmark = .LastModified
End With
End If 'Do you want to duplicate



End Sub

 

[Larry Linson]

Davidfor a thread as old as April of 2003, you should really include
details. Otherwise, anyone who would be interested in helping would have to
go out to Google, find the original thread (not always easy even if your
subject is an exact quote). Few people here, I can assure you, will have the
time and energy to do that much research just to understand the question you
are asking.

Larry Linson
Microsoft Access MVP

 

[David]

Hi
Larry
Point taken.
http://groups.google.co.uk/groups?hl=en&lr=&[email protected]&rnum=4

Dave

 

[John Spencer (MVP)]

David,

Sorry to say, that I can't recall how that code was supposed to work to give you
a reference to the newly created record. You might try grabbing the primary key
and stuffing it into a variable right before the .Update statement.

Then you can requery the form and use the value of the variable to goto the
correct record.

I'll try to take a look at that code when I don't have two glasses of wine in
me. Right now, I would not be sure of any solution that I developed.

 

[Fred Boer]

Aw, c'mon John... have a couple more and *then* try... It'll be fun to see
what comes out! <g>

Fred Boer

 

[John Spencer (MVP)]

If you want to move to the record you just created, the I think you can just use
MoveLast as in

'Optional line to move form to new record
Forms("Log").MoveLast

Sorry that this took so long, but I've been busy and finally took the time to
actually look at the code.

 

[David]

John
Sorry to say in my case your example did not work, I got Runtime Error 2465.
However using the following bit of code got my sub to work:-
End With
End If 'Do you want to duplicate
DoCmd.GoToRecord acDataForm, "log", acLast

Thank you for your time

All the best
Dave