Append one record only into another table (1 Viewer)

[razebo]

I am modifying one the default Access template databases (InventoryControl)...

I created another number field in the Inventory Transactions table (UnitsRemoved) and duplicated this table to create an append query. I have also set up a macro that triggers the append query on the UnitsRemoved field 'after update'. The append query works except I get all records from Inventory Transactions...I only want the current record - after it has been changed.

I think my problem is with setting criteria..the duplicate table with same fields(Total Units Removed)is basically going to be recording change history and could be any numeric value so there will be duplicate transaction IDs, but this should not matter since the key in Table2 is an autonumber field. How do I set the criteria for this to get only the changed record? (I am not a VB person!)

INSERT INTO [Total Units Removed] ( TransactionDate, ProductID, TransactionDescription, UnitsRemoved, DateRemoved, Location, PurchaseOrderID )
SELECT [Inventory Transactions].TransactionDate, [Inventory Transactions].ProductID, [Inventory Transactions].TransactionDescription, [Inventory Transactions].UnitsRemoved, [Inventory Transactions].DateRemoved, [Inventory Transactions].Location, [Inventory Transactions].PurchaseOrderID
FROM [Inventory Transactions]
WHERE [Total Units Removed].UnitsRemoved = [Forms]![Products]![ProductsSubform]![UnitsRemoved];

 

[razebo]

As I suspected..needed to be done through event procedure..

Private Sub Form_AfterUpdate()
''******************************************************
'' Update the Total Units Removed table
''******************************************************
Dim dbsMy As Database
Dim strSQL As String, lngTransactionID As Long, intQuantityRemoved As Integer
Dim datRemoveDate As Date
Dim varTempVari As Variant

Set dbsMy = CurrentDb()

varTempVari = Me![txtTransactionID]
If Not IsNull(varTempVari) And varTempVari <> 0 Then
lngTransactionID = varTempVari
End If
Me.Requery
varTempVari = Me![UnitsShrinkage]
If Not IsNull(varTempVari) And varTempVari <> 0 Then
intQuantityRemoved = varTempVari
End If
varTempVari = Me![DateRemoved]
If Not IsNull(varTempVari) And IsDate(varTempVari) Then
datRemoveDate = varTempVari
End If

strSQL = "INSERT INTO [Total Units Removed] ( TransactionID, TransactionDate, ProductID, TransactionDescription, UnitPrice, UnitsOrdered, UnitsReceived, UnitsRemoved, DateRemoved, Location, Expiration, PurchaseOrderID )" _
& "SELECT [Inventory Transactions].TransactionID, [Inventory Transactions].TransactionDate, [Inventory Transactions].ProductID, [Inventory Transactions].TransactionDescription, [Inventory Transactions].UnitPrice, " _
& "[Inventory Transactions].UnitsOrdered, [Inventory Transactions].UnitsReceived, [Inventory Transactions].UnitsRemoved, [Inventory Transactions].DateRemoved, [Inventory Transactions].Location, [Inventory Transactions].Expiration, [Inventory Transactions].PurchaseOrderID " _
& "FROM [Inventory Transactions] " _
& "WHERE ((([Inventory Transactions].TransactionID)= " & lngTransactionID & "));"

dbsMy.Execute strSQL

DoCmd.Hourglass False

End Sub