Requery After Apend Query

[sdebo2]

I have two subforms (RECEIVING_SUBFORM and RECEIVED_SUBFORM) on a main form (RECEIVING). The form is used to record receipt of expecting orders. When a partial order is received I want to add a new record with the difference of what was ordered and what was received then requery both sub forms, but I am having trouble.

So far I have an append query that runs from the RECEIVING_SUBFORM on the update record event, using an if statement to determine if the Quantity receive equals the quantity order. Then on the same subroutine I update the current quantity ordered field for the current record. This all works correctly. Now I want to requery both subforms so they reflect the current data. When I do this I get an error (The macro or function set to the BeforUpdate or ValidationRule property for this filed is preventing Microsoft Access from saving the date in the field). The debug takes me to the me.requery line. I have tried to use the command Forms!Receiving!RECEIVING_SUBFORM.Requery instead but I get the same result. My current code is below, and I do not have anything set to run BeforeUpdate or as a ValidationRule.


Private Sub Form_AfterUpdate()

If (Me.QUANTITY_RG <> 0 And Me.QUANTITY_RG <> Me.QUANTITY_PO) Then
ReceivePartail
End If

Me.Requery
Forms!Receiving!RECEIVED_SUBFORM.Requery

End Sub

Private Sub ReceivePartail()
Dim stDocName As String
Dim stDiff As Integer

stDiff = Me.QUANTITY_RG
stDocName = "ReceivingPartialUpdate"
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
Me.QUANTITY_PO.Value = stDiff

End Sub

Thanks for your help.

 

[pbaldy]

This is a guess, but it looks like that query is being left open, which may affect the code. Try closing it after you get your value from it.

 

[sdebo2]

The query being used is an apend query, so the query window never appears, How would I make sure the query is closed?

 

[pbaldy]

I didn't realize it was an append query; you're right, it will close itself. Can you post a sample db with the relevant objects in it?

 

[sdebo2]

Attached is a partial database. Go to form Receiving, enter 715007 in the Vendor Number Drop Down list, and click requery. This will populate the Receiving List. The receiving list contains Items that we are expecting, so when an Item comes in we find the Item and enter the quantity_RG, and the date received, once you update the record the system will check if the Quantity_RG is = to the Quantity_PO, and if they are unequal create a new record for the Items remaining to be received. Thats where I get the error.

Thanks for all your help