Step Through Queries in Code to find errors

[Lesg]

Hi Guys,

I have a problem with a stock system I have been assigned to check and update some details, however, when I use the system, no stock elements are being updated and just wondering if anyone can point me in the direction of a tutorial or advise how I can step through the code as it runs via the vba code window and allow me to see why its not working?

[Welshgasman]

Alt + F11 to get to the vba window
Select an object in the left window with a double click
Set a breakpoint by clicking in the vertical grey bar on the left of the code
Run the form or F5 if code module. F8 will step through line by line
Hover over variables to see what the or Debug.Print the variable in the immediate window (Ctrl +G)
Breakpoint on a line is BEFORE that line gets executed.

HTH

[orange]

Lesg,
Are you really dealing with a Query? Or some vba code/function(s)?
Paul has given you the steps for stepping through code. There are examples in the Debugging link in my signature.

[Lesg]

Lesg,
Are you really dealing with a Query? Or some vba code/function(s)?

Hey Orange,

I am checking these queries created in the 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?

[orange]

Can you post a copy of the database with only a few records?
You are working on a test system --some copy from operational database, right?
Other option is to add a few Debug.Print statements to understand the actual logic flow.

eg Debug.print " cbostocktype :" & cboStocktype

You can place place Breakpoints and check the local windows for variables and values while stepping with Fn 8.

Are you sure the test you are making should update tblInventory etc?

[Lesg]

Not easy to post a copy, the this is an SQL backend with Access frontend. it is backup version of full system on a dev server.

Yes, the project is to add/update stock items to the inventory table, plus I have other forms to produce orders that will deduct stock as orders are processed, will check that one after I get this working!

Will try and get your suggestions working and see how I get on, this is what I need to understand so I can check this code, thanks for the help

[Lesg]

I have tried the suggestion as advised, but nothing happens, the whole process goes through and does not output anything in the locals window and does not stop at the breat point I have added, see image:



If I try just running in the vba code window, it just opens another window for a macro?... am I missing something here:

[Welshgasman]

Put a breakpoint at the very first executable line (not Dim statements) and F8 from there.
If it does not reach a breakpoint, then you need to look at the code before that breakpoint, so start at the top and use F8.

Slow I admit, but you do get to see exactly what is happening and also be able to check values.
This method is brilliant for what I call 'silly errors'

[orange]

Try Paul's suggestion --breakpoint on first executable statement. Then F8 to proceed line by line.

Your debug.print may not have worked since it was within an IfElse block which may have been bypassed depending on the value of cboStockType.

You can have several
debug.print "About to Insert into tblfaultyDetail"
debug.print "After Insert into tblfaultyDetail"

You pick some key point in your process and create the Debug.Print to follow the flow.

[davegri]

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

[Minty]

In addition to the above comment out this line

DoCmd.SetWarnings False

As if there were any errors you wouldn't see them anyway.
It will also allow you to see how many rows are (Aren't?) being updated

[Lesg]

Hi Guys,

Thanks for your feedback and suggestions on this, however, despite your suggestions, this coide just runs straight through and does not recognise and debug or break point, all that happens is, with the vb code window open and when I process and goodsin and click the cmdClose button that this code is set to, the left column window refreshes, but nothing stops or shows in the locals window.

If I have just the code window open and click F8 nothing happens, so no idea why none of this is working for me?


Looking online, can I only debug a 'Module' as this is an event that is happening on a forms onevent ? and if it has to be a module, how to I convert this to a module, I have tried the setting on design that says convert to vba, but despite it saying it has worked, I am not seeing anything added to a module?

[orange]

Lesg,

Watch this video by Steve Bishop. He'll show you exactly how to debug vba code.

[Lesg]

Lesg,

Watch this video by Steve Bishop. He'll show you exactly how to debug vba code.

Thanks for that, I have checked that and seem a few and I cannot understand why my debug is not working, is there a way to check this?

My access type is .accdb, 64bit, I can click the compile and it compiles with no errors.. seriously confused here??

OK, got debug to work now, found out that in current database options, use special keys was de-selected, hence debug was not working...I can press on now

[orange]

The 64 bit is not relevant.
Are you saying you have:
-inserted some debug.print statements in your code and/or
-placed breakpoint(s) on different executable lines and
-nothing gets printed in the immediate window and
-the code does not recognize a(ny) breakpoint

How do you know for sure that the code is actually working? Put some comment/msgbox as a test withing the code and ensure it is processed.
You may have to work with a test set up and a transaction or 2 that you can follow.

You may have some sort of corruption, but that's sort of a down the road point.