renumbering sequence when record(s) removed

[Guest]

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

 

[Guest]

update [Concomitant Medications] set [MedNumber] = [MedNumber] - 1
where [MedNumber] > Me.[MedNumber];

should be the SQL to renumber for you. This will decrement the MedNumber for
every Medication above the one that was deleted.

Presumably you can either add another DoCmd.RunCommand with this as the SQL
or string all of this together into one SQL command (I'm less sure about this
option; I just tested the SQL real quickly and have never used RunCommand
like this before.)

Can create the update using a Criteria of '> Me.[MedNumber]' and Update To
of '[MedNumber] - 1' in the QEB.

 

[Guest]

i would really like to try this out and see it work. i will get back w/ you
if warranted.

thanks lots for the helpful code.

-ted

update [Concomitant Medications] set [MedNumber] = [MedNumber] - 1
where [MedNumber] > Me.[MedNumber];

should be the SQL to renumber for you. This will decrement the MedNumber for
every Medication above the one that was deleted.

Presumably you can either add another DoCmd.RunCommand with this as the SQL
or string all of this together into one SQL command (I'm less sure about this
option; I just tested the SQL real quickly and have never used RunCommand
like this before.)

Can create the update using a Criteria of '> Me.[MedNumber]' and Update To
of '[MedNumber] - 1' in the QEB.

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