storing a deleted record.

[vbaInet]

You could:

1. Turn the Warnings off in the Before_Delete_Confirm event
2. Turn it back on in the After_Delete_Confirm event

 

[mothi]

One more thing is that In the COde I pasted below i am as of now, only checking for one table what if my parent table has got more linked tables.

Instead of creating Dcount function for each linked table . Can i just have single line of code which checks whether that particular participant is having records or not in the linked tables

 

[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

 

[vbaInet]

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.

These are books recommended by one the forum's MVPs.

http://www.btabdevelopment.com/main/BooksIRecommend/tabid/101/Default.aspx

Although I haven't read any of them, I would imagine they are pretty good.

Also, go back to page 1 of your thread and look at any of the posts from SOS, in his signature you will find "Function X". Have a look at that.

 

[mothi]

One more thing is that In the COde I pasted below i am as of now, only checking for one table what if my parent table has got more linked tables.

Instead of creating Dcount function for each linked table . Can i just have single line of code which checks whether that particular participant is having records or not in the linked tables

 

[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

 

[vbaInet]

One more thing is that In the COde I pasted below i am as of now, only checking for one table what if my parent table has got more linked tables.

Instead of creating Dcount function for each linked table . Can i just have single line of code which checks whether that particular participant is having records or not in the linked tables

I did see your comment of before and was about to reply.

How many tables are we talking about? Can a query to include all the records from all the tables not work? Or are they not related?

 

[mothi]

Parent Table is connected to around 6 to 8 tables .

But I Guess when you create a query having all the common records. I see what ur saying . But you know i have to repeat the same logic for children table as well . So, I have to create queries for each table .

 

[vbaInet]

If your Parent table is branching out to each one of these tables and the Parent table contains the ParticipantID then create a query to pull just one field from each of the 6 tables, then use that query in the DCount.

 

[mothi]

I Did that Just now But You know what i explain it thru example

Lets say table 1 ( Parent table ) has got records in table 2 ( Child) and onre more link to table 3 ( child )

So it is not returning the total number of rows because if the table 1 and table 2 has got common rows ( lets say 230 commmon records) and table 1 and table 3has 120 common rows then on the whole it is returning 120 rows .

 

[mothi]

Thats because i asm asking query to return only the common records between the tables so table 1 and table 2 has 240 common records ( lets that includes ZZ12345Z record) but the table 1 and table 3 ( has 100 common records but it does not include zz12345z because table 3 does not have that record in the table ) then it would only return the common one's . So it would further filter and return the common records among all the linked tables.

 

[vbaInet]

I still don't get it. If you were unsuccessful in the query approach because of the way your tables were designed then you would have to count each table and check the summed result of all.

Dim myVar as long

myvar = DCount(table [B]1[/B])
myvar = myvar + Dcount(table [B]2[/B])
myvar = myvar + Dcount(table [B]3[/B])
... and so on.
If myvar > 0 then

 

[mothi]

Yes. I guess thats the only. But from the bottom of my heart, i really appreciate all the efforts you have put in. Thanks a lot and Take Care.

 

[vbaInet]

You're most welcome.

Happy coding!