?
Solved

Update Records through Joined Recordset

Posted on 2011-09-26
5
Medium Priority
?
300 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 2000 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

777 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