: "Access97: Auto Nbr Field w Blanks and AutoNbr"

I have an Access97 main table that does not have an auto nbr field with the old records. I need to add new records to this table that need to have an auto number, e.g., EquipID. Further, the old records must have blanks for the EquipID field and the new records must have the EquipID(auto nbr) starting with 1,2,3…

Currently, I have a temp table with the auto number w/ form and after each new record is keyed in, I append the auto number with other fields to the main table. Works fine! Problem: my temp table does not have old records, such that operators cannot search for the old records. Mgmt wants this form to have the flexiblity to find/edit old and new records without changing the form. I now experimented with making an exact copy of the exisiting form and connected it to the main table whereby all old/new records are available for search/edit…via a command button, but this solution is not satisfactory with mgmt as they object to a new (with same setup)form popping up. They want the same form to switch table/query source under the command button of “Find Record". Any one with a solution to my problem…Desperate!!!


uyeshiro6Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TrygveCommented:
You can't have both - Autonumber and blank fields.

You will need something like this.

Sometimes during the editing process of a new record you need to get the largest number, add 1 and store it.

Something like this:

dim pMaxEquipID as long

pMaxEquipID = nz(dmax("[pMaxEquipID]","TableName"),0)+1

me!EcuipID = pMaxEquipID
 
' Store the value so that when the next user enters this stage the same number is not used.
docmd.runcommand accmdSaveRecord
0
uyeshiro6Author Commented:
Hi Tryve,

Thank you very much for your help and quick response...

I got some local help and with some of the hints on the prev ans question...we were able to solve the problem pretty much along the lines what you have specified...

You may have the 200 pts...as promised.

Thanks,
Ron
0
TrygveCommented:
Thanks a lot!

Is it possible for you to post the solution here so that we can see?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

JimMorganCommented:
Just another contrary solution:  Since you are primarily trying to determine the old records as opposed to the new records, if might just be easier to let every record use the autonumber field for the EquipID.  You can differentiate the two by changing the EquipID of the old records to - the number assigned.  Autonumbers can go from -2 million something to +2 million something.  We do this to differentiate equipment which is physically installed versus equipment which just describes what a piece of equipment looks like.  They select an equipment description (-ID) and it is added as a new record with a +ID and associated with the location of installation.

Another option is to add a Yes/No OLD Equipment field.  The default is No (0).  When you add on old piece of equipment, force the field to Yes.

Jim
0
uyeshiro6Author Commented:
Hi Jim,

Thanks very much for your advice.

I have already done this for my client...I have created another column and updated it with a 1 for all the "old" and with a 2 all the "new" ...which to me is a better way, but got let the client drive his own car....

Thanks, Jim
0
JimMorganCommented:
I know how this is.  It seemed so obvious that I was embarassed about even mentioning it.

At lot of people don't know that autonumber fields can be - and +.  Also you can assign/change a number as long as the new number is unique.

Jim
0
TrygveCommented:
In SQL Server there is something called GUID fields which are Globally Unique Identifiers.

These give each record a totally unique ID and this means that you can use this field to input records on you laptop on a trip, then connect to the database and transfer the records when you get and there are no key violations. It is not a nicely formatted numric expression, but very usfull when you need to be able to refer to the key from other tables etc. and still need the "autonumber" functionality.
0
uyeshiro6Author Commented:
Hi Tryve,

Thank you for your additional commeent...re: GUID....might have to use something like that in the future...
I'm away from my project site now...but later I would be glad to share the code that we used...

Happy Holidays to you and JimMorgan...Aloha...
Ron
0
TrygveCommented:
Same to you!
0
JimMorganCommented:
Aloha!  You guys from the Islands just have to rub it in this time of year.  :-)

Season Greetings from the Morgan family.

Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.