.Addnew with Autonumber

[bbrendan]

hi Can someone help please!

I have an order entry app that I would like to show the new OrderID after I insert a new record on the form.

Currently the database has an Autonumber field, and when I add a record, it creates this new OrderID on the fly. Which is cool. But how Do I grab that New OrderID and show it on the form


Below is the code to add all the relevant data from text boxes to the Table,, which works fine.

Code
-------------------------------------------------------------------------
Public Sub SaveOrderAdd()

Dim oRSMain As ADODB.Recordset
Dim strConnect As String

Dim strSQL As String

On Error GoTo ErrorHandler

strConnect = INI_getString("String", "ConnectString1", App.Path & "\dataconn.ini")

strSQL = "Select BillFirstName, ShipFirstName ,GBS_Account, Status, OrderID from Orders"


Set oRSMain = New Recordset

oRSMain.Open strSQL, strConnect, adOpenKeyset, adLockOptimistic

With oRSMain
.AddNew
.Fields("BillFirstName") = frmOrders.BillFirstName
.Fields("ShipFirstName") = frmOrders.ShipFirstName
.Fields("Status") = frmOrders.status
.Fields("GBS_Account") = frmOrders.cboAccountNumber
.Update
.Requery
End With

MsgBox ("The New Order Has been Added to the system" & vbCrLf & vbCrLf & "Your Reference number is:"), vbInformation, "ORDER SAVED"

'oRSMain.Close
Set oRSMain = Nothing


Exit Sub

ErrorHandler:
MsgBox "An error occurred in Saving your Order" & vbCrLf & vbCrLf & Err.Number & " : " & Err.Description, vbCritical, "ERROR : Saving Order"
WriteErrorLog CStr(Err.Number), Err.Description, Err.Source
Err.Clear

Exit Sub

End Sub

 

[zemp]

Look into using the '@@Identity' property.

zemp

 

[ArtieChoke]

Since you're using a keyset cursor, you should be able to directly access it from the newly added recordset using the field name.

"I think we're all Bozos on this bus!" - Firesign Theatre