On Form, Save Record and Create New Record based on the change of one control

[jcamrud]

Hello,

I have a MS Access 2003 database that tracks radio system equipment and
maintenance. The specifications of the equipment will almost always remain
the same but the equipment can be moved from site to site. Say a radio site
has base radio B that is in need of repair. The radio site requires a
working radio so we temporarily place base radio C at the site while radio B
is being repaired. The change may also be permanent if radio B is unfixable.
When radio equipment is moved from one site to another, I want to save the
existing record with the existing site name, copy that same record to a new
record with the new site name. I have it all working with the below code but
the RadioSiteName on the existing record does not retain the existing
RadioSiteName. The existing and new records have the new RadioSiteName.
Please help. Thank you in advance.

Private Sub RadioSiteName_BeforeUpdate(Cancel As Integer)
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = True
End If


'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!RadioSiteName = Me.RadioSiteName.Column(0)
!RadioCode = Me.RadioCode.Column(0)
!RadioUnit = Me.RadioUnit.Column(0)
!SerialNum = Me.TxtSerialNum
!PropNum = Me.TxtPropNum
!EquipManufacturer = Me.EquipManufacturer.Column(0)
!EquipModelNum = Me.EquipModelNum
!SoftwareVer = Me.SoftwareVer
!GateAccess = Me.GateAccess
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !RecordNum

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If