New Record number (not AutoNumber) upon new record creation

[lmjje]

I have been pouring through this, and other forums looking for the answer to this question. So far I have not been able to find it. I am not a beginner, nor am I an expert...I am part of that dangerous middle group that tries to do things just beyond their experience level, and goes bald in the process from pulling their hair out when it doesn't work.



I have a personnel tracking database that now needs two "AutoNumber" fields. The REAL AutoNumber is set at PersonID in the "Master" table I need a second field (UnitID) to auto generate a number when a new record is created. My thought was to use the AutoNumber in PersonID and then add 1 or 2 or 10... to it. I have tried everything that I can think of to get it to work...but so far no love. Any help would be greatly appreciated.

[Rod]

There are a number of ways to do this. However let me first ask whether this is a multi-user application where more than one person may be using the db simultaneously.

[RuralGuy]

This link should help: http://www.baldyweb.com/CustomAutonumber.htm

[lmjje]

RuralGuy, thank you for the rapid reply. Maybe I am entering this string in the wrong location. I am entering it in the Master table, in the UnitID field under the Default Value. When I do that I get "Unknown function 'Nz' in validation expression or default value on 'Master.UnitID'". This is what I have been getting everytime I have tried a Nz or DMax expression.

[lmjje]

Rod, this (for now) is a single user application. In the next 6 months to a year it may become a multi-user environment.

[RuralGuy]

RuralGuy, thank you for the rapid reply. Maybe I am entering this string in the wrong location. I am entering it in the Master table, in the UnitID field under the Default Value. When I do that I get "Unknown function 'Nz' in validation expression or default value on 'Master.UnitID'". This is what I have been getting everytime I have tried a Nz or DMax expression.

Have you checked to see if you have any bogus *MISSING* References?

[lmjje]

AAAhhhh...and this is where my knowledge fails. So far everything that I have done to include VB and SQL coding has worked properly and so I have not had to mess with references yet. I am afraid that I do not know how to check that. I will have to figure out how to check that.

[RuralGuy]

THis link may help: http://www.btabdevelopment.com/ts/missref

[lmjje]

Very helpful...thank you. I was out looking for the directories on my C: drive. Yes, based on the tutorial all of my references are present and accounted for.

[orange]

I have a personnel tracking database that now needs two "AutoNumber" fields. The REAL AutoNumber is set at PersonID in the "Master" table I need a second field (UnitID) to auto generate a number when a new record is created. My thought was to use the AutoNumber in PersonID and then add 1 or 2 or 10... to it. I have tried everything that I can think of to get it to work...but so far no love. Any help would be greatly appreciated.

What exactly is the new number for? Please describe, perhaps with an example.
Maybe you could describe in plain English - no autonumbers etc - WHAT you are trying to do. Maybe there are options for solution.

[lmjje]

I am trying to generate a number automatically in sequential order to the highest previous number to fill in a employee ID field. it cannot be the AutoNumber in the Master table...it must be its own self generated number that is created as soon as the Add New Employee form is opened up.

[orange]

NewNumber = DMax(YourEmployeeId) + 1

Why do the numbers have to be sequential? And can't be autonumber? Just curious.

You didn't really address my question in non Access - plain English.

Maybe you could describe in plain English - no autonumbers etc - WHAT you are trying to do. Maybe there are options for solution.

[RuralGuy]

You do know that an AutoNumber is not guaranteed to be contiguous, right?

[lmjje]

yes...that is why I do not want to use it...using that number to generate a new number was just one way that I tried to get a number to auto generate. I have a work around that is working for now, but I would prefer to have an actual solution and not a work around.

[RuralGuy]

As Rod had asked earlier, being MultiUser may bite you later on with your solution.