This sounds easy to me, but I can't make it work. I have found topics that sound similar, but are not really about the same thing.
I have an Order Entry form that ties to my main Order table. On the form I have a subform tied to the subrecords of the Order table and stored on a Receiving Table.
In the subform I have fields for a receive date and received quantity. In the Subform header I have a calculated field that sums up all the received quantities. (subform and form linked via Order ID).
On the main form I have a Qty field which reflects the total quantity on the order. I have a combo box on the Main Form that stores the selection on the Order table. I want to comapare the Total Received (calculated field in the subform) to the Qty (total quantity ordered field on main form). If they are equal, change the combo box value to "Closed".
Any help will be greatly appreciated. I had some help from that theDBguy(thanks much) on another forum, and thought we had it licked but I was mistaken and no one has returned to my thread.. I most have been looking at the wrong field (one we made for testing) because I thought it was working. I can not get the results to populate my Order Status combo Box.
Tested it out with a field to make sure we could get it to recognize the calculated field in the subform: =IIf([Qty]=[Receiving Table Sub].[Form]![Total Received], "Closed", [Order Status]) That worked so I made an On Current event :
If Me.[Order Status] & "" = "" Then
If [Qty]=[Receiving Table Sub].[Form]![Total Received] Then
Me.[Order Status] = "Closed"
End If
End IfThis did not work. I want this field to stay the default value of "Open" if the Total Received is less than QTY ordered. The current event did Not change my Order Status combo box value.
Thanks in advance for any help.