SQL Statement Count Total Quantity

[flair]

You are doing a

Do Until rst.EOF

Then inside this you are again defining

DisplaySQL = "SELECT * FROM DataGrid"
        Set rst = oSQL.Execute(DisplaySQL)

This would reset the recordset. The .Movenext would no more be effective. Don't set it again.

[carlovfrimily]

How about this?

Private Sub DisplayGrid ()
i = 0
fg.Clear
    sSQL = "SELECT * FROM PackQty_View "
    Set oRs1 = oShip.Execute(sSQL)
    Qty = oRs1!Qty
    ModelDefined = oRs1!ModelDefined
 
   DisplaySQL = "SELECT Model,TargetQty,Exceed FROM DataGrid"
   Set rst = oSQL.Execute(DisplaySQL)
 
    fg.ColWidth(0) = 2900
    fg.ColWidth(1) = 1600
    
    fg.ColAlignment(0) = flexAlignLeftCenter
    fg.ColAlignment(1) = flexAlignLeftCenter
 
    Row = 0
    
    fg.TextMatrix(Row, 0) = "Model"
    fg.TextMatrix(Row, 1) = "Total Quantity"
    
    Row = Row + 1
    
Do Until rst.EOF
 
    fg.Rows = rst.RecordCount + 1
    fg.TextMatrix(Row, 0) = rst!Model
  
 
    If fg.TextMatrix(Row, 0) <> oRs1!ModelDefined Then
                Qty = 0
            Else
                Qty = oRs1!Qty
            End If
           
 
        TargetQty = rst!TargetQty
        Exceed = rst!Exceed
        sTotal = Qty + Exceed
 
        fg.TextMatrix(Row, 1) = sTotal
 
        If fg.TextMatrix(Row, 1) < TargetQty Then
         fg.Row = Row
         fg.Col = 1
         fg.CellBackColor = vbRed
 
        ElseIf fg.TextMatrix(Row, 1) >= TargetQty Then
         fg.Row = Row
         fg.Col = 1
         fg.CellBackColor = vbGreen
        End If
 
        Row = Row + 1
        rst.MoveNext
   
Loop
Exit Sub
End Sub

[carlovfrimily]

May I know where is my mistakes?

[carlovfrimily]

Do Until rst.EOF

fg.Rows = rst.RecordCount + 1
fg.TextMatrix(Row, 0) = rst!Model
fg.TextMatrix(Row, 1) = rst!TargetQty
' Row = Row + 1

If fg.TextMatrix(Row, 0) <> ModelDefined Then 'How to write the coding to compare with the two row of ModelDefined instead of comparing only with the first row of ModelDefined here? Qty = 0
Else
Qty = oRs1!Qty
End If

TargetQty = rst!TargetQty
OnHand = rst!OnHand
sTotal = Qty + OnHand

fg.TextMatrix(Row, 2) = sTotal

If fg.TextMatrix(Row, 2) < TargetQty Then
fg.Row = Row
fg.Col = 2
fg.CellBackColor = vbRed

ElseIf fg.TextMatrix(Row, 2) >= TargetQty Then
fg.Row = Row
fg.Col = 2
fg.CellBackColor = vbGreen
End If

Row = Row + 1
rst.MoveNext

Loop