Code (1 Viewer)

[mission2java_78]

I have a subform (many relationship) with records that are numbered in ascending order. There are times when I need to renumber all of the records because a record is deleted. I tried the following code:

Private Sub cmdDeleteShift_Click()
On Error GoTo Error_Handler

Dim db As Database
Dim rs As Recordset
Dim lIndex As Long
Dim rs2 As Recordset
    
lIndex = 1


Set db = CurrentDb

Set rs2 = db.OpenRecordset("SELECT * FROM tblShifts WHERE ShiftID= " & Me.ShiftID.Value)

Set rs = db.OpenRecordset("SELECT * FROM tblShifts WHERE RecordID= " & Forms!frmMain!RecordID & " ORDER By ShiftID")

rs2.Delete

If rs.BOF Then
 'do nothing
Else
 While Not rs.EOF
    rs.Edit
    rs("ShiftNumber").Value = lIndex
    lIndex = lIndex + 1
    rs.Update
    rs.MoveNext
 Wend
End If

Done:
Set rs = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
MsgBox Err.Description, vbCritical, "Error #: " & Err.Number
Resume Done

End Sub

But sometimes this does not work..for say a brand new record...sometimes I get no Record error...and other times I get you and another user are modyfing this record cannot delete.

All I want to do is delete the current record the user is looking at if they press this button and renumber all of the records where the RecordID from the main form is equal to the recordID from the subform.

Thanks,
JOn

 

[WitchCraft]

I'd say your problem would be fixed if you just deleted all the records like this :

button_action

I assume you're positionned on the record to delete?

dim rs as recordset
set rs db.OpenRecordset("SELECT * FROM tblShifts ORDER By ShiftID")

rs.findfirst "recordId="Forms!frmMain!RecordID

if not rs.notmatch then
rs.delete
endif

While Not rs.EOF
rs.Edit
rs("ShiftNumber").Value = rs("ShiftNumber").Value -1
rs.Update
rs.MoveNext
Wend