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.

 

[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

 

[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