Coding a sum in vba subform

[Guest]

I have a form "POReciept" where I am trying to receive line items against a PO.
There are 3 tables, PO, POItems, POReciepts
There are 2 subforms, 1 showing the line items and the other showing dates
received.
Users open the form where it asks for PO#, opens fine, subforms have all
correct data...
when the user highlights the line item in subform1, he can then enter the
quantity received in subform 2...
I want to error check that when the amount entered in subform2 (QTYrcvd) is
totaled it must be equal to or less than the amount ordered in the subform1
(as QTY).
I have tried a hidden (and unhidden) textbox in sub2 but can't seem to get
it (the Total field) to update after an amount is entered, it won't update
until i try to enter another record.
In other words in subform2 I have...
If (Total > Forms!POReceipt!Subform1!QTY) Then
Msgbox "etc..."
Me.undo

This works fine, but it works too late, and I think I have tried it
everywhere (BeforeUpdate, AfterUpdate, LostFocus, etc.) relating to the field
QTYrcvd
All I want to do is make sure that they can't receive more stuff than we
have ordered.
Please help the frustrated.

 

[Guest]

I have tried a hidden (and unhidden) textbox in sub2 but can't seem to get
it (the Total field) to update after an amount is entered, it won't update
until i try to enter another record.

How are you summing values to put into this control?

In other words in subform2 I have...
If (Total > Forms!POReceipt!Subform1!QTY) Then
Msgbox "etc..."
Me.undo

This works fine, but it works too late, and I think I have tried it
everywhere (BeforeUpdate, AfterUpdate, LostFocus, etc.) relating to the field
QTYrcvd

I would suggest using the BeforeInsert on the form to sum both the value of
the amount textbox and the sum of pre-existing records, maybe using DSum.
Something like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
If CSng(Me.txtAmountReceived) +
DSum("AmountReceived","POReceipts","PONum = " & Me.txtPoNum) >
Forms!POReceipt!Subform1!QTY Then
MsgBox "Blah"
Cancel = True
End If
End Sub

Barry

 

[Armando Blanco]

sorry

you cant do this with microsoft access 2003.

you must upgrade to microsoft access 2004:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html


HTH

Armando Blanco
Microsoft MVP

 

[Jeff Boyce]

Disregard the "cant do this with Access" message. It is SPAM (or worse).
Follow the link at your own peril...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks Barry, I checked out your suggestion and BAM! it hit me...the (+)
plus sign on your first line...
Just add the quantity they are trying to receive to the total as follows...

Private Sub QTYRcvd_BeforeUpdate(Cancel As Integer)
If ((Total + QTYRcvd) > Forms!POReceipt!Subform1!QTY) Then
MsgBox "You cannot receive more product than we ordered, please try
again.", vbOKOnly
Cancel = True
Me.Undo
End If
End Sub

I knew there was an easy solution, thanks a million, this was killing me!

P.S. The summing was taking place in the footer of the subform, but it
didn't seem to make a difference as I had tried multiple positions and still
had the same results.

 

[Guest]

Thanks Jeff, I'm a frequent browser and have seen it before, but thanks for
the heads up.