>You could do a background renumber on every Delete (there is an event
for that).
Thx Bas ! To be honest it had occurred to me along the way, but I
wanted to bullet-proof the renumbering snippet via a manual button on
the form before tying it into my delete event. I have done that finally
& it sems to work ... just 1 small glitch.
The Glitch:
---------------
If I delete a few or single records the re-numbering works fine ... but
if I delete all records, then the renumbering does not occur. On close
examination I found in the
Sub Cmd_ReNumber_Click, the recordsetclone id sort of disconnected from
the recordset. After deleting ALL records, now even if i add several
records, the condition (rst.BOF And rst.EOF) tests TRUE which leads me
to believe the recordset is getting populated whereas the
recordsetclone has been disconnected from the original recordset. Is
there any way of tying them back together again ?
My code is posted below.
Private Sub Cmd_ReNumber_Click() 'Code Snippet Courtesy of Wayne
Morgan
Dim rst As DAO.Recordset, lngNumber As Long
Set rst = Me.RecordsetClone
MsgBox rst.BOF & " " & rst.EOF
If Not (rst.BOF And rst.EOF) Then
With rst
.MoveFirst
lngNumber = 0
Do Until .EOF
lngNumber = lngNumber + 1
.Edit
![Sub_Tran_No] = lngNumber
.Update
.MoveNext
Loop
.Close
End With
End If
Set rst = Nothing
If Me.Recordset.RecordCount = 0 Then
Me.Sub_Tran_No.DefaultValue = 1
Else
Me.Sub_Tran_No.DefaultValue = DMax("sub_tran_no", "JVTable") +
1
End If
Me.Refresh
Screen.PreviousControl.SetFocus
End Sub
Private Sub Cmd_Delete_Click()
On Error GoTo Err_Cmd_Delete_Click
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings True
Me.DrTotal = DSum("DEBIT", "JVTable") ' After deleting a Record
Totals shd be re-calculated
Me.CrTotal = DSum("CREDIT", "JVTable")
Screen.PreviousControl.SetFocus
Call Cmd_ReNumber_Click 'Re-Number the sub_tran_no field if any
record is DELETED
Exit_Cmd_Delete_Click:
Exit Sub
Err_Cmd_Delete_Click:
MsgBox Err.Description
Resume Exit_Cmd_Delete_Click
End Sub
Private Sub Form_Current()
Dim msg, msg1, msg2, msg3 As String
If Me.NewRecord = True And Me.Sub_Tran_No >= 254 Then
'MsgBox "New Record !"
msg1 = "MAXIMUM ROWS/TRANSACTIONS ALLOWED ARE 255 !" & Chr(13)
& Chr(13)
msg2 = "Please Delete Some Rows !"
MsgBox msg1 & msg2, vbOKOnly + vbCritical, "Error !"
SendKeys "^{PGUP}", True
Screen.PreviousControl.SetFocus
End If
If Me.Recordset.RecordCount = 0 Then
'MsgBox "Empty Table !"
Me.Sub_Tran_No.DefaultValue = 1
Else
Me.Sub_Tran_No.DefaultValue = DMax("sub_tran_no", "JVTable") +
1
End If
End Sub
Wayne, thank you for the code.
Best Rgds,
Prakash.
