Record Count based on Form control value

[Monterey_Manzer]

Hello Everyone,

I know this shouldn't be too difficult, I've just been so mired in other tasks that I feel my Access skills leaking out of my head. Therefore feel free to let me know how simple this is and how I should have figured it out already.

The general situation:
I have a form where my users enter information about customers into a table (pretty straight forward, right?). Each customer has a unique ID by which we identify them, however they may have multiple records on this table if there are multiple issues.

Specific Issue:
I want to construct a record count textbox or combobox (whichever is easier) on the form that will display the number of records a customer has in the table. I want the criteria to the value in the ID textbox. So basically, there may be 30 records in the table but customer 5 only has 2 records. When I navigate in the form to either of those two records I want the record count to display 2. When I am entering a new record, once I've entered the ID and navigated to another control, the record count should update to reflect the creation of the new record.



I've tried simple stuff like =Count([ProbTracker].[ID] = [ID]) for a text box data source. It always gives me the total number of records.

I eventually want to use this criteria ([ProbTracker].[ID]=[Form].[ID]) in a SQL statement so I can generate a small report that would show each record specific to that customer. I figure I'll use some DAO or ADO recordset code to help me out there.

Any and all eventual help is much appreciated!

[pbaldy]

Simplest is probably a DCount() with the appropriate criteria:

DLookup Usage Samples

[orange]

Have you considered having a Customers table and CustomerIssues Table?

[Monterey_Manzer]

I am sorry if I was unclear, I do have a separate Customers and CustomerIssues table. In fact I have multiple Customer Issues tables due to all the different fields that apply to only specific issue types. This is a small aspect of a larger system.

With respect to DCount, I thought that was only for unique values. I'm looking for repeats, including the record I am on. I have the current expression in the Control Source "=DCount("SMARTID","ProbTracker","SMARTID=" & [Forms]![ProbTrackfrm]![SMARTID]) " and it returns a 0 regardless of the number of records that actually match the SMARTID on the form.

[pbaldy]

This should work if SmartID is the field identifying the customer:

=DCount("*","ProbTracker","SMARTID=" & [Forms]![ProbTrackfrm]![SMARTID])

[Monterey_Manzer]

Unfortunately in this case the operative word is "should". The SMARTID is the field that identifies the customer but the formula you gave me, when used as the data source for a text box, returns a 0 for all instances. This is true no matter how many records a customer has in the table ("ProbTracker"). Also when I try to use the formula as part of a combo box query I still get 0 for all SMARTIDs. Perhaps we could use a DAO or ADO recordset with an appropriate SQL statement?

[pbaldy]

Sure. Can you post the db here?

[Monterey_Manzer]

Sadly, both the db size and the sheer volume of protected information make posting the db frighteningly prohibitive.