Stock Control help

[Lesg]

Hi Guys, I am trying to understand and fix a stock control element of a database.



It has a form with a combo for available and faulty stock.

If I select available on the form, items are added into the the inventory systems, adds and updates as required. However, whe I try the same with the faulty option, the system errors with a debug error saying needs to be updatable.

My problem is I added this many years ago, and I and struggling trying to get my head back around this, just getting complete brain freeze finding the problem. I have copied the code below, all the inventory elements work as mentioned fine, its the faulty option that throws the error:

Private Sub cmdClose_Click()
Dim rsGoodsLineItems As dao.Recordset
Dim rsInventory As dao.Recordset
Dim rsInventoryDetail As dao.Recordset
Dim rsFaultyinventory As dao.Recordset
Dim rsFaultyinventoryDetail As dao.Recordset


Dim strQty As String
Dim strSQL As String
Dim strSQLF As String
Dim strSQLCoS As String


  
  
  Dim strSQLCS As String
  Dim strSQLGIL As String
  
  DoCmd.OpenForm "frmProcessing", acNormal
  
   DoCmd.SetWarnings False


  Me.goodsintotal = Me.frmGoodsSub!txtGross
  Me.txtgoodsinnett = Me.frmGoodsSub!txtNett
  Me.txtgoodsinvat = Me.frmGoodsSub!txtVat
  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    Set rsGoodsLineItems = CurrentDb.OpenRecordset("tblgoodsinlineitems", dbOpenDynaset, dbSeeChanges)
    Set rsInventory = CurrentDb.OpenRecordset("tblinventory", dbOpenDynaset, dbSeeChanges)
    Set rsInventoryDetail = CurrentDb.OpenRecordset("tblinventorydetail", dbOpenDynaset, dbSeeChanges)
    Set rsFaultyinventory = CurrentDb.OpenRecordset("tblfaulty", dbOpenDynaset, dbSeeChanges)
    Set rsFaultyinventoryDetail = CurrentDb.OpenRecordset("tblfaultydetail", dbOpenDynaset, dbSeeChanges)


    If cbostocktype.value = 5 Then ' faulty
    
        strSQLGIL = "INSERT INTO tblfaultydetail ( qty" & vbCrLf
        strSQLGIL = strSQLGIL & "           , productID" & vbCrLf
        strSQLGIL = strSQLGIL & "           , cost" & vbCrLf
        strSQLGIL = strSQLGIL & "           , unitsID" & vbCrLf
        strSQLGIL = strSQLGIL & "           , name" & vbCrLf
        strSQLGIL = strSQLGIL & "           , rrp" & vbCrLf
        strSQLGIL = strSQLGIL & "           , Code" & vbCrLf
        strSQLGIL = strSQLGIL & "           , workingprice )SELECT tblgoodsinlineitems.qty" & vbCrLf
        strSQLGIL = strSQLGIL & "           , tblgoodsinlineitems.productID" & vbCrLf
        strSQLGIL = strSQLGIL & "           , tblgoodsinlineitems.cost" & vbCrLf
        strSQLGIL = strSQLGIL & "           , tblgoodsinlineitems.unitsID" & vbCrLf
        strSQLGIL = strSQLGIL & "           , tblgoodsinlineitems.name" & vbCrLf
        strSQLGIL = strSQLGIL & "           , tblgoodsinlineitems.rrp" & vbCrLf
        strSQLGIL = strSQLGIL & "           , tblgoodsinlineitems.Code" & vbCrLf
        strSQLGIL = strSQLGIL & "           , tblgoodsinlineitems.workingprice" & vbCrLf
        strSQLGIL = strSQLGIL & "        FROM tblgoodsinlineitems" & vbCrLf
        strSQLGIL = strSQLGIL & "       WHERE (((tblgoodsinlineitems.stockinID)=[forms]![frmGoodsReceipt]![txtStockinID]));"




        strSQLCS = "UPDATE tblfaulty " & vbCrLf
        strSQLCS = strSQLCS & "  INNER JOIN tblfaultydetail " & vbCrLf
        strSQLCS = strSQLCS & "          ON tblfaulty.productID = tblfaultydetail.faultyID SET tblfaulty.Sumofqty = [tblfaulty]![SumOfqty]+[tblfaultydetail]![qty]" & vbCrLf
        strSQLCS = strSQLCS & "       WHERE (((tblfaultydetail.productID)=[tblfaulty]![faultyID]) " & vbCrLf
        strSQLCS = strSQLCS & "         AND ((tblfaultydetail.name)=[tblfaulty]![name]));"


        
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQLGIL
        DoEvents
        
    ElseIf cbostocktype.value = 4 Then ' inventory
    
        strSQLGIL = "INSERT INTO tblinventorydetail ( qty" & vbCrLf
        strSQLGIL = strSQLGIL & "           , productID" & vbCrLf
        strSQLGIL = strSQLGIL & "           , cost" & vbCrLf
        strSQLGIL = strSQLGIL & "           , unitsID" & vbCrLf
        strSQLGIL = strSQLGIL & "           , name" & vbCrLf
        strSQLGIL = strSQLGIL & "           , rrp" & vbCrLf
        strSQLGIL = strSQLGIL & "           , Code" & vbCrLf
        strSQLGIL = strSQLGIL & "           , workingprice )SELECT tblgoodsinlineitems.qty" & vbCrLf
        strSQLGIL = strSQLGIL & "           , tblgoodsinlineitems.productID" & vbCrLf
        strSQLGIL = strSQLGIL & "           , tblgoodsinlineitems.cost" & vbCrLf
        strSQLGIL = strSQLGIL & "           , tblgoodsinlineitems.unitsID" & vbCrLf
        strSQLGIL = strSQLGIL & "           , tblgoodsinlineitems.name" & vbCrLf
        strSQLGIL = strSQLGIL & "           , tblgoodsinlineitems.rrp" & vbCrLf
        strSQLGIL = strSQLGIL & "           , tblgoodsinlineitems.Code" & vbCrLf
        strSQLGIL = strSQLGIL & "           , tblgoodsinlineitems.workingprice" & vbCrLf
        strSQLGIL = strSQLGIL & "        FROM tblgoodsinlineitems" & vbCrLf
        strSQLGIL = strSQLGIL & "       WHERE (((tblgoodsinlineitems.stockinID)=[forms]![frmGoodsReceipt]![txtStockinID]));"






        strSQLCS = "UPDATE tblinventory " & vbCrLf
        strSQLCS = strSQLCS & "  INNER JOIN tblinventorydetail " & vbCrLf
        strSQLCS = strSQLCS & "          ON tblinventory.productID = tblinventorydetail.productID SET tblinventory.Sumofqty = [tblinventory]![SumOfqty]+[tblinventorydetail]![qty]" & vbCrLf
        strSQLCS = strSQLCS & "       WHERE ((tblinventorydetail.productID)=[tblinventory]![ProductID]) " & vbCrLf
        strSQLCS = strSQLCS & "         AND ((tblinventorydetail.name)=[tblinventory]![name]);"


        
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQLGIL
        DoEvents
        
    End If
    
    
    If cbostocktype.value = 5 Then 'tblfaulty
        rsFaultyinventoryDetail.Close
        DoEvents


        Set rsFaultyinventoryDetail = CurrentDb.OpenRecordset("tblfaultydetail", dbOpenDynaset, dbSeeChanges)


                If rsFaultyinventory.RecordCount > 0 Then
                        rsFaultyinventory.FindFirst "[faultyID]=" & rsFaultyinventoryDetail![productID]
                    If rsFaultyinventory.NoMatch = False Then
                        DoCmd.RunSQL strSQLCS
              ElseIf rsFaultyinventory.NoMatch = True Then
                        DoCmd.OpenQuery "QryTempfaulty", acViewNormal
                    End If
                End If
          If rsFaultyinventory.RecordCount = 0 Then
             DoCmd.OpenQuery "QryTempfaulty", acViewNormal
           End If
        DoCmd.OpenQuery "QryDelTblFaultyDetail"
    'End If


        
    ElseIf cbostocktype.value = 4 Then ' tblinventory
        rsInventoryDetail.Close
        DoEvents


    Set rsInventoryDetail = CurrentDb.OpenRecordset("tblinventorydetail", dbOpenDynaset, dbSeeChanges)




       If rsInventory.RecordCount > 0 Then
                rsInventory.FindFirst "[ProductID]=" & rsInventoryDetail![productID]
            If rsInventory.NoMatch = False Then
                     DoCmd.RunSQL strSQLCS
              ElseIf rsInventory.NoMatch = True Then
                     DoCmd.OpenQuery "qryTemp", acViewNormal
            End If
         End If
          If rsInventory.RecordCount = 0 Then
             DoCmd.OpenQuery "qryTemp", acViewNormal
           End If
        DoCmd.OpenQuery "qryDeltblCSDetail"
    End If






rsInventory.Close
rsInventoryDetail.Close
rsGoodsLineItems.Close
rsFaultyinventory.Close
rsFaultyinventoryDetail.Close


Set rsInventory = Nothing
Set rsInventoryDetail = Nothing
Set rsGoodsLineItems = Nothing
Set rsFaultyinventory = Nothing
Set rsFaultyinventoryDetail = Nothing


DoCmd.Close acForm, "frmprocessing"
DoCmd.Close
End Sub

the system is split as BE SQL and Access front end so cannot add a copy to view

Thanks in advance

[Micron]

I can't pinpoint the issue but can say that any reserved word should be avoided (.name) and if not, then enclosed as [name] which you've done but not consistently. Which gives the error - the append or the update? The actual line might help if you can provide that. Also, when you open a recordset and want to do something to the underlying tables and the recordset isn't closed, you could be trying to update a table that is locked. I'd either update the recordset, or close it and run the sql instead.

[Lesg]

Hi, thanks for that feedback, I have updated to close the [name] object now, will look to see about revising that in the future, depending on how deeply its tied into the systems.

The error occurs here:

[Welshgasman]

No space between ) and SELECT?

workingprice )SELECT tblgoodsinlineitems.qty" & vbCrLf

Do yourself a favour Debug.print the sql unti you get it correct.
You can even then copy it into the query GUI and try it out, in case syntax errors still exist.?

[Lesg]

ok guys, I have got it working now by matching the details of the inventory part of the script and the faulty part, making sure everything matches, so this now all works. Thanks for your advise.

[Lesg]

actually, the updating section of this is not working, as mentioned it reports 'run-time error '3073' Operation must be an updateable query'

Can you advise where I add the Debug.Print to check please?

                If rsFaultyinventory.RecordCount > 0 Then
                        rsFaultyinventory.FindFirst "[productID]=" & rsFaultyinventoryDetail![productID]
                    If rsFaultyinventory.NoMatch = False Then
                            DoCmd.RunSQL strSQLCS
              ElseIf rsFaultyinventory.NoMatch = True Then
                        DoCmd.OpenQuery "QryTempfaulty", acViewNormal
                    End If
                End If

the error occurs here: DoCmd.RunSQL strSQLCS

[Lesg]

so I have copied the strSQLCS to access designer window and that also reports it must be updateable but no idea why its not updateable and no message to understand why it's updatable



I have checked the inventory option and that works fine and updates any products. I have also deleted the tblfaulty and created it again using the tblinventory as the template so it matches the table exactly, so have no ideas why this table would be not updateable??

[Micron]

Looks to me like you have no primary key in either table. That certainly can cause this. You might get away with just adding autonumber fields to each table and find that the query will do something, but it still won't be designed correctly. Or the fields you have joined on could be primaries if each of their records must be unique. In the end, the foreign key of the child table (the many side) should be the parent table (one side) primary key value. Perhaps a review of db normalization would be a good idea.

[Lesg]

so are you saying I need add a PK to tblfaulty and tblfaultydetail? tblfaultydetail is just a table to hold the data whilst we update tblfaulty, add/update items.

As mentioned the tblinventory is the same no PK and that adds & updates with no problems, so cannot understand why this would cause a problem.

in testing, I added a PK to tblfaulty (faultyID) then tried to run the update again and it errors again?

additional testing with suggestions ----->

I have tried again adding PK to both tables and when using the faulty option the process fails, however using the inventory option, the process works perfectly.

[Welshgasman]

You add the Debug.Print after the variable has been created and before you attempt to use it.
You can also copy and paste the output back here for members to view/comment on.

[Micron]

Somehow I don't think outputting the sql is going to help as the message indicates the sql syntax is OK but the query can't update.

As mentioned the tblinventory is the same no PK and that adds & updates with no problems,

That is only one table, I take it. It's the fact that you have joins and neither table has an index, not just that the tables have no index.

Perhaps post a db with these 2 tables and enough records in each to replicate the problem. You can remove everything else if that helps. Or check this out - emphasis on the 8th(?) bullet point.

http://allenbrowne.com/ser-61.html

[Lesg]

test update.zip

Hi Guys, sorry for delay, had a few other things to get on with for another client.

So have have exported the two tables into a test DB with the same strSQLCS update and it works perfectly, it just seems in the application it still will not work! I have attached db to this post

[Lesg]

So on further testing, If I convert tblfaulty to a local table from a linked table, the update script works, however, having tblfaulty as local is no good, as this needs to be in the SQL backend for ALL users, not the local user.

I have also tried using both tblfaulty and tblfaultydetail as SQL Linked but the script fails in that setup!

[ArviLaanemets]

You have a table tblFaultyDetail, where it looks like quantities of faulty details for every incomings for your stock are registered;
You have a table tblFaulty, where it looks like you store total of faulty details for every product.

So let's assume in table tblFaultyDetail, for product 'A' you have 3 incomings with quantities 1, 2, and 3;
In this case you must have in tblFaulty an entry, where for product 'A' in field SumofQty is stored 6;
And now you want a run a query, which adds another 6 to this SumOfQty, totalling as 12? And then maybe running this query again, etc. Either you want something else, or this is a very weird world you live in

[Lesg]

Hi Yes, this is for stock control, so if stock in-coming is OK, it is added to tblinventory and as new stock of the same item (productID) arrives, its added to the sumofqty for that item in tblinventory. When invoices are produced they will 'deduct' from 'sumofqty'stock from tblinventory.

However, if stock arrives, and for any reason it is deemed as faulty, we have to place the stock in tblFaulty and not into tblinventory as we cannot supply faulty stock.

Hopefully this makes it clear, so not a weird world really

I have been able to create the SQL script to work in the backend SQL, so now just trying to figure out how I can run this script via the VBA code now!