Code:
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
' updates the costprice of item before posting
DoCmd.SetWarnings False
' strSQLCoS = "UPDATE products " & vbCrL
' strSQLCoS = strSQLCoS & " SET products.cost = [tblgoodsinlineitems].[cost]" & vbCrLf
' strSQLCoS = strSQLCoS & " FROM products" & vbCrLf
' strSQLCoS = strSQLCoS & " INNER JOIN tblgoodsinlineitems" & vbCrLf
' strSQLCoS = strSQLCoS & " ON products.productID = [tblgoodsinlineitems].productID;"
' DoCmd.OpenQuery "qryGoodsInCostUpdate", acViewNormal ' legacy Access query
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 = 2 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 = 1 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 = 2 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 = 1 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
everything runs, but tblInventory is not updated, nothing is added and no existing products are updated and no errors are reported?