Solved

Update Records through Joined Recordset

Posted on 2011-09-26
5
296 Views
Last Modified: 2012-06-22
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
Comment
Question by:pskeens
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 36676301
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36680023
>>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
 
LVL 2

Author Comment

by:pskeens
ID: 36685075
@ 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
 
LVL 42

Expert Comment

by:dqmq
ID: 36710612
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
 
LVL 2

Author Comment

by:pskeens
ID: 36711210
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question