Solved

Update Records through Joined Recordset

Posted on 2011-09-26
5
256 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
  • 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now