Getting identity of appended record in VBA

  • Thread starter Thread starter Gary Schuldt
  • Start date Start date
G

Gary Schuldt

I'm using FrmA to display recX. RecX has an Autonumber field Xid for its
primary key.

I want to:

1. Duplicate recX

2. Save recX

3. Display recX-dup (using frmA).

I used the Command Button Wizard to get the VBA code (shown below) for Step
1, and it works fine.

However, I'm not sure how to do Steps 2 & 3. I know I could save recX by
closing frmA.

But how do I know the "identity" of recX-dup so I can reopen
frmA to display it?

Thanks.

Gary
 
The code you refer to wasn't attached. However, perhaps
this will help.

I have used a recordset on the table I am working with to
create a duplicate record. E.g.,

..AddNew
[insert values into all the fields except the XID field]
-inserting a value into any one of the other fields will
trigger insertion of the next incremental value into the
AutoNumber field. Capture that in a variable, e.g.
lngAutoNum.

Then, after all values are inserted into all fields,

..Update to save your addition to the recordset.
..Close the recordset as appropriate.

Then, assuming the form is displaying Xid, try the code:

Me.Requery
Me.Xid.SetFocus
DoCmd.FindRecord lngAutoNum...... [I don't have the all
the parameters handy...

Hope this helps.
 
Hi,

sorry for the oversight in not attaching the code! For the record (NPI),
here it is:

=====================================
Private Sub CloneRecord_Click()
On Error GoTo Err_CloneRecord_Click


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

Exit_CloneRecord_Click:
Exit Sub

Err_CloneRecord_Click:
MsgBox Err.Description
Resume Exit_CloneRecord_Click

End Sub
===========================
Since the above method doesn't make any specific fields (such as Xid)
"visible" in the code, I can't see how I can make that approach compatible
with your solution. Do you?

Otherwise, I think I will have to revise my approach and duplicate the
record by filling field by field from the current record.

I'm not a VBA coder by any means, and I was hoping for a more "idiot-proof"
solution to this problem! (I don't even know how to figure out what the 8,
2, and 5 parameters refer to in the above DoMenuItem statements.)

Thanks.

Gary

The code you refer to wasn't attached. However, perhaps
this will help.

I have used a recordset on the table I am working with to
create a duplicate record. E.g.,

.AddNew
[insert values into all the fields except the XID field]
-inserting a value into any one of the other fields will
trigger insertion of the next incremental value into the
AutoNumber field. Capture that in a variable, e.g.
lngAutoNum.

Then, after all values are inserted into all fields,

.Update to save your addition to the recordset.
.Close the recordset as appropriate.

Then, assuming the form is displaying Xid, try the code:

Me.Requery
Me.Xid.SetFocus
DoCmd.FindRecord lngAutoNum...... [I don't have the all
the parameters handy...

Hope this helps.

-----Original Message-----
I'm using FrmA to display recX. RecX has an Autonumber field Xid for its
primary key.

I want to:

1. Duplicate recX

2. Save recX

3. Display recX-dup (using frmA).

I used the Command Button Wizard to get the VBA code (shown below) for Step
1, and it works fine.

However, I'm not sure how to do Steps 2 & 3. I know I could save recX by
closing frmA.

But how do I know the "identity" of recX-dup so I can reopen
frmA to display it?

Thanks.

Gary


.
Click to expand...
Click to expand...
 
Back
Top