Update Records through Joined Recordset
Posted on 2011-09-26
Hopefully I can explain this. I have two tables. One table (INV_DETAILS) and the second (INV_SHIPPED). INV_DETAILS Contains all of the inventory information per TagID. The INV_SHIPPED Table contains a log file of all shipments per tagID and Shipment Number.
I have a view that uses these two tables and calculates INV_DETAILS.TAG_PCS that was received and INV_SHIPPED.BOL_PCS and returns INV_AVAIL in the view.
Now what I need to do is when I save a new shipment I need to UPDATE INV_DETAILS record. Below is what I have to do this however the recordset is not updateable since its a joined table. Any thoughts on a better solution?
strSQL = "SELECT A.INV_TAGID, A.INV_STATUS, A.INV_AVAIL, B.INV_AVAIL AS INV_PCS_AVAIL" & _
" FROM DBO_TBL_INV_DETAILS A INNER JOIN" & _
" VW_INV_DETAILS B ON A.INV_TAGID = B.INV_TAGID AND A.INV_SHIP_LOC = B.INV_SHIP_LOC AND" & _
" A.INV_SHIP_NUM = B.INV_SHIP_NUM"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
If rs("INV_PCS_AVAIL") = 0 Then
rs("INV_STATUS") = "SHIPPED"
rs("INV_AVAIL") = "N"
Set rs = Nothing