Event Proceedure From Texbox

[Glue]

My first post: First off thanks to all those that answer questions on this forum. I’ve spent hours upon hours finding solutions and learning programming. I’d be stuck without you, so a big THANK YOU!!!!

I’ve spent a ton of time looking into this and haven’t found an answer so thought I’d finally post my own question:

I have a form with a continuous subform. The Subform has QtyOrdered Field(Entered on a different form), and QtyReceived Field (Entered on this continuous subform). What I want is for a third form to popup when Total Qty ordered – total QtyReceived = 0. I tried a query but ran into a problem where the table could not update. (Spent hours looking into this one as well, and couldn't find a solution)

As a work around I have an unbound text box named [QtyOutstanding] in the Subforms footer with a control source as follows: =Sum([QtyOrdered]-[QtyReceived])

On my main form I have an unbound text box named QtyZeroCheck with a control source as follows: =[ReceivingFormSubF].[Form].[QtyOutstanding]

Currently I’m simply trying to get anything to happen when QtyZeroCheck = zero.

I’ve put different types of code in different places trying to get a msgbox to popup if QtyZeroCheck = 0. Here is the most simple, where nothing at all happens.

Private Sub Form_AfterUpdate()
If me.QtyZeroCheck = 0 then
Msgbox “Finally working”
End if
End Sub

Any help for a frustrated rookie would be greatly appreciated.:banghead:

 

[Isskint]

I assume the main form display one product/order at a time and the subform is used to record seperate deliveries of that product/order?

If so, you could run the code in the OnCurrent event of the main form (this would trigger when the record changes) or the AfterUpdate event of the textbox on the subform that the user inputs the receipt quantites.

The way to get the sum would be the DSUM() function, EG DSUM("[QtyReceived Field]","[ReceiveTable]","[product/order]='" & me.Product/order 7 "'")

 

[Glue]

Yes, we will often have multiple item types on one order arriving separately or Items that will be back ordered and arrive at a different time.

So putting that into an if statement so it only triggers a Msgbox "for now" when all Quantities have arrived: (this is bugging out everywhere, but am I on the right track?)

If DSum("[QtyReceived]","[OrderItems]",[OrderID]='"& me.OrderID"'") - DSum("[QtyOrdered]","[OrderItems]",[OrderID]='"& me.OrderID"'") = 0 Then
Msgbox "Testing"
End If

Or maybe more like this?

If DSum("[QtyOrdered]-[QtyReceived]", "[OrderItems]", [OrderID] = Me.OrderID) = 0 Then

 

[Isskint]

yes that looks to be on the right lines.
Just a couple of things:
You are missing quote marks in the last part of the DSUM() function and Is orderID numeric or text? if numeric them you will not need the single quotes:

If DSum("[QtyReceived]","[OrderItems]",[OrderID]='"& me.OrderID"'") - DSum("[QtyOrdered]","[OrderItems]",[OrderID]='"& me.OrderID"'")

becomes

If DSum("[QtyReceived]","[OrderItems]","[OrderID]="& me.OrderID) - DSum("[QtyOrdered]","[OrderItems]","[OrderID]="& me.OrderID)

Now if you put htis code in the AfterUpdate event of the textbox the user types the recieved quantity in, you should be in business.

 

[Glue]

Genius!!!! Thank you so very much.

For lurkers like me here is what finally worked thanks to Isskints:

Private Sub Form_AfterUpdate()
If DSum("[QtyReceived]", "[OrderItems]", "[OrderID]=" & Me.OrderID) - DSum("[QtyOrdered]", "[OrderItems]", "[OrderID]=" & Me.OrderID) = 0 Then
MsgBox "Finally working"
End If
End Sub

 

[Isskint]

Glad to help