I have the following sal insert that I am performing and I can correctly get the new autonumber field that is assigned after the insert, so I can use that value to do inserts to child tables. My problem is the "SELECT * FROM tblRefundDetailReporting" is taking too long on a table that has many rows.
Is there a more efficient way about getting the new autonumber value inserted? I'm not interesed in selecting max after insert, because I'm not sure how accurate that is.
Here is my code:
The rsInsert!RefundDetailID is the autonumber field.
strSQL = "SELECT * FROM tblRefundDetailReporting"
Set rsInsert = New ADODB.Recordset
rsInsert.CursorLocation = adUseClient
rsInsert.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
!RefundID = IIf(IsNull(lngRefundID), "NULL", lngRefundID)
!TrackingNumber = Trim(rs![Express or Ground Tracking ID])
!ShipmentAmount = IIf(IsNull(rs![Transportation Charge Amount]), "NULL", rs![Transportation Charge Amount])
!ShipmentDate = IIf(IsNull(dtShipmentDate), "NULL", dtShipmentDate)
!ChargedAmount = IIf(IsNull(rs![Transportation Charge Amount]), "NULL", rs![Transportation Charge Amount])
!CreatedDate = Now()
bookmark = rsInsert.AbsolutePosition
rsInsert.AbsolutePosition = bookmark
lngRefundDetailID = rsInsert!RefundDetailID