Using DCount function in an unbound control (Acess ADP)

[rluciano]

Here's the situation. I have the following

Form Name: NEW_CLIENT
Combo Box Name: Supplier List
Unbound Control 1: Supplier No
Unbound Control2: Total Clients

When I select the supplier name from the list, a macro sets the value of Supplier No to the corresponding Id.(Supplier Id from the table, datatype is numeric. Example = 7)

The DCOUNT function is in the control source of Total Clients. The Form is open and no fields on the form are bound to any fields in a table or view.

The expression is:

=DCount("[Client Id]",
"Client Master File",
"[Supplier Id]=Forms![NEW_CLIENT]![Supplier No]")

This results in #error displayed in the field.
I also tried the expression in a macro used in the after update section of the supplier combo box and it errors as well.

Any help on why this errors is appreciated. I am sure the field names and table name is correct in the expression.

Note-The expression works if I manually enter the supplier id in the criteria of the DCount function like:

=DCount("[Client Id]",
"Client Master File",
"[Supplier Id]=7")

This gives me the proper count of clients whos supplier id is 7.

 

[ESquared]

Repost in ADP forum please.

But I'll give you a hint... there are threads already in the ADP forum which are answering (by me) this exact question... like, fifth from the top at this moment. It even has "dlookup" in the title of the question.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[rluciano]

Thanks for that Esquared, I saw your answer and as you know works perfectly.