Using counters

[FuzzyBear9]

Hi,

I am implementing a database in Access 97 and have a problem. The database holds contact information for different companies. Each company has more than one contact. Is it possible to maintain a counter for the number of contacts in a given company? If a contact record is deleted from a given company, than the remaining contacts should be re-numbered for that company. I would like to display a field on the form that shows that contacts counter value, ie. 3.

What type of query do I need to use? And where would I place it? (In 'On current' under 'Form current'?) I really am at a loss as to where to begin.

Any help would be greatly appreciated.

 

[crpjaviman]

Hi Fuzzybear9,

There is one way that I have used in reports and forms:
Make a text box with no control source. Input "=1" in the control source line.
This will give you a running counter for every detail line in the form. This will also recount the contacts every time you open the form.

I hope this helps or at least gives you an idea.

crpjaviman

 

[jebry]

Hi!

This count can be obtained using DCount("ContactID",
"ContactTable", "CompanyName = '" & Me!txtCompanyName & "'&quot
This will get the count you need. Since you can get this programmatically, it shouldn't be stored anywhere. On the renumbering problem when a contact is deleted, if the records will only be deleted through a form then you can open a recordset in code and make the appropriate changes there. If the user will be accessing the table directly, then, on closing the application, you can compare DMax and DCount for each company and, if they are different, open a recordset for that company, find the missing value and make the necessary changes.

hth
Jeff Bridgham
bridgham@purdue.edu