Increment number not using autoincrement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ray V - 29 Oct 2007 21:32 GMT

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

Reply to this Message

mray - 29 Oct 2007 23:54 GMT

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.

> I have a database in which I want to autonumber a Field based on the number
> of occurances.
[quoted text clipped - 18 lines]
> Thanks,
> Ray

Reply to this Message

Dale Fye - 30 Oct 2007 17:25 GMT

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

Signature

Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

> I have a database in which I want to autonumber a Field based on the number
> of occurances.
[quoted text clipped - 18 lines]
> Thanks,
> Ray

Reply to this Message

Klatuu - 30 Oct 2007 19:05 GMT

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

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

Signature

Dave Hargis, Microsoft Access MVP

> I have a database in which I want to autonumber a Field based on the number
> of occurances.
[quoted text clipped - 18 lines]
> Thanks,
> Ray

Reply to this Message