I've got a database with a SQLServer back end which was migrated from Access and an Access front end. After the migration one problem I keep running into is that autonumbers are not generated until after the record is saved (kind of obvious but Access didn't seem to care). I have a form that opens to create a new record in a table, but elements of that form require the value of the autonumber (Identity) field of that new record to calculate things. I want to somehow obtain this number right as the form loads instead of having to save it and reopen it just to obtain this number. What's the best way to go about this? Thanks in advance.
Access databases return + generate the autonumber when the record is dirty. In the case of SQL server + Access you cannot use nor does the form display the autonumber UNTIL record save time. The simple solution is to thus force a save in the forms data, and then any following/existing code you have will continue to work.
So your code can look like this:
If Me.NewRecord = True Then Me.Dirty = False End If
The above will work as long as SOME editing has occurred. Note that if NO editing has occurred the above will NOT generate the autonumber ID (however even in non SQL server databases, when no editing has occurred then autonumber is not available anyway).
The above works for a bound form. If you have reocrdset code, then you change typial code like this:
Set rstRecords = CurrentDb.OpenRecordset("tblmain") rstRecords.AddNew
In above your VBA code can/could grab autonumber. However the above code for sql sever will have to force a save.
In fact code that will work for both ACE or SQL server is thus:
will become: Dim rstRecords As DAO.Recordset Dim lngNext As Long
Set rstRecords = CurrentDb.OpenRecordset("tblmain", dbOpenDynaset, dbSeeChanges)) rstRecords.AddNew ' code here does whatever rstRecords.Update rstRecords.Bookmark = rstRecords.LastModified lngNext = rstRecords!ID rstRecords.Close
So the simple “issue” is you need to write out the record to force SQL server to generate the auotnumber. Once you done this record save, then your forms and most VBA code should run “as is”. You do not need to "resort" to additional code such as select @@identity UNLESS you using SQL insertcommands as opposed to say above forms or recordset code.
What you are referring to is the AutoNumber column in Access which is an Identity field in SQL.
The only way to accomplish this functionality in SQL is to insert the record when your form is opening and then use the @@Identity in SQL to retrieve the most recent value.
When your Access application was combined together with the database, Access was essentially doing this for you. You can check the following link for more details.
If its on a bound form, give a save button which will save record to database of Master table.It will have nothing except the identity field's value. Then enable or show subform which needs ID from this master table/form.
Looks @@identity code may not be possible for you since its bound form. To convince users why they need to save before adding any data to form, I name the button "show details" or "Add data to details" , this saves the record using docmd on the form, and makes visible the details section.
'OR a different code snippet, Just update Dirty status of form
If Me.Dirty = True then Me.Dirty = False End If
'You may write code to show/visible detail section/subform