If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
ranges for autonumber
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 !!! |
#2
|
|||
|
|||
ranges for autonumber
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. -- Kind regards Noëlla "Tink" wrote: 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 !!! |
#3
|
|||
|
|||
ranges for autonumber
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. ---- HTH Dale "Tink" wrote: 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 !!! |
#4
|
|||
|
|||
ranges for autonumber
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/f...19 89c53d7ffb Ken Sheridan Stafford, England Tink wrote: 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 !!! -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
ranges for autonumber
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 "Tink" wrote in message ... 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 !!! |
#6
|
|||
|
|||
ranges for autonumber
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 |
#7
|
|||
|
|||
ranges for autonumber
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/...g=ws-msdevapps Ken Sheridan Stafford, England Ron2006 wrote: 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 -- Message posted via http://www.accessmonster.com |
#8
|
|||
|
|||
ranges for autonumber
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 |
Thread Tools | |
Display Modes | |
|
|