i have a form called "Concomitant Medications" which has a PK comprised of
"Patient Number" and "Med Number" in the underlying table of my a2k mdb file.

Med number (MN) is automatically incremented for any given patient number
(PN) when a new medication is entered by the user.

it is conceivable a record (e.g. # 4) might be mis-entered and requiring a
removal in which case the sequence might appear to the user as 1,2,3,5,6,7
when only 6 medications remained.

my code to delete a record is as under

Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY DELETE THIS
RECORD of this patient's (#" & Me![Patient Number] & ") !", vbCritical +
vbOKCancel + vbDefaultButton2, "Critical") Then
     Me.AllowDeletions = True
    ' RunCommand acCmdDeleteRecord
     Me.Undo
     DoCmd.RunSQL "DELETE * from [Concomitant Medications] where [Patient
Number] = " _
     & Me.Patient_Number & " AND [Med Number] = " & Me.[MedNumber] & " ; "
End If
Exit_DeleteRecord_Click:
   Me.AllowDeletions = False
   Exit Sub

Err_DeleteRecord_Click:
   'Ignore "error" if user cancels delete; otherwise
   ' show the error message.
   If Err.Number <> 2501 Then
       MsgBox Err.description
   End If
   Resume Exit_DeleteRecord_Click
End Sub

and my question's got to do with re-numbering the sequence for the affected
PN via vba or something not requiring the user's intervention.

any help'd be appreciated.

-ted