Thanks for the reply. I can see from the feed back that I will need to find a
different way than the way I was doing it. That's what makes this fun,
sometimes you have to find a different way of doing the same thing and in the
process you learn something new. Again thanks for your help.

"Armen Stein" wrote:

In addition to Dave's suggestion for using an Identity field in SQL
Server, I'll add that the behavior is slightly different. In Access,
the new AutoNumber key value is generated the moment you start typing
in the form. In SQL Server, the Identity isn't generated until later,
when the record is actually saved.

If you have code that depends on the Access behavior, it will need to
be changed. If you haven't already, check out my PowerPoint
presentation on techniques for using Access as a client-server
front-end to SQL Server databases. It's called "Best of Both Worlds"
at www.JStreetTech.com/Downloads. It includes some thoughts on when
to use SQL Server, performance and security considerations,
concurrency approaches, and techniques to help everything run
smoothly. It also shows some code to retrieve the just-generated
Identity value.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com