This code only works when stepping through

rossco
Lounger
Posts: 32
Joined: 11 Mar 2010, 04:08

This code only works when stepping through

Post by rossco »

I've set up an order form and subform to take orders and details and for this case there will only ever be two detail records per order. I also need to have the order details display on the parent form so it's clearer for warehouse handlers so I created code to transfer the first and last order details to two parent form fields named "warehouse1" and 'warehouse2".

However, the code works with a breakpoint and stepping through but it seems to be ignored if there's no breakpoint.

Code: Select all

Private Sub Form_AfterUpdate()

    Dim strSQLF As String, strSQLL As String
    Dim db As dao.Database
    Dim rstF As dao.Recordset
    Dim rstL As dao.Recordset
    
    Set db = CurrentDb()
    
    If Me.OrderDetCount = 1 Then
        
        strSQLF = "SELECT tblOrderDetails.OrderID, First(tblOrderDetails.Quantity) AS FirstOfQuantity, First(tblProduct.Product) AS FirstOfProduct" _
                & " FROM tblProduct INNER JOIN tblOrderDetails ON tblProduct.ProductID = tblOrderDetails.ProductID" _
                & " GROUP BY tblOrderDetails.OrderID" _
                & " HAVING (((tblOrderDetails.OrderID)=" & [Forms]![frmOrders]![OrderID] & "));"
        Set rstF = CurrentDb.OpenRecordset(strSQLF, dbOpenDynaset)
        Me.Parent!Warehouse1.Value = rstF!FirstOfQuantity & "  X  " & rstF!FirstOfProduct
    
    ElseIf Me.OrderDetCount = 2 Then
        
        strSQLF = "SELECT tblOrderDetails.OrderID, First(tblOrderDetails.Quantity) AS FirstOfQuantity, First(tblProduct.Product) AS FirstOfProduct" _
                & " FROM tblProduct INNER JOIN tblOrderDetails ON tblProduct.ProductID = tblOrderDetails.ProductID" _
                & " GROUP BY tblOrderDetails.OrderID" _
                & " HAVING (((tblOrderDetails.OrderID)=" & [Forms]![frmOrders]![OrderID] & "));"
        Set rstF = CurrentDb.OpenRecordset(strSQLF, dbOpenDynaset)
        Me.Parent!Warehouse1.Value = rstF!FirstOfQuantity & "  X  " & rstF!FirstOfProduct
        
        strSQLL = "SELECT tblOrderDetails.OrderID, Last(tblOrderDetails.Quantity) AS LastOfQuantity, Last(tblProduct.Product) AS LastOfProduct" _
                & " FROM tblProduct INNER JOIN tblOrderDetails ON tblProduct.ProductID = tblOrderDetails.ProductID" _
                & " GROUP BY tblOrderDetails.OrderID" _
                & " HAVING (((tblOrderDetails.OrderID)=" & [Forms]![frmOrders]![OrderID] & "));"
        Set rstL = CurrentDb.OpenRecordset(strSQLL, dbOpenDynaset)
        Me.Parent!Warehouse2.Value = rstL!lastOfQuantity & "  X  " & rstL!lastOfProduct
    
    Else
    End If
    
    db.Close
    Set db = Nothing
    Set rstF = Nothing
    Set rstL = Nothing
    
End Sub
What could be stopping normal operation?

Thanks

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: This code only works when stepping through

Post by HansV »

I don't know why that happens, but you could try replacing [Forms]![frmOrders]![OrderID] with Me.Parent!OrderID
Best wishes,
Hans

rossco
Lounger
Posts: 32
Joined: 11 Mar 2010, 04:08

Re: This code only works when stepping through

Post by rossco »

Thanks for your suggestion - unfortunately no joy! Access is a nuisance with these random failings. I'll try a different method but it's frustrating to put time and effort into a solution that should work. Sorry - just venting.

User avatar
Carol W.
3StarLounger
Posts: 390
Joined: 26 Jan 2010, 16:02
Location: Las Vegas, NV

Re: This code only works when stepping through

Post by Carol W. »

If Hans doesn't know the answer, I certainly don't. However, when quirky things like this happen to me, I try compiling the code. Sometimes it works and sometimes it doesn't.

Just a thought.
Carol W.

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: This code only works when stepping through

Post by HansV »

If you wish, you can create a stripped down copy of the database, zip the copy and attach the zip file to a reply (max. file size 250 KB).
Best wishes,
Hans

rossco
Lounger
Posts: 32
Joined: 11 Mar 2010, 04:08

Re: This code only works when stepping through

Post by rossco »

I agree Carol and let me be the first to say that Hans has saved my bacon countless times and I apologise profusely if there was any hint about taking a shot at him. I have nothing but the greatest respect and always will have. My comment was purely about Access' unexpected strangeness, which I suspect maybe even Microsoft could not account for.

Yes I shall try the recompiling as I think the database might need a spring clean anyway.

Thanks for the offer Hans however I've created an alternative method to get the desired results.

As always, many thanks for your help!

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: This code only works when stepping through

Post by HansV »

I didn't interpret your reply as "taking a shot at me" at all!
Best wishes,
Hans

User avatar
Carol W.
3StarLounger
Posts: 390
Joined: 26 Jan 2010, 16:02
Location: Las Vegas, NV

Re: This code only works when stepping through

Post by Carol W. »

rossco,

It never even occurred to me that you were "taking a shot" at Hans. The thought never crossed my mind. He's saved my bacon too -- too many times to count! This is the problem with the written word, as opposed to the spoken word.

I was only suggesting that compiling might reveal something that might otherwise not be obvious. I hope it works for you.
Carol W.

rossco
Lounger
Posts: 32
Joined: 11 Mar 2010, 04:08

Re: This code only works when stepping through

Post by rossco »

Thanks. I agree with interpreting the written word. I was being overly cautious. All good.