dCount referencing a control on another form

[JonathanT]

I'm trying to use the DCount function to count the number or records in a table that have a specific value in a field. I want to get the value from a combo box on another form. The answer I'm getting is a count of all the records in the table, not just a count of the records of the ones matching the value in the combo box. If I hard code a value in my expression it returns the correct count, however when I try to get the value from a combo box the count is all the records in my table.



Here is the syntax of what I'm using.

=DCount("[ID]","[Spending]","[Forms]![Navigation]![NavVendor]")

Any Ideas??

[Bob Fitz]

What is the name of the field, in "Spending", that will have the same value as "[Forms]![Navigation]![NavVendor]" and what is its' data type, Text or Numeric.

[JonathanT]

The field in Spending is "Vendor" and the data type is short text.

[Bob Fitz]

Try:

=DCount("[ID]","[Spending]","Vendor = '" & [Forms]![Navigation]![NavVendor] & "'")

Note the use of single apostrophes which I have shown in red.

[June7]

Are code and combobox located on forms used as TargetObjects on same navigation subform? If so, only one TargetObject at a time will be available and code will fail.

[JonathanT]

This code returned zero records as a count
=DCount("[ID]","[Spending]","Vendor = '" & [Forms]![Navigation]![NavVendor] & "'")

[JonathanT]

The combo box and text box with the code are on 2 separate forms. Both forms are open. No mainform/subforms exist. DB Attached

[davegri]

This worked for me:

=DCount("[ID]","[Spending]","Vendor ='" & [Forms]![Navigation]![NavVendor].[column](1) & "'")

[Bob Fitz]

JohnathanT

I have attached 2 Copies of your db.

Spending1 is a corrected version of the db that you posted.
The expression I gave you needed to be changed to:
=DCount("[ID]","[Spending]","Vendor ='" & [Forms]![Navigation]![NavVendor].[column](1) & "'")
as was shown by davegri
The addition of [column](1) was required because the "Vendor" field in the spending table was, as you stated, a text field. However, any reference to the "Vendor" combo box returns a number because it uses the Vendor table for its data. That table has two data columns. First is unique identifier for the record (Primary Key). Second is the name of the vendor. The combo box holds both columns but the first is hidden because it means nothing to the user but although the user selects a name it is the numeric value that is saved. [column](1) is needed to reference the second column, which has the text name value. The column numbers start with 0 which is why the second is 1.

In Spending2 I have changed the structure of the spending table by removing the text field "Vendor" and replacing it with a numeric field "VendorIDfk" wich now holds the Primary Key value from the Vendors table. IMHO that's the better way to do it. I don't see the point of having a Primary Key in the Vendors table and then using some other field in a related table. I would recommend making the same kind of changes regarding "Categories". With those changes to the spending table, we now need the following expression for DCount():
=DCount("[ID]","[Spending]","VendorIDfk = " & [Forms]![Navigation]![NavVendor])
I also changed the name of the primary key field in Vendors from ID which is a poor choice of name, especially if you use it other tables, to VendorID
The apostrophes used as delimiters are no longer needed as the field and combo box are both numeric values.

BTY I have also added several vendor names to the vendor table that had been used in the spending table but were not in the vendors table and have created a one to many relationship for them in the Relationships window. I have also enforced Referential Integrity between them.

Hope this helps. Post back if you have any questions.

[JonathanT]

Bob and Davegri, Thanks for the help. Code works perfectly. Bob, I'll take a look at your suggestions for my tables

[Bob Fitz]

Bob and Davegri, Thanks for the help. Code works perfectly. Bob, I'll take a look at your suggestions for my tables

Please do. Post back if you have any questions after viewing them