Duplicating a Record in Access XP

[Donna]

In Access 2000 I had the following code to duplicate a record and put
certain data in the original record and in the duplicated record. Now all
it is doing is changing the original rather than duplicating. Thanks in
Advance.

Private Sub MoveRecord_Click()
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Me![OriginalBidNumber] = [BidNumber]
Me![BidNumber] = [NewBidNumber]
Me![NewBidNumber] = Null
Me![BidDate] = Null
Me![ActualBidDate] = Null
Me![DateReceived] = Null
Me![RebidDueDate] = Null
Me![RebidDate] = Null
Me![Job] = No
Me![JobNumber] = Null
Me![CompetitorID] = Null
Me![DateLanded] = Null
Me![ProjectNotes] = Null
Me![BidTypeID] = 1
Me![HotList] = No
Me![Odds] = Null
Me![FollowUp] = Null
Me![ProjectedStart] = Null
Me![Withdrawn] = No
Me![MovedBid] = No
Me![JobUpdated] = No

Exit_MoveRecord_Click:
Exit Sub

Err_MoveRecord_Click:
MsgBox Err.Description
Resume Exit_MoveRecord_Click

End Sub

 

[Michel Walsh]

Hi,


If your actual record has a primary key, pk, an autonumber field, then,
I would try:


CurrentDb.Execute "INSERT INTO tableNameHere (list Of FIelds Here,
without the autonumber field) SELECT (list Of fields here, without the
autonumber field) FROM tableNameHere WHERE pk=" & pk


or

DoCmd.RunSQL "INSERT INTO tableNameHere (list Of FIelds Here,
without the autonumber field) SELECT (list Of fields here, without the
autonumber field) FROM tableNameHere WHERE
pk=FORMS!FormNameHere!ControlNameHere" )

If you prefer, I would use an SQL statement rather than a
DoCmd.DoMenuItem sequence of statements.

Since VBA is "another user" (not the same than the one typing at the
keyboard, at least), that record won't be seen by others unless they
requery.



Hoping it may help,
Vanderghast, Access MVP