storing a deleted record.

[mothi]

Oh Alright , I will do that. But i was trying to ask you that whether the core concept is right ?

 

[vbaInet]

The core concepts have already been layed out to you. Follow what has already been presented and please put your code in code tags.

 

[mothi]

Private Sub Form_Delete(Cancel As Integer)

Dim db As dao.Database
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 = No Then

Cancel = True

Else

If DCount("part_cin", "tbl_Participant", "part_cin ='" + Me.frm_part_cin + "'") = 0 Then


db.Execute "Insert into tbl_delete(part_table,part_del_participant) values ('" &

Me.RecordSource & "' ,'" & Me.frm_part_cin & "')"

Else

Cancel = True

End If
End If


It is not working now, I need serious help now.

Case 1 The problem is when a user tries to delete a participant and participant does not have any related records in the linked tables . I am storing primary key informatino in a separate table and then deleting a participant.


Case 2 when a user tries to delete a participant but the same participants has related records in the linked tables. Even then the user selects yes on delete message prompt then that record should not be saved in the table

 

[mothi]

This is ONDELETE Event of the form .

 

[mothi]

VBAInet, Help me Help Help Help.

 

[vbaInet]

What isn't working? Does it throw an error? What line is highlighted?

 

[mothi]

I am in desperate need of Help.

 

[mothi]

Dcount is 1 actually So its cancelling the operation .

 

[SOS]

Dcount is 1 actually So its cancelling the operation .

Shouldn't it be =1 instead of =0 in your code? By it being 0 you are essentially saying to Access if the record doesn't exist in the table copy that non-existent record to the tbl_delete table, which doesn't make sense.

 

[mothi]

Yes you are right , this way i can be able to satify the CASE 1

But in the CASE 2, the deletion would be definitely unsuccessfull because it would have related records in the linked tables. But the problem is that the record is getting stored in the table . I do not want the record to get stored even if the user selects yes in CASE 2

 

[mothi]

And Count of the each Participant would be only 1 in this table . Because the ID will be unique and Count is based on ID .

 

[vbaInet]

Case 1 The problem is when a user tries to delete a participant and participant does not have any related records in the linked tables . I am storing primary key informatino in a separate table and then deleting a participant.


Case 2 when a user tries to delete a participant but the same participants has related records in the linked tables. Even then the user selects yes on delete message prompt then that record should not be saved in the table

Both cases are contradictory.

 

[mothi]

Case 2 when a user tries to delete a participant but if the participant has related records in the linked tables. Even then the user selects yes on delete message prompt then that record should not be saved in the table


I am EXTREMELY SORRY , i am not talking about the same participant in CASE 2 ( a totally different Participant). SORRY Once again.

 

[vbaInet]

Could you possibly rephrase Case 2 so we have a clear picture.

 

[mothi]

Case 2

If the User By mistake tries to delete a participant on the form and selects yes on the delete message prompt. But the deletion would be unsuccessful because the participant has got records in the linked tables as well. The participant won't get deleted because the user is trying to delete him in the parent table. So even if the user selects YES on the message prompt, as we know the deletion will be unsuccessful, and thats right, but the table is storing the information and identifying it as a deleted record. When in reality, it is not deleted

 

[vbaInet]

Use another DCount to count the linked records and Cancel if it is > 0.

Put the DCount here:

dim db as dao.database

set db = currentdb

If Response = vbNo Then
     Cancel = True
Else
     If [COLOR=Red][B]DCount(Count linked) > 0[/B][/COLOR] Then
         Cancel = true
     Else
         If DCount() = 1 Then
             db.execute "INSERT INTO ..."
         End If
     end if
End If

 

[mothi]

THanks A lotlottttttttttttttttttttttttttttttttttttt. VBAINET. you are truely , genuinely a friend. I mean it . Thanks a lot

 

[mothi]

Dim db As dao.Database
Dim Response As Integer
Set db = CurrentDb()


Response = MsgBox("Do you want to delete this record ? ", vbYesNo)
If Response = No Then
Cancel = True
Else
If DCount("part_nod_cin", "tbl_Participant_NOD", "part_nod_cin= '" + Me.frm_part_cin + "'") > 0 Then
Cancel = True
Else
If DCount("part_cin", "tbl_Participant", "part_cin ='" + Me.frm_part_cin + "'") = 1 Then
db.Execute "Insert into tbl_delete(part_table,part_del_participant) values ('" & Me.RecordSource & "' ,'" & Me.frm_part_cin & "')"
End If
End If
End If


participant_NOD ( Linked table) checking whether that table has got any records of that participant.

 

[mothi]

The Code is on the ONDelete Event of the Form.

The System Prompts i have disabled them Docmd.Setwarnings False in the BEforeDeleteConfirm Event of the Form. because I created my own prompt in the code.

It Should work right?

 

[mothi]

i really like to ask you could you recommend me a VBA book or anything where i could learn the VBA and get even more grip on that subject.