AutoNumber on SQL table; not created until table is saved???

[cesqueda]

I am currently moving my databases from Access to MSSQL 2005. Everything has gone well except one thing. I used to be able to grab an AutoNumber field prior to saving the record to use as the link to a child table. See below:


'This command button first adds a new record to the Component Type Table, TblComponentClasses, if needed
'Then opens the Parameter Entry and Description Creation form, FrmStockComponentParameter.

Dim intComponent As Integer
Dim strWhere As String

'cse save record so can select autoid
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 (I TRY TO SAVE IT HERE SO I CAN GRAB IT BUT I GET A WRITE CONFLICT)

intComponent = Me.autoid (THIS IS THE NUMBER I WANT TO GRAB)
strWhere = "[Component Number]= " & intComponent

'Ensures that a record with the ProductID doesn't already exist in TblComponentClasses, as
'the [ProductID] Field is indexed as a unique value. If the ProductID doesn't exist
'the recordset is opened, and the record added
If IsNull(DLookup("[Component Number]", "TblStockComponentClassifications", strWhere)) Then
'MsgBox "load records"
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("TblStockComponentClassifications", dbOpenDynaset, dbSeeChanges)

rs.AddNew
rs![Component Number] = intComponent
rs.Update

rs.close
Set rs = Nothing
Set db = Nothing

End If

'The form to enter the Description and Parameters is opened according to the
'record in TblComponentClasses corresponding to the ProductID
DoCmd.OpenForm "FrmStockComponentParameter", , , strWhere
Forms!FrmStockComponentParameter!txtProductID = Me.ProductID

End If

End Sub


SQL seems to not want to create the number until you save the record. If I try to save the record, as I do in the code above, I get a write conflict. Any advice would be greatly appreciated.

 

[ca8msm]

Have a look at the scope_identity() method


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[LNBruno]

That's correct. The IDENTITY is not set until the record is successfully inserted.

Typically, a return value with the IDENTITY of the new record is set in a stored procedure and returned to the calling procedure. Not as safe would be to query the table for the MAX(ID).

You might get a more Access-specific answer if you post there:

http://www.tek-tips.com/threadminder.cfm?pid=701

< M!ke >
[small]Don't believe everything you think.[/small]