Increment number not using autoincrement

[Guest]

I have a database in which I want to autonumber a Field based on the number
of occurances.

Say I have a database that holds calls for a client. (Table "CALLS"). This
database has a relationship to another table called "CLIENT". When I add a
new record to "CALLS" I want to autoincrement a number field (NUMCALLS) based
on the number of records allready stored in "CALLS" for the current client
for which I am adding a record.

It is like creating a Combo Box display control field with a row source
selection that looks like: "SELECT COUNT(*) + 1 FROM this.table WHERE
CLIENT.ID = CLIENT.ID"
Of course the above example doesn't work, otherwise I wouldn't ask this
question.

Or do I need to create a field in the CLIENT table that holds a count of
entries in CALLS and use that?

I hope this question makes sense.


Thanks,
Ray

 

[Guest]

It sounds like the number of calls should be a calculated value, and not
stored in a field in the CLIENT table (I believe that's what you're
proposing). If you base a text box or other control on the SELECT statement
like the one you proposed, you don't have to update any fields in the CLIENT
table. Running the query would always give you an up-to-date value for the
number of calls, and relationally it's the best way to go.

 

[Guest]

Ray,

I assume you are doing this work in a form, not in your tables. If so, do
you lookup the client before you enter the new "call"? If so, then I assume
you have some sort of subform for the "Calls" info to be displayed within the
Clients form.

If that is the case, then in the Calls subform, you can add some code in the
subforms current event. Something like:

Private sub Form_Current

If me.NewRecord then
me.txt_ClientID = me.parent.txt_ClientID
me.txt_CallNum = DCOUNT("ClientID", _
"tbl_Calls", _
"[ClientID] = " &
me.txt_ClientID) + 1
Endif

End Sub

This will determine whether the current record is new, and if it is, it will
enter the clientID and the number of previous calls (plus 1) for this client
in the txt_Calls textbox.

HTH
Dale

 

[Guest]

In the contorl source for the field:
If ClientID is numeric:
=DCount("*", "CallTable", "[ClientID] = " & [ClientID])

If ClientID is text:
=DCount("*", "CallTable", "[ClientID] = '" & [ClientID] * "'")