Thanks for your input. I will begin to look at doing it a different way.
Again thanks

"Klatuu" wrote:

There are a couple of options.
One way to do that is set up your own sequential numbering scheme. The
simple answer is using the DMax function to find the highest current highest
number and add 1 to it.
The other is to force the record to update as soon as a new record is
created; however, that may not be feasible if you have any required fields.

What your dilema shows is why you never use autonumbers for anything other
than surrogate keys. Autonumber fields are really not meant for human
consumption.

There are more issue about this practice than I have time to list. My
suggestion would be to change which field you are using for your order number
and set up a scheme to increment that number and assign it in the Current
event of the form.
--
Dave Hargis, Microsoft Access MVP


"jmillerWV" wrote:

This I understand. Yes I have set it up. The problem I need help with is that
SQL does not assign the "Autonumber" until after the INSERT is completed. I
need the ID number generated at the time the order is being entered so that
it will appear on the order sheet that is printed.

"Klatuu" wrote:

SQL Server has the capability to use your Access Autonumber field, but you
have to set it up.
First, you want to identify the field data type as int
Then under Table Designer look for Indentity Specification
Set (Is Identity) to Yes
Set Identity Increment to 1
Set Identlty Seed to 1

It will work exactly like an Autonumber field
--
Dave Hargis, Microsoft Access MVP


"jmillerWV" wrote:

I am in the middle of moving an Access2k3 Database to SQL. I have run into a
problem I need help with. My main table "OP" in Access uses they
autonumbering field as the primary key. this PK filed also acts as our order
number through the system. On main form "Add Record" starts a new record
enters dates and times by default the PK field then saves. I need some
suggestions on how to best duplicate the process of generating a sequential
number like autonumbering for the form and OP table. Hope this isn't
confussing.