Hi , I have the code which stores the deleted records in a separate table . When the user tries to delete a record , upon select yes on the message box the record gets deleted and it is stored in a separate table .
But there is a glitch , I will explain it by taking 2 tables ( tbl_customer and tbl_dept)
tbl_dept = Parent Table
tbl_customer = child table
Both tables have ( customer_id as the primary key ) So customer_id is the foreign key in the tbl_customer and both the tables are linked by customer_id
If i try to delete a record on the tbl_dept and when the user selects "Yes" on the message box the record is getting stored in the delete table . But in reality, the system prompts him that one or more records are available in the tbl_customer so he cannot delete that record in tbl_dept. So in this case, even though the user selects yes , the record should not be saved in the delete table because it is not yet deleted.
the code i have is
Dim db As dao.Database
Dim tbl_delete As dao.Recordset
Dim Response As Integer
Set db = CurrentDb()
Set tbl_delete = db.OpenRecordset("tbl_delete")
Response = MsgBox("Do you want to delete this record ? ", vbYesNo)
If Response = vbYes Then
With tbl_delete
.AddNew
!part_table = Me.RecordSource
!part_del_participant = Me.frm_part_cin
!part_update_user_id = CurrentUser()
!part_update_date = Date
.Update
End With
End If
If Response = vbNo Then
Cancel = True
End If
So please help me out when the user selects yes in the case of trying to delete a record in the parent table before deleting that in the child table . The record should not be saved in the table even though he selects yes because in reality, the system prompts him that one or more records are available in the child table . Please Help me out.
But there is a glitch , I will explain it by taking 2 tables ( tbl_customer and tbl_dept)
tbl_dept = Parent Table
tbl_customer = child table
Both tables have ( customer_id as the primary key ) So customer_id is the foreign key in the tbl_customer and both the tables are linked by customer_id
If i try to delete a record on the tbl_dept and when the user selects "Yes" on the message box the record is getting stored in the delete table . But in reality, the system prompts him that one or more records are available in the tbl_customer so he cannot delete that record in tbl_dept. So in this case, even though the user selects yes , the record should not be saved in the delete table because it is not yet deleted.
the code i have is
Dim db As dao.Database
Dim tbl_delete As dao.Recordset
Dim Response As Integer
Set db = CurrentDb()
Set tbl_delete = db.OpenRecordset("tbl_delete")
Response = MsgBox("Do you want to delete this record ? ", vbYesNo)
If Response = vbYes Then
With tbl_delete
.AddNew
!part_table = Me.RecordSource
!part_del_participant = Me.frm_part_cin
!part_update_user_id = CurrentUser()
!part_update_date = Date
.Update
End With
End If
If Response = vbNo Then
Cancel = True
End If
So please help me out when the user selects yes in the case of trying to delete a record in the parent table before deleting that in the child table . The record should not be saved in the table even though he selects yes because in reality, the system prompts him that one or more records are available in the child table . Please Help me out.