ranges for autonumber

[Tink]

Hi,

How can I define ranger for autonumbers ?

I would like Access to give an ID number depending on what has been selected
in another field.

For example :

Employee numbers should be like that :
Employee working in London : from 1 to 199'999
Employee working in NY : from 200'000 to 399'999
Employee working int HK : from 400'000 to 599'999

I would like Access to give the number when all the form is completed. And
depending on the location that has been selected.

Can anyone help me ?
Thanks !!!

 

[NG]

Hi Tink,

I'm afraid you cannot use an autonumber field to distribute ID numbers the
way you want.
What you can do is: create a table tblID_Ranges with the fields: Workzone
(text), LowerBoundary (long integer); UpperBoundary(Long integer);
LastAssigned (Long Integer).

The each time you enter a new employee you can lookup the last used number
in this range, add 1 to it and assign this number as new ID. Yoiu then
update the last assigned number in the table. You can write this code in the
Onclick event of a command button, or in the before insert event of the form.

 

[Dale Fye]

As Noella indicates, you will have to create a function that does this for you.

Your field data type will need to be set to Long Integer. Then, create a
function that you pass the value of the city where the employee works, and it
returns the next appropriate value. Something like:

Public Function fnNextEmpID(City as String) as Long

Dim lngLower as long, lngUpper as long

Select Case City
Case "London"
lngLower = 1
lngUpper = 199999
Case "NY"
lngLower = 200000
lngUpper = 399999
Case "HK"
lngLower = 400000
lngUpper = 599999
end Select

strCriteria = "[EmpID] >= lngLower AND [EmpID] <= lngUpper"
fnNextEmpID = NZ(DMAX("EmpID", "yourTableName", strCriteria), lngLower)
+ 1

end function

Personally, I would not restrict my employee IDs this way. I would let them
range freely, and would store the ID of the city where the individual works
in the Employees table. This would make it much easier to get the next
number, and would not require code changes if you add another city to the mix.

 

[KenSheridan via AccessMonster.com]

That would not be a good design as it introduces redundancy into the table.
On the one hand the ID number encodes the employee's work location, but at
the same time the work location is stored in another column in the table.
Consequently the table is exposed to the risk of inconsistent data.

If you really need the ID numbers to reflect the work location, then you
should not store the location in another column in the table, but use the ID
number to reference another table of work locations as Nöella suggests, but
it raises the problem of what you'd do if an employee moves location. Their
ID number would then no longer reflect their work location unless changed to
a new number, which might have other detrimental effects.

Structured keys like this are rarely a good idea and I'd strongly recommend
that you follow Dale's advice of storing the work location as a column in
the table, but without also encoding this in the ID number. The latter could
then either be a straightforward autonumber which inserts arbitrary unique
values, or, if sequence is important, you can compute the next number when a
record is inserted. Roger Carlson has a method for doing this, which also
handles conflicts in a multi-user environment, at:

http://www.rogersaccesslibrary.com/...?TID=395&SID=83z7c11zc7b721d2a1e51989c53d7ffb


Ken Sheridan
Stafford, England

 

[Steve]

You can more easily achieve the same thind by adding a work location table
to your database and adding a WorkLocationID field to your employee table:

TblWorkLocation
WorkLocationID
WorkLocation

TblEmployee
EmployeeID
EmployeeFName
EmployeeLName
WorkLocationID
etc

Steve
(e-mail address removed)

 

[Ron2006]

Be careful of any solution that you derive that entails you going out
and getting the last used number and then adding 1 to it to get your
new number.

IF you have more than one person at a time EVER adding records then
you will encounter the problem of two records with the same number.
Emp A starts the add action, then answers the phone before getting off
of the record; Emp B then starts and completes his/her addition of a
new record. When Emp A leaves his/her record, you NOW have two records
with that same number. Using a real autonumber gets around that
problem but can skip numbers.

Ron

 

[KenSheridan via AccessMonster.com]

Ron:

There are a number of solutions to that. Roger Carlson has a simple one at
the link which I gave in my reply to the OP. A slightly more complex one of
mine which also allows for the next number to be used to be 'seeded' is at:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[Ron2006]

Ken,

Agreed. And I believe I used your specific approach to solve that
particular problem when I was encountering it a while back. I was just
trying to warn Tink of the potential. I warned someone else of the
potential problem and they responded that they wouldn't have the
problem since only one person was ever adding records.

So I tend not to give the solution until the questioner actually
realizes that they have a problem. I have not decided yet if I will
continue that way or not.

Thanks for the link.. I will add it to my notes on the solution to
that problem.

Ron

(I just checked my notes and that was indeed the reference I had on
how to solve the problem.)
Thank you again