Count in a form

[GoatieEddie]

Having a bit of a bad brain day!

I am creating a form with a combo box at the top that refers to various managers in the firm. I will then have various text boxes showing data relevant to that manager taken from various tables. My first requirement where I am already falling over is to count the number of appearances in table1 of the managers name (ie the number of clients associated to that manager). However, my =count([Customers]) just shows the total customers and not those that are linked to the particular manager. I can find nowhere to put in the child/master field links.

Really stupid and basic question I know but one too many beers last night is clouding my ability to do anything meaningful today!

Any pointers greatfully received!
Thanks,
GE

 

[ZmrAbdulla]

Try "DLookUp" with a criteria.
See more here..

http://www.mvps.org/access/general/gen0018.htm

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.

 

[GoatieEddie]

Thanks for the pointer but I can't work out how to use it to count. Can you help a bit further?

 

[ZmrAbdulla]

I have made a mistake.. Sorry.. It should be Dcount with Criteria. Code for AfterUpdate Event of the ComboBox
Here is a sample of that

Me.MyTextBox = DCount("*", "TableName", "ManagerName=" & "'" & [Combo2] & "'")

or if you are connected the Manager/Customer tables with ManagerID then you will have to change the

Me.MyTextBox = DCount("*", "TableName", "ManagerID=" & [Combo2])

you may need to change it to something like this if the combo is showing the managername and combobound column is managerID

Me.MyTextBox = DCount("*", "TableName", "ManagerID=" & [Combo2].Column(0))

Hope this helps

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.

 

[GoatieEddie]

Thanks. When I do it as you suggest, I get an error box saying "Microsoft Access cannat find the macro "Me"".
What am i doing wrong?

 

[GoatieEddie]

Ignore the last comment. Put it in a module and now it kind of works. But it is returning zero when I know that isn't true.

 

[ZmrAbdulla]

If you are adding this code directly into the Controlsource of the textbox in the property window then you need only the part starts from "=". Your code will look like

= DCount("*", "TableName", "ManagerName=" & "'" & [Combo2] & "'")

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.