frmMain.txtSID is bounded to tblMain.SID. So whenever a record is to be deleted in tblMain, a message box will inform the user and upon user's response to "Yes" to go ahead and delete, then the current record with "txtSID" in frmMain is DELETED in tblMain with the corresponding "SID".
The cmdDelete button code can be found in the Code Snippet.
Now to my new request.
If a record is confirmed to be "deleted", I will appreciate a Function that would do the following if called from cmdDelete.
When user responds to a msgbox in the cmdDelete button by clicking the "Yes" to go ahead and delete the current record in frmMain.........then do the following:
1). Capture the "SID" number using the "txtSID" control that was deleted.
2). Use the captured "SID" or "txtSID" to "search" tblMain using an "SQL or sSQL" for the serial number "GROUP" of
tblMain.FDID that matches the deleted "SID or txtSID"
3). If a group of the deleted serial number (matching the deleted "SID or txtSID") is found then....
Re-order / renumber the remaining FDID member(s) in that group using required fields in tblMain
rs!FDID = Left$(rs!TestType, 1) & "-W-" & rs!CTypeID & "-" & rs!BNo & "-" & rs!LNo & "-" & rs!STypeID & "-"
4). If "no" other member in the group of the deleted FDID serial number (matching the deleted "SID or txtSID") is found then....
I am assuming that the call to re-order / renumber would be called right after user deleted an SID record.
Before user deletes the currently opened record in frmMain using the command button code shown below, we probably have the following in tblMain
If user deletes SID #2 above ... P-W-PL-201-111-DM-02 '<===== DELETED !!!
Note after re-numbering occurs then see below:
'<========= SID DELETED
3 P-W-PL-201-111-DM-03 '<===== This is expected to re-order to P-W-PL-201-111-DM-02
the new record after renumbering in tblMain FINAL records.... should be: -
'<========= SID #2 DELETED
3 P-W-PL-201-111-DM-02 '<===== New re-ordering
If a sample db is required, I'll be glad to upload one.
Private Sub btnDelete_Click()
On Error GoTo Err_btnDelete_Click
Dim intReturn As Integer
intReturn = Msgbox("Warning: You are about to delete SID " & txtSID & ". " & _
"Click Yes to delete or Cancel not to delete .", vbOKCancel)
If intReturn = vbCancel Then
If Me.Dirty Then
CurrentDb.Execute "Delete * From tblMain Where [SID] =" & Me.txtSID
Call to Re-order / Renumber can be placed here. '<=============Call to re-order / renumber here
Msgbox "Error# " & err.Number & " " & err.Description