Count records in another table

[jonnyuk3]

Hi,

I have three tables, customer, interest and link_customer_interest. This is to allow for many to many relationships.

On the interest form i want to be able to count the records in the link_customer_interest table that are relevant to the current interest displayed on the interest form and display this value in a text box.

Any ideas how i do this.

Thanks in advance,



jonnyuk3

[maximus]

you can use Dcount:

My assumptions:
1) You have Interest_Id which is the unique number of each interest in your table in your link_customer_interest table.
2) You have a primary key in your link_customer_interest table which I have refered to in my expression as [link_cutomer_interest_Id]
3) You have the interest_id field on your form, recordsource interest table.


=iif(isNull(DCount("[link_cutomer_interest_Id]","link_customer_interest","[Interest_ID]=" & Forms!Name of the Form!Interest_ID)),0,DCount("[Link_cutomer_interest_Id]","link_customer_interest","[Interest_ID]=" & Forms!Name of the Form!Interest_ID))

Please cjange the name of the Fields, Controls and Form with the ones in yor database.

[jonnyuk3]

Thanks for the reply Maximus, excuse my ignorance but where do i use this code?

thanks,

jonnyuk3

Got it, thanks for your help Maximus.
I put the code on the onCurrent event of the form, works like a charm.