How do I copy access record with primary key using VBA?

[Bob]

Hello,

I'm in the process of writing a maintenace program. It is a single table application. Simplist of all!!! BUT..... When I go to copy a record and
change the primary key, it always comes back and says it couldn't perform the operation because it would create a duplicate.

Help me out on this one please. Here's my code:

Private Sub btnCopy_Click())
Dim Response
Response = MsgBox("Are you sure you want to COPY this record?", vbYesNo, "Copy Confirmation")
If Response = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSave
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPaste
MIRCtl.Value = 99999
MIRCtl.SetFocus
MIRDateInit.Value = Now
MIRCloseDate.Value = MIRDateInit.Value
AddedIndicator.Caption = "Copy"
AddedIndicator.Visible = True
End If
End Sub
-------------------------------------------------------------------------------------------------------

MIRCtl is the primary key. I want to change the copied key to '99999' actually before pasting the record so it doesn't create a dupe then insure the
user changes it before he saves it, if he doesn't, I'll delete the record with the '99999' key.

I've seen some light examples but those had a PK assigned by Access. It seems so easy, but I can't get the sequence of commands proper and I'm
probably missing some.

Could someone straighten me out on this??????

Bob

 

[banem2]

Hello,

I'm in the process of writing a maintenace program.  It is a single table application.  Simplist of all!!!  BUT..... When I go to copy a record and
change the primary key, it always comes back and says it couldn't perform the operation because it would create a duplicate.

Help me out on this one please.  Here's my code:

Private Sub btnCopy_Click())
    Dim Response
    Response = MsgBox("Are you sure you want to COPY this record?", vbYesNo, "Copy Confirmation")
    If Response = vbYes Then
         DoCmd.SetWarnings False
         DoCmd.RunCommand acCmdSave
         DoCmd.RunCommand acCmdSelectRecord
         DoCmd.RunCommand acCmdCopy
         DoCmd.RunCommand acCmdPaste
        MIRCtl.Value = 99999
        MIRCtl.SetFocus
        MIRDateInit.Value = Now
        MIRCloseDate.Value = MIRDateInit.Value
        AddedIndicator.Caption = "Copy"
        AddedIndicator.Visible = True
        End If
End Sub
-------------------------------------------------------------------------------------------------------

MIRCtl is the primary key.  I want to change the copied key to '99999' actually before pasting the record so it doesn't create a dupe then insure the
user changes it before he saves it, if he doesn't, I'll delete the record with the '99999' key.

I've seen some light examples but those had a PK assigned by Access.  Itseems so easy, but I can't get the sequence of commands proper and I'm
probably missing some.

Could someone straighten me out on this??????

Bob

As program tells you there is duplicated ID, you probably already have
record with ID = 99999.

To prevent this happening (user can save at any time ID with '99999')
maybe you need to change your code to:

MIRCtl.Value = NZ(DMax("[MIRCtl]", "myTable"),0)+1

This will write in ID field next ID number which user needs to change.
You can keep your code, but you need to check ID field with code in
BeforeUpdate event, so that the user has retyped ID 99999 with
something else.

Regards,
Branislav Mihaljev, Microsoft Access MVP

 

[john.fuller]

Hello,

I'm in the process of writing a maintenace program.  It is a single table application.  Simplist of all!!!  BUT..... When I go to copy a record and
change the primary key, it always comes back and says it couldn't perform the operation because it would create a duplicate.

Help me out on this one please.  Here's my code:

Private Sub btnCopy_Click())
    Dim Response
    Response = MsgBox("Are you sure you want to COPY this record?", vbYesNo, "Copy Confirmation")
    If Response = vbYes Then
         DoCmd.SetWarnings False
         DoCmd.RunCommand acCmdSave
         DoCmd.RunCommand acCmdSelectRecord
         DoCmd.RunCommand acCmdCopy
         DoCmd.RunCommand acCmdPaste
        MIRCtl.Value = 99999
        MIRCtl.SetFocus
        MIRDateInit.Value = Now
        MIRCloseDate.Value = MIRDateInit.Value
        AddedIndicator.Caption = "Copy"
        AddedIndicator.Visible = True
        End If
End Sub
---------------------------------------------------------------------------­----------------------------

MIRCtl is the primary key.  I want to change the copied key to '99999'actually before pasting the record so it doesn't create a dupe then insure the
user changes it before he saves it, if he doesn't, I'll delete the record with the '99999' key.

I've seen some light examples but those had a PK assigned by Access.  It seems so easy, but I can't get the sequence of commands proper and I'm
probably missing some.

Could someone straighten me out on this??????

Bob

As program tells you there is duplicated ID, you probably already have
record with ID = 99999.

To prevent this happening (user can save at any time ID with '99999')
maybe you need to change your code to:

MIRCtl.Value = NZ(DMax("[MIRCtl]", "myTable"),0)+1

This will write in ID field next ID number which user needs to change.
You can keep your code, but you need to check ID field with code in
BeforeUpdate event, so that the user has retyped ID 99999 with
something else.

Regards,
Branislav Mihaljev, Microsoft Access MVP- Hide quoted text -

- Show quoted text -

I would think the error would actually be coming from when he tries to
paste the record back in, it still has the same MIRCtl value as the
one he copied, therefore creating a duplicate every time. I had a
similar problem like this a worked around it by creating small
function in VBA. Its not necessarily pretty, but it works. Should be
able to modify it fairly easily. The Profile field is my primary key
field.

Sub Duplicate_Entry(tblName As String, OldProfileNumber As Integer,
NewProfileNumber As Integer)
Dim myRstOld As DAO.Recordset, myRst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT " & tblName & ".* FROM " & tblName & " WHERE " &
tblName & ".Profile = " & OldProfileNumber
Set myRstOld = CurrentDb.OpenRecordset(strSQL)

If myRstOld.RecordCount = 1 Then
Set myRst = CurrentDb.OpenRecordset("SELECT " & tblName & ".*
FROM " & tblName)

With myRst
.AddNew
For i = 0 To .Fields.Count - 1
.Fields(i).Value = myRstOld.Fields(i).Value
Next i
.Fields("Profile").Value = NewProfileNumber
.Update
End With
End If
End Sub

 

[Marshall Barton]

I'm in the process of writing a maintenace program. It is a single table application. Simplist of all!!! BUT..... When I go to copy a record and
change the primary key, it always comes back and says it couldn't perform the operation because it would create a duplicate.

Help me out on this one please. Here's my code:

Private Sub btnCopy_Click())
Dim Response
Response = MsgBox("Are you sure you want to COPY this record?", vbYesNo, "Copy Confirmation")
If Response = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSave
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPaste
MIRCtl.Value = 99999
MIRCtl.SetFocus
MIRDateInit.Value = Now
MIRCloseDate.Value = MIRDateInit.Value
AddedIndicator.Caption = "Copy"
AddedIndicator.Visible = True
End If
End Sub
-------------------------------------------------------------------------------------------------------

MIRCtl is the primary key. I want to change the copied key to '99999' actually before pasting the record so it doesn't create a dupe then insure the
user changes it before he saves it, if he doesn't, I'll delete the record with the '99999' key.

I've seen some light examples but those had a PK assigned by Access. It seems so easy, but I can't get the sequence of commands proper and I'm
probably missing some.

Using Copy/Paste is inadequate for what you want.

Setting the key before copying the record would modify the
current record and make a mess of things.

Since you want to set some values in your code and leave
others for the user to fill in, you need to set each field
separately.

I suggest that you first save the field values you want to
copy, go to the new record and then set the fields. The
general idea is along the lines of:

Dim v1 As Variant
Dim v2 As Variant
. . .
If Me.Dirty Then Me.Dirty = False 'save record
v1 = Me.field1
v2 = Me.field2
. . .
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.field1 = v1
Me.field2 = v2
. . .
MIRCtl.Value = 99999
MIRCtl.SetFocus
MIRDateInit.Value = Now
MIRCloseDate.Value = MIRDateInit.Value
AddedIndicator.Caption = "Copy"
AddedIndicator.Visible = True

I see no benefit to setting the key to 9999, then using the
form's BeforeUpdate event to undo the newrecord. Leaving
the field Null should accomplish the same thing.

 

[Bob]

Using Copy/Paste is inadequate for what you want.

Setting the key before copying the record would modify the
current record and make a mess of things.

Since you want to set some values in your code and leave
others for the user to fill in, you need to set each field
separately.

I suggest that you first save the field values you want to
copy, go to the new record and then set the fields. The
general idea is along the lines of:

Dim v1 As Variant
Dim v2 As Variant
. . .
If Me.Dirty Then Me.Dirty = False 'save record
v1 = Me.field1
v2 = Me.field2
. . .
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.field1 = v1
Me.field2 = v2
. . .
MIRCtl.Value = 99999
MIRCtl.SetFocus
MIRDateInit.Value = Now
MIRCloseDate.Value = MIRDateInit.Value
AddedIndicator.Caption = "Copy"
AddedIndicator.Visible = True

I see no benefit to setting the key to 9999, then using the
form's BeforeUpdate event to undo the newrecord. Leaving
the field Null should accomplish the same thing.

Thanks Branislav, John, and Marsh for your replies,

I decided to use John's code to solve my problem since it was easy to adapt adding and removing fields in the table without having to go thru code.
However, I'm not out of the woods yet. My current code is:
=========================================================================
Private Sub btnCopy_Click()
Dim varBookMark As Variant
Dim Response
Response = MsgBox("Are you sure you want to COPY this record?", vbYesNo, "Copy Confirmation")
If Response = vbYes Then
Dim myRstOld As DAO.Recordset, myRst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT MIRMaster.* FROM MIRMaster WHERE MIRCtl = " & MIRCtl.Value
Set myRstOld = CurrentDb.OpenRecordset(strSQL)

If myRstOld.RecordCount = 1 Then
Set myRst = CurrentDb.OpenRecordset("SELECT MIRMaster.* FROM MIRMaster")

With myRst
.AddNew
For i = 0 To .Fields.Count - 1
.Fields(i).Value = myRstOld.Fields(i).Value
Next i
.Fields("MIRCtl").Value = 99998
.Update
varBookMark = .Bookmark
End With
myRst.Bookmark = varBookMark
DoCmd.RunCommand acCmdSelectRecord
MIRCtl.SetFocus
MIRDateInit.Value = Now
MIRCloseDate.Value = MIRDateInit.Value
AddedIndicator.Caption = "Copy"
AddedIndicator.Visible = True
End If
End Sub
===========================================================================

I want to display the newly copied record (should be MIRCtl = 99998) in the form. What I did was save a bookmark to what I thought was the copied
record and tried to bring the record current. When I bookmarked the record, the original "copy from" record was bookmarked, not the "copied to"
record. Both the "copied from" and "copied to" records are in the table so the real question is......

How do I set currency to the "copied to" record? Easier ways to accomplish what I'm trying to do? Examples?

Thanks to all,

Bob