Step through Queries to find errors help

[Lesg]

Hi Guys,

I have an issue when I am updating an inventory table (tblInventory), from a Purchase Order (PO), in that if an Item of the PO is not currently in tblInventory, I use the following code to add the item:

    If 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"

However, I found that items that are not currently in tblInventory, items are not being added as I had expected, so I want to be able to stop the process and check why the items are not being added?

qryTemp is an append query, that should add any items of the PO to tblInventoryDetail, which in turn updates the qty of items in tblInventory.



Hope that makes sense and any advise is appreciated.

[Welshgasman]

You would need to read the recordset again, I would have thought. It is not dynamic. At least I do not think so , despite that dbseechanges unless you are using SQLServer, which I have never used.

https://www.google.com/search?q=dbse...hrome&ie=UTF-8

You could try the RecordsAffected property?

https://learn.microsoft.com/en-us/of...d-property-dao

Plus I thought you needed Execute on updatable queries?

[Lesg]

You would need to read the recordset again, I would have thought. It is not dynamic. At least I do not think so , despite that dbseechanges unless you are using SQLServer, which I have never used.

https://www.google.com/search?q=dbse...hrome&ie=UTF-8

You could try the RecordsAffected property?

https://learn.microsoft.com/en-us/of...d-property-dao

Plus I thought you needed Execute on updatable queries?

Thanks for your feedback, the tblInventorydetail is a 'local' access table, that is cleared once the update has cleared. tblInventory is however, an SQL table, but from my testing, I am seeing the the tblInventoryDetail is created correctly, its just the qryTemp that is not being executed. I have tested the qryTemp once tblInventoryDetail is created??

Can you clarify what you are saying about the 'Execute on updatable Queries' are you saying I should change line:

ElseIf rsInventory.NoMatch = True Then
                     DoCmd.OpenQuery "qryTemp", acViewNormal ' change this ?

[Lesg]

So after extensive testing to identify the issue, it looks like this is caused by field 'SumofQty' in tblInventory for products that were not updating, if they existed in the table, was dues to having a value of null.

I am not sure how these got a null value, so need to investigate further on that.

But then also need to figure out why the qryTemp is not adding any items to tblInventory via the if statatments:

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"

[Welshgasman]

Start indenting your code properly.
That helps a lot when trying to identify logic errors.

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

Next step is to walk your code and see what it is doing, not what you think it s doing.
Try the queries on their own in the QBE window