AutoNumber -- block of numbers for ea user -- maxumum auto number

[JR]

I know, from Access help and other posts to this community, how to generate
the start numbers in the block, but not how to establish a maximum number.
So the question is: how do I establish a maxumum autonumber -- AND then,
how can I modify it if I have to expand an individual's block (give them more
numbers to use)?

I am designing a small office action tracker data base (Access 2003). One
master table to be updated when users (each with their own data base on their
hard drive, linked to the master table on a virutal server) use a form to add
or modify a record to their individual data base.
I want to assign each user a "block" of numbers -- say from 1 - 999; 1000 -
1999; ...8000-8999 as a way of tracking spefcific actions tied to them when
quering the master.

This may not be necessary as I can immagine a query by user name being
almost, if not equally, as useful, so you may be able to talk me out of this
concept. But it (using blocks of numbers) is a small part of corporate
culture in other, larger applications.

 

[Douglas J. Steele]

There's nothing in Access that allows you to set the maximum value for an
Autonumber, but then I can't see why you'd want one. Autonumbers cannot be
controlled: you can't have it generate one number for user A and a different
number for user B.

In fact, should you ever have a need to replicate your database, you'll find
that the Autonumber field will no longer be sequential, but instead will be
a random number. For this reason, no meaning should ever be assigned to the
value of the Autonumber field. (In fact, it's common never to show its value
to the users)

See http://www.mvps.org/access/api/api0008.htm at "The Access Web" for code
to give you the current user's Network ID. You can set the "CreatedBy" field
in the table to that value in the form's BeforeUpdate event.

 

[Valentín Playá]

Autonumbers can not be use as you want. Autonumbers are generated for
the whole table and is not secuential, some numbers may be missing.

You need to add code to do what you want. If you use code to insert a
record to the "master table" you may query the table to find the
highest number for the user and add one before inserting the record.

Regards,

Valentín Playá
Sonotronic S.A.
Madrid, Spain
*************************************************************************

 

[JR]

Yes I can start each user at a different number -- I have already done so.
This part is even in Access help under "change the starting value of an
AutoNumber field (MDB).

 

[Tony Toews [MVP]]

I want to assign each user a "block" of numbers -- say from 1 - 999; 1000 -
1999; ...8000-8999 as a way of tracking spefcific actions tied to them when
quering the master.

How To Implement Multi-user Custom Counters in DAO 3.5
http://support.microsoft.com/default.aspx?scid=kb;en-us;191253
How To Implement Multiuser Custom Counters in Jet 4.0 and ADO 2.1
http://support.microsoft.com/default.aspx?scid=kb;en-us;240317

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/