Step Through Queries in Code to find errors

[Lesg]

As others suggest, debug.print and breakpoints are a very helpful set of debugging tools.
Perhaps this will help with how to specifically use debug.print and breakpoints. Ctrl+G will show immediate window to view the debug.print results.

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)

            'set breakpoint on line below

    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]));"
        
        Debug.Print "Faulty strSQLGIL " & strSQLGIL


        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]));"


        Debug.Print "Faulty strSQLCS " & strSQLCS
        
        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]));"


        Debug.Print "Inventory strSQLGIL " & strSQLGIL


        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]);"


        Debug.Print "Inventory strSQLCS " & strSQLCS
        
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQLGIL
        DoEvents
        
    End If
    
        'set another breakpoint on line below
        
    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

Hi Dave, I have got the debug working now, but setting the break points, when it comes to the debug.print section, it goes straight past with nothing in the immediate window. I am getting info in the locals window, but lists every element of the database, which would be time consuming to look for detail in there!

[Welshgasman]

Hi Dave, I have got the debug working now, but setting the break points, when it comes to the debug.print section, it goes straight past with nothing in the immediate window. I am getting info in the locals window, but lists every element of the database, which would be time consuming to look for detail in there!

If it does that, then it is NOT getting to that line?

Use F8 !

[Lesg]

OK guys, thanks for all your input, I have been able to resolve the problem now and it was due to the cbostocktype values being wrong, updating to the correct values has resolved and the stock control is now working again

[orange]

Lesg,

You should have some message -whether driven from some flag you can set, or included in the code- to indicate that the Process has completed. Could be a message for successful completion and another within your error handling to indicate an error has occurred.
I mention this because your testing/reporting wasn't showing anything (according to your posts).

The locals window shows everything involved -- you can watch values change as you step through the code etc.
Good luck.

You'll have to resolve how/why cbostocktype values were wrong in an operational procedure.