INT IDENTITY Field Question

[Guest]

Hello. I'm a newbie when it comes to using Access as the front end for a SQL
Server database. We have an Access application that was developed using
local Access data, and we have recently moved the data to SQL Server. When
the data was in Access rather than the linked SQL Server tables, the
AutoNumber field on forms would be generated immediately and returned to the
form upon the first edit of ANY field on the form (of course I'm talking
about inserting new records).

The newly-generated AutoNumber value is then used upon navigating to other
fields, or upon certain events. Well, now that the data has moved to SQL
Server, and AutoNumber fields have been replaced with INT IDENTITY fields,
those values do NOT get generated until all data has been entered. Does
anyone know if there's a setting I can alter to have it generate the value
for me immediately upon the first edit?

My guess is that I'm just dreaming that I can just flip a switch and make it
so. I imagine I'll have to rework the logic of the forms.

Thanks,
John

 

[Patrice]

This is expected. The value is created by SQL Server when the record is
inserted so Access can't have any knowledge of that before the insertion...

It shouldn't be a problem (for example if the db has some kind of integrity
you won't be able anyway to insert related records before the main record is
created so it loos like consistent).

I would suggest posting about the exact problem knowing the value at the
time of the insert causes in your application as this is IMO best ot left
this behavior unchanged (if you really need the other behavior a quick
workaround would be to create the record first and then enter edit mode but
it may have problem if the user want to cancel the record creation).

 

[Guest]

Yes, I figured that I would have to rework the way I do some things as you
suggested below.

Thanks,
John