Checking Equality

[Meilu]

Hi,
My question is sort of a Query and a programming question.

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.

So I wrote the following to run on the "On Exit" even
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"
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!

I'd appreciate ANY HELP!

Thanks,
Meilu

 

[Allen Browne]

If at all possible, consider dropping the Qty field from tblColorOrders. It
sounds like it must always be the sum of the quantities in the detail
records, and if not it would be an error? If so, you are only making
unnecessary problems for yourself by storing it instead of
calculating/reading the total when needed.

If you want to do it anyway, use the AfterUpdate event procedure of the
subform to get the total:
Private Sub Form_AfterUpdate
Dim strWhere As String
Dim lngQty As Long
strWhere = "LinkID = " & Me.Parent!LinkID
lngQty = Nz(DSum("Qty", "tblColorOrderDetails", strWhere), 0)
Me.Parent.Qty = lngQty
End Sub

You will need to use the subform's AfterDelConfirm event as well:
Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then
Call Form_AfterUpdate
End If
End Sub

(That's why we used Me.Parent!LinkID. There may be no record in the subform
after a delete.)

 

[Meilu]

Thanks for the input

The reason I check the Sum(qty) from details against the
Qty value stored in the parent form is a safe guard
against errors in data entry.

So I cannot simply take the Sum(qty) from the child form
and put it into the Qty of the Parent form.

Can you think of a way to do this?

Thanks!
Meilu

 

[Allen Browne]

If you prefer a message box:


Private Sub Form_AfterUpdate
Dim strWhere As String
Dim lngQty As Long
strWhere = "LinkID = " & Me.Parent!LinkID
lngQty = Nz(DSum("Qty", "tblColorOrderDetails", strWhere), 0)
If Me.Parent.Qty <> lngQty Then
MsgBox "Fix it!"
End If
End Sub

 

[Meilu]

Thanks for all your help,

I just need you opinion on something. I was
wondering ... Why does my original code not work? Am I
misunderstanding a basic concept? In a previous version
of this project ... the approach I posted worked! (but
then other stuff didn't work ... so I junked that
version..)

Also, the problem with putting the code you suggested at
the Form_update event is that the msg box would
continuously pop up until the Sum was correct ... that's
not very practical...

And I have been asked to "force" the user to enter in a
legitimate amount before allowing them to "leave" the
form. And for some reason the DoCmd.CancelEvent command
doesn't really help.

I know that my problem is becoming a programming one, so
I will also repost my question on the Programming
newsgroup

Thanks Again for your help!
Meilu

 

[Allen Browne]

You cannot force the user to enter the correct quantity in the subform
before they can save the record in the main form, because you must save the
main form record before the subform records can be created.

You could run a test in the Unload event of the main form to locate any
records that don't match by running a query. However, this would really slow
down the closing of the form, and there could be several records where the
sum is not correct.