Warehouse stock location tracking

[NB]

Hi

A part of my application involves management of stocks in the
warehouse.

Incoming/outgoing transactions, stocktake, quantity on hand have all
been satisfactorily managed. The app has run robustly for some time.

Now I need to develop location tracking functionality.

I believe this is a common topic and it might have been addressed.
Therefore I may get some expert idea on best practice from this NG
(though I couldn't find any yet after some searches here)

Basically, so far I have had

tblIn(InID,...), tblInDetail(ProductInID, InID, ProductID,...),
tblInDetailPallet(InDetailPalletID, ProductInID, Inqty...)

tblOut(OutID,...), tblOutDetail(ProductOutID, OutID, ProductID,...),
tblOutDetailPallet(OutDetailPalletID, ProductOutID, Outqty...)

tblStocktake(stocktakeID, productID, ...)

tblWarehouseLocation(WHLocID, ...)
tblWarehouseLocProduct(WHLocProductID, WHLocID, ProductID, ...)

A warehouse location is a space unit that fits 1 pallet. On 1 pallet,
there may be several products.
The fact that stocks are kept on pallet and I also need to keep track
of pallet quantity explains the above structure where stock quantities
are kept in ...DetailPallet tables

To accomplish location tracking I added

tblWHLocProduct(WHLocProductID, WHLocID, ProductID, qty, ...)

Now the issue is business rules enforcement. How do I ensure a
properly updated tblWarehouseLocProduct?

This is how I figure the solution:

In the form for Incoming transactions, I allocate stocks to warehouse
location (WHLocID).
At Form's beforerupdate and ondelete event I capture the value of
WHLocID
At Form's afterupdate event:
- insert a record into tblWarehouseLocProduct (if WHLocID
beforeupdate's value is null)
- update the existing record to new values if WHLocID beforeupdate's
value is not null
- delete the matching record from tblWarehouseLocProduct (if WHLocID
beforeupdate's value is not null)
At Form's afterinsert event:
- insert a record into tblWarehouseLocProduct
At Form's after del confirm:
- delete the matching record from tblWarehouseLocProduct (if WHLocID
ondelete's value is not null)

In the form for Outgoing transactions, I perform the similar tasks (in
the reverse direction as stock is moving out)

I also need a new form to maintain movement within the warehouse (from
1 location to another)
This may be an unbound form where user choose a product, from-location
and to-location and press a button to confirm the movement (the code
behind will update the matching record in tblWarehouseLocProduct)
I think I can get all those to work. However I feel the logic appears
a tad too complex and there must be a better approach out there.

I appreciate any suggestion

Thanks
NB