I have an input form that captures information about pallets received at our loading dock. The user enters some information in the form, then clicks a button which writes the user-supplied info back to a SQL table. This table includes an auto-numbering IDENTITY column (called dbID) and a computed column (called PalletID) that uses the value of the identity column to create a unique pallet ID number, which is also stored in the table.
After the record is inserted and PalletID has been computed, I need to retrieve the value of PalletID and display it on my form so it can be printed on an ID tag. I tried to open a recordset using SELECT... WHERE but I don't know the value of either IDENTITY column or the PalletID column and those are the only possibilities for unique identifiers for my record.
How can I return the value of either the dbID or PalletID columns after the INSERT completes?
Here's my code:
set cn = createObject("ADODB.Connection")
strConnString = "Provider=SQLOLEDB;Data Source=" & strSQLInstanceName & "; _
Persist Security Info=False; _
Initial Catalog=" & strDatabaseName & ";Integrated Security=SSPI"
' Write form values to table
' dbID is auto-sequence IDENTITY folumn. PalletID is computed as Current Year
' plus dbID value
strSQL = "INSERT INTO dbo.T1 (ManID, RecDate, SCode) VALUES _
('" & Form.CodeObject.ManID & "','" & _
Form.CodeObject.RecDate & "','" & _
Form.CodeObject.StockCode & "')"
set cn = nothing
' Call function that creates text file output for barcoded tag
Many thanks, in advance, for your help!