• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

Update Records through Joined Recordset

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)
               
                    rs.Edit
                        If rs("INV_PCS_AVAIL") = 0 Then
                            rs("INV_STATUS") = "SHIPPED"
                            rs("INV_AVAIL") = "N"
                        End If
                    rs.Update
                    rs.Close
                    Set rs = Nothing
0
pskeens
Asked:
pskeens
  • 2
  • 2
1 Solution
 
dqmqCommented:
Not completely sure I understand the purpose of the view or how your recordset is limited to a single record.  But in any case, this SQL eliminates the join and may make your recordset updatateble.

"SELECT A.INV_TAGID, A.INV_STATUS, A.INV_AVAIL, B.INV_AVAIL AS INV_PCS_AVAIL" & _
                         " FROM DBO_TBL_INV_DETAILS A 
WHERE EXISTS (Select * from VW_INV_DETAILS B where  a.INV_TAGID = B.INV_TAGID AND A.INV_SHIP_LOC = B.INV_SHIP_LOC AND A.INV_SHIP_NUM = B.INV_SHIP_NUM"

Open in new window

0
 
Anthony PerkinsCommented:
>>Now what I need to do is when I save a new shipment I need to UPDATE INV_DETAILS record.<<
So why not use an UPDATE statement instead of using RBAR?
0
 
pskeensAuthor Commented:
@ acperkins

I was going to use an Update statement but the conditions have to be right for the update to happen.  Only updated the inventory with "SHIPPED" IF the count remaining is 0.  The above statement worked fine and here is the solution:


                Dim strUpdateInv As String
               
                strUpdateInv = "SELECT A.INV_TAGID, A.INV_STATUS, A.INV_AVAIL" & _
                         " FROM DBO_TBL_INV_DETAILS A" & _
                         " WHERE EXISTS (Select * from VW_INV_DETAILS B where a.INV_TAGID = B.INV_TAGID" & _
                         " AND A.INV_SHIP_LOC = B.INV_SHIP_LOC AND A.INV_SHIP_NUM = B.INV_SHIP_NUM AND B.INV_AVAIL = '0')"

                Set rs = CurrentDb.OpenRecordset(strUpdateInv)
               
                    If rs.RecordCount > 0 Then
                        rs.Edit
                            rs("INV_STATUS") = "SHIPPED"
                            rs("INV_AVAIL") = "N"
                        rs.Update
                        rs.Close
                        Set rs = Nothing
                    Else
                        rs.Close
                        Set rs = Nothing
                    End If
0
 
dqmqCommented:
I concur with acperkins, an update statement is a better solution; you can get it to work.  The logical flaw with the recordset is that it potentially contains multiple records and you are only updating the first.  Perhaps your view, precludes that possibility, but we can't see that part.  

If you are intending to update a single record, it is unclear how your view restricts the recordset to a single record.  If you are intending to update all records meeting the INV_AVAIL = '0' condition, then the recordset needs to loop.  The Update statement is cleaner and more self-documenting in that regard.  Like this:


SQL = "Update DBO_TBL_INV_DETAILS
   set INV_STATUS = 'SHIPPED'
   set INV_AVAIL = 'N'
   where EXISTS
           (Select * from VW_INV_DETAILS B where a.INV_TAGID = B.INV_TAGID AND A.INV_SHIP_LOC = B.INV_SHIP_LOC AND A.INV_SHIP_NUM = B.INV_SHIP_NUM AND B.INV_AVAIL = '0')"

 

(side note:  you are comparing B.INV_AVAIL to a string.  Are you sure that is correct?)
 
0
 
pskeensAuthor Commented:
Thanks DG, I will change it to the Update string.  I agree its better but for some reason could not get it to work.  

And I since changed the string to variant on the B.INV_AVAIL field.  Thanks again
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now