Hi all -
I have an Access 2007 front end, with a SQL Server 2000 back end.
I have a bound form to add new data to my table. The table uses a SQL Identity Seed for the primary key.
When the user clicks my Save button, I need to be able to save the record, retrieve the ID, and then use it to insert rows into some related tables.
The problem that I am having is that for a reason I do not know, the ID is not becoming available after the user saves the record.
MyID = Nz(Me.txtMyID.Value, 0)
I had previously been using Me.Dirty, but was told to try acCmdSaveRecord. It seems to be working slightly better, but still having issues getting that ID number.
If I step through the code, no problems at all. So I tried putting in DoEvents and a Refresh, but that didn't help.
How can I reliably get the ID number?
Side note: Eventually when I have time, I have plans to rebuild the form as non-bound, and creating a dynamic pass through query to do everything, that way I can use Identity_Scope(). But right now I just don't have the time. I need to put one more band-aid on this first.