"Autonumber" issue post-SQL migration

[ExcessionOCP]

Hi All,



In what was probably a massive bodge, I had a form with a hidden field linked to to an Autonumber ID field in the source table that served as the record's ID and PK. When my users started entering information into the form, this field was automatically updated with the record's ID, which I then went on to use when the user submitted the form to perform some data manipulation before saving the record.

The problem is, that now I have migrated the app's back end to SQL server, this field is no longer populated when the users start entering data, and I suspect isn't even generated until the record is saved and SQL server does its thing to write the record to the DB.

Does anyone have any other suggestions for how I can access the record's ID before it is saved? Now that I type this out I'm realising how much of a bodge this must have been and that there's probably a much better, safer, faster way of doing this.

Cheers,

Phil

[Minty]

I have seen this problem with conversions from Access to SQL before, and depending on how the form is designed it can be a simple or painful fix.
As you surmised, you only get the ID as soon as the record is saved.

So the only real option is - force a save early doors in the process, then do your other processing.

You can do an insert on the table in code on going to a new record, and then retrieve the new empty record as the "New" record, if they cancel then you are caught having to delete the record or marking it as "Deleted" in some way.

[pbaldy]

I have an older app where I had this situation, I grabbed the ID from a bound textbox in the after insert event of the form.

[ExcessionOCP]

I have an older app where I had this situation, I grabbed the ID from a bound textbox in the after insert event of the form.

Hi, could you give me a bit more info about the after insert event? This isn't an event I've used in the past.

[pbaldy]

Hi, could you give me a bit more info about the after insert event? This isn't an event I've used in the past.

https://learn.microsoft.com/en-us/of...erinsert(even)

[ArviLaanemets]

One way to correct such issues:

In SQL server, create a table like the Access one with a new autonumber ID, + a long integer field for Access autonumber value (my advice is, name it something like MyOldId, and the new key field same as old one in Access);
Insert all data (previous autonumeric id included) from Access table into this SQL Server table - all records get a new autonumeric ID, and in another field, you'll have values of old Access autonumeric ID's;
In SQL Server, in all tables which contain foreign keys linked to old Access ID field, rename all those old foreign keys, and into every such table, add a field for saving new foreign key with old name. Run update queries for all such tables (set new foreign keys to matching primary key value, comparing old keys in WHERE clause);
Remove all renamed fields which contain old ID's and foreign keys from all SQL Server tables.

[NoellaG]

I suppose you let the number generate through the identity specification property of the PK field. If you want to use the number before the record is saved, you can create a sequence on SQL server and take the number with a select statement like

select next value  for MySeq

[Gicu]

Some more reading for you :
https://www.red-gate.com/simple-talk...ntity-columns/
Using the sequence as already suggested would probably be the safest, you can also try the IDENT_CURRENT plus IDENT_INCR if your db doesn't have a lot of concurrent users that might impact the results.
Cheers,