Having Trouble comparing Values

[Meilu]

Please Tell me what's wrong?!
I have two tables: tblColorOrders AND a
tblColorOrderDetails
I have two corresponding forms: frmColorOrders ANd
frmColorOrderDetails.

The second being a subform of the first.

ColorOrders has:
ID field, and a Qty Field

ColorOrderDetails connects to ColorOrders via the ID
field. ColorOrderDetails also has a Qty Field.

I've been trying to run a check that would compare the
sum of all the corresponding QTY's of ColorOrderDetails
to that of ColorOrders.
Example:
frmCO. RED Shirts 10000 pcs Japan
frmCODetails: 500 pcs Large
500 pcs Medium
200 pcs Small

I'm just trying to double check that the details of the
order match the general overview. I have been asked
to "force" the user to stay on the form until the
quantities match or the Sum = 0 .


So I wrote the following to run on the "On Exit" event of
frmColorOrderDetails. (It's a little simplified for
testing purposes... )

Private Sub frmColorOrderDetails_Exit(Cancel As Integer)
Dim db As Database
Dim rec As Recordset
Dim strQry As String
Dim strMsg As String
Dim tempQty As Integer

strQry = "Select Sum(tblColorOrderDetails.Qty) AS
[Qty] From tblColorOrderDetails " _
& " Where tblColorOrderDetails.LinkID = " &
Me!RecID

Set db = CurrentDb()
Set rec = db.OpenRecordset(strQry)

If (rec!Qty = 0) Then
If MsgBox("Uh Oh", vbOKCancel, "UH OH") = vbOK
Then
Debug.Print "It's a 0"
DoCmd.CancelEvent
End If
ElseIf rec!Qty <> Me!Qty Then
If MsgBox("CHANGE IT NOW", vbOKOnly, "CHANGE") =
vbOK Then
DoCmd.CancelEvent
End If
Else
Debug.Print "Do Nothing"
End If

End Sub



The problem is that if the Qty in the subform is anything
but '0', the "CHANGE IT NOW" msg box pops up! In Other
words ... trying to compare rec!Qty with MPO!Qty doesn't
work. WHY?!

I'd appreciate ANY HELP!
Meilu

 

[John Spencer (MVP)]

Isn't Me!Qty equal to the quantity of the single current record of the subform?
If you want the sum of the quantities in the subform to equal the sum of the
recorded quantity in the mainform you would need to refer to the value of the
quantity in the main form - FORMS!MPO!QTY .

Questions for your consideration:
1) Why not use DSum to get the sum of the quantities in the subform?
2) Why not set Cancel = True instead of using DoCmd.CancelEvent
3) Why enter the total quantity in the main record at all? It normally would be
calculated from the detail records and then you wouldn't have to worry about a
discrepancy between the details and the summary.