Loop recordset with IF greater than?

[aron.ridgway]

Im trying to create a record set that compares a quantity value in the recordset to a Value in a temporary table that holds the most recent remaining quantity. I have the following code in the after update of a text box, but it does not trigger. I want it to run after a value is entered into the text box. Is the code wrong or the location im putting it incorrect?

Private Sub txtQty_AfterUpdate()
Dim rs As DAO.Recordset

DoCmd.OpenQuery "qryQuantitySoFar"
Set rs = Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordsetClone
   With rs
      Do While Not rs.EOF

      If rs("Qty") > Nz(DLookup("[RemainingQty]", "tblQtySoFarTEMP", "[OrderDetailPK]= " & rs![OrderDetailFK])) Then
     MsgBox "The Quantity received is greater than the outstanding quantity? Would you like to update the original order quantity?"
     End If
       rs.MoveNext
      Loop
  End With
Set rs = Nothing
End Sub

 

[pr2-eugin]

What exactly are you trying to do again? I am a bit confused.

 

[BlueIshDan]

First off, try to remember that when you use a with [object] statement, you don't have to refer to that object within its scope.

Second, Is [OrderDetailFK] a text or number value?

Third, Here is your code indented:

Private Sub txtQty_AfterUpdate()
    Dim rs As DAO.Recordset
    DoCmd.OpenQuery "qryQuantitySoFar"
    
    Set rs = Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordsetClone
    
    With rs
    
        While Not .EOF
            
            If !Qty > Nz(DLookup("[RemainingQty]", "tblQtySoFarTEMP", "[OrderDetailPK]= " & !OrderDetailFK)) Then
                MsgBox "The Quantity received is greater than the outstanding quantity? Would you like to update the original order quantity?"
            End If
            
            .MoveNext
            
        Wend
        
    End With
    
    Set rs = Nothing
End Sub

 

[BlueIshDan]

Also, are you getting any errors?

 

[aron.ridgway]

Thank you for the replies, what i want to do is check the qty in the text box with a value in a temp table( this holds the remaining qty for each item)

I want to Ask a vbyesno message eventually that asks if they want to update the original order Qty if the total received qty is more than the original order.

BLue i have tried the updated code, i receive no errors but it is not triggering any code either?

thanks

 

[BlueIshDan]

Tell me this, are you only trying to parse one, or all, record's quantities.

 

[aron.ridgway]

Its more than one but there is probably a maximum of 20 records at one time in the temp table, that i want to parse through?

 

[aron.ridgway]

I have updated the qty see bellow, i can get the code to trigger once. Just thinking about it, i dont actually need it to loop through i only need it to look at the current value!! and if the condition is true to run an update query.

 

[BlueIshDan]

Run this, it will throw a message box message that tells you how many records have returned with in the recordset.

Private Sub txtQty_AfterUpdate()
    Dim rs As DAO.Recordset
    DoCmd.OpenQuery "qryQuantitySoFar"
    
    Set rs = Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordsetClone
    
    With rs
        MsgBox .RecordCount
        While Not .EOF
            
            If !Qty > Nz(DLookup("[RemainingQty]", "tblQtySoFarTEMP", "[OrderDetailPK]= " & !OrderDetailFK)) Then
                MsgBox "The Quantity received is greater than the outstanding quantity? Would you like to update the original order quantity?"
            End If
            
            .MoveNext
            
        Wend
        
    End With
    
    Set rs = Nothing
End Sub

 

[aron.ridgway]

thank you for your help, i managed to get it to work with this code

Private Sub txtQty_AfterUpdate()
Dim areYouSure As String
Dim mySQL As String
Dim mySQL2 As String
Dim minusNum As Integer
If Me.Qty > Nz(DLookup("[RemainingQty]", "tblQtySoFarTEMP", "[OrderDetailPK]= " & Me.OrderDetailFK)) Then
     areYouSure = MsgBox("The Quantity received is greater than the outstanding quantity? Would you like to update the original order quantity?", vbYesNo, "Update Order Qty")
     
If (areYouSure = vbYes) Then
minusNum = Me.txtQty.Value * -1
mySQL = "UPDATE [tblOrderDetail] SET [QTY] = " & Me.txtQty.Value & " WHERE [OrderDetailPK] = " & Me.OrderDetailFK & ";"
DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.OpenQuery "qryQuantitySoFar"
DoCmd.SetWarnings True
Forms!frmReceive!sfrmReceiveDetail.Form.Requery
ElseIf (areYouSure = vbNo) Then
Exit Sub
End If
End If
End Sub

 

[BlueIshDan]

please tab lol. It is a great thing to do!

 

[pr2-eugin]

SPAM user : Cafroline reported !