CODEWITHSUNDEEP
sqlvbams-access
configure.ng
configure.ng

Reputation: 273

VBA Access Need to obtain autonumber before saving new record

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.

Upvotes: 2

Views: 6859

Answers (3)

Sham Yemul
Sham Yemul

Reputation: 463

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. 

DoCmd.RunCommand acCmdSaveRecord

'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

Upvotes: 0

Albert D. Kallal
Albert D. Kallal

Reputation: 49329

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.

Upvotes: 3

neverseenjack
neverseenjack

Reputation: 316

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.

http://bytes.com/topic/sql-server/answers/143378-identity-sql-vs-autonumber-access

Upvotes: 1

Related Questions