storing a deleted record.

mothi

Registered User.
Local time
Today, 11:48
Joined
Dec 26, 2009
Messages
111
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.
 
Three things:

1. Why are you using a recordset to perform the INSERT operation?
2. Replace If Response = vbNo Then with ELSE
3. What event is this?
 
You might also clarify what it is you wish to happen. Stop the addition of the record to the delete table? Delete the child records first? Archive the child records also? Etc.
 
Hi , this is code is present onDelete event of the form .So when the user tries to delete a record he will be prompted with yes and no options . SO when he selects yes the record gets deleted and i am saving that deleted record in a table . But when the user tries to delete a record on the parent table before deleting in the child table . The record should not be saved in a table even though if he selects YES on the message prompt . So please Help me out

as an example

If message prompt = Yes and error.number = 3200 ( which is the record cannot be deleted or changed because the following table contains the related records). the record should not be saved in a table .

If the message prompt = Yes and no error then the record should be saved in a table . Please help me out.
 
An error fires when a line of code fails, it's not used as a check. If you want to check whether there's a related record in one of your tables use the DLookup function:

You didn't answer my first question about why you're using a recordset?

Also, with regards my second point, have you done that?
 
Yes i have done that . I am using the record set because it is a new table and i am using that table to store only the deleted records it is not linked to any other table . Its just a stand alone table saving the deleted record information. and I am posting the modified code here take a look at it . Thanks for replying



If Response = vbNo Then
Cancel = True
Else
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




I did not understood this part "An error fires when a line of code fails, it's not used as a check. If you want to check whether there's a related record in one of your tables use the DLookup function "

And moreover if the user by mistake tries to delete a record on the parent table before deleting that in the child table thats where i am facing trouble because if the user selects yes on the message prompt then the system prompt will come up saying that record cannot be deleted or changed because the following table includes the related records. So even upon selecting Yes on the message prompt , the record should not be saved in the table because it was not yet deleted . Please Help me out and thanks for replying .
 
Use an INSERT statement instead of a recordset. Your code will look something like this:

Code:
dim db as dao.database

set db = currentdb

If Response = vbNo Then
     Cancel = True
Else
     db.execute "INSERT INTO ..."
End If
To sort out the message, go to your Relationships, right-click on the join of the tables and select Referential Integrity and Cascade Delete.

Edit: Here's a link on the INSERT statement
http://www.techonthenet.com/sql/insert.php
 
Last edited:
Hi VBAInet, I did modified my code it is doing the same thing .

If a User by mistake tries to delete a record on the parent table before deleting that record on the child table . It should not be saved in the table .

and The problem is , that it is getting saved in a table because we are cancelling the operation if he selects NO . If select Yes , we are saving it in a table irrespective of whether the record is deleted or not .
 
I do not want to do the cascade delete . Because if the user by mistake selects yes on the record then the whole data will be lost.
 
Probably should just have a boolean field which is titled ARCHIVE and then when they "delete" a record you just mark the box true and in your normal forms and all you just have it select all that are not true. Then you still have the data but it just doesn't look like it and you don't need to move it between tables and can use a simple query to get both live and deleted data if ever necessary.
 
Hi SOS could you please explain in detail about that. Please
 
Hi SOS could you please explain in detail about that. Please

I thought I just had. :)

Okay - so in your table/tables you add a field named Archived

Then in the On Delete function you use an update query to update the selected record to Archived = True and then cancel the delete using Cancel = True

Then for any forms you use you use a QUERY where there is criteria of FALSE for the Archived field - that way only valid "non-deleted" records would show up.

Does that help?
 
Hi SOS, i can't do that , that way i will not delete any record i would just mark that but those records would be there in the table and i have to identify them by looking at archived field. No thats not what i want .

I want the record to get deleted and the deleted record should be saved in another table ( lets say delete table). And to an extent i was able to do that. But when the parent table and child table are linked together by a primary key ( ID) and when i select yes to delete a record on the parent table before deleting that record on the child . THe system will not allow you to delete that record. But since i selected Yes on the message prompt , even though the record does not really get deleted . I am saving that as a deleted record on the table . So please help me out I do not want to save the record in the table when a user tries to delete a record on the parent table even though if he selects yes on the message prompt. Please Help me out.
 
Actually it's sounding like you don't want Referential Integrity enforced. Go to your relationships, uncheck Referential Integrity and see if that message comes up. It shouldn't.
 
Hey SOS and VBAInet . I figured it out thanks anyways for replying and helping me out.

actually what iam doing is that i am using Dlookup Function ( Suggested by VBAInet) and checking whether it is still in the table or not . If it is then i am not saving that record . If its not , then iam saving that record in the table ( because that record would not be there in the table only if the deletion was successfull ,that means there are no records with that ID in the child table ) . Thanks a lot guys.
 
Actually use a DCount and just check whether it's greater than 0. If you were going to use the DLookup function then you should be testing for Null for when there are no matching records. I would use the DCount if I were you.

Good luck.
 
Help me VBAInet, Because u suggested that idea. And i going blank now.Please Help me.
 
Hmm... I thought you said you had it sorted? Structure:

Code:
dim db as dao.database

set db = currentdb

If Response = vbNo Then
     Cancel = True
Else
     If DCount() = 0 Then
         db.execute "INSERT INTO ..."
     Else
         Cancel = True
         Msgbox "Record not saved"
     End If
End If

Lookup the Dcount function.
 
Dim sql As String
sql = Nz(DLookup("part_cin", "tbl_Participant", "part_cin ='" + Me.frm_part_cin + "'"))
Dim db As dao.Database
Dim tbl_delete As dao.Recordset
Dim Response As Integer
Dim sum As String
Set db = CurrentDb()
Set tbl_delete = db.OpenRecordset("tbl_delete")
Response = MsgBox("Do you want to delete this record ? ", vbYesNo)
If Response = vbYes Then
If sql <> Forms!frm_Participant!part_cin 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
Else
Cancel = True
End If
End If
If Response = vbNo Then
Cancel = True
End If



Please a take a look at it . it is working actually, Could you please correct me if i had done any thing wrong in this. THank You for replying me VBAINET
 
It just seems that what was advised in post #2 (points 1 & 2) was ignored. You adhered to point 2 and amended your code in post #4 but now again doing the same thing, using the vbNo in a different block.

What you've written doesn't even conform to the structure I gave you in my last post, especially with respect to using the INSERT statement.
 

Users who are viewing this thread

Back
Top Bottom