Setting a flag on a subform as a result of a calculation on it's own subform.

[DaveUssell]

Thank you for being here.

I've bashed my head without help so far, but I'm rapidly running out of time and could use a hand. I don't think it should be too difficult for an experienced Access person.

My structure is I have a "client" form with an "order" subform with an "order details" subform to that.

Client
>Order
>>Order details

The "Order details" subform calculates how many items are yet to be picked up and transfers that number to it's parent subform "Order". One of the fields of the "Order" subform is "Order complete". The "Order complete" checkbox should be set as soon as the value transferred from the "Order details" subform becomes zero and cleared if it is no longer zero.

I don't care if the "Order complete" field on the "Order" subform is activated from the value in the "Order details" subform (=Count([Picked Up])+Sum([Picked Up])) {preferred for elegance} or from the temporary transferred value in the subform "Order".

I've tried lots of things and now need some advice. It takes a village to raise a programmer.

Thanks,
Dave Ussell

[MMcCarthy]

Thank you for being here.

I've bashed my head without help so far, but I'm rapidly running out of time and could use a hand. I don't think it should be too difficult for an experienced Access person.

My structure is I have a "client" form with an "order" subform with an "order details" subform to that.

Client
>Order
>>Order details

The "Order details" subform calculates how many items are yet to be picked up and transfers that number to it's parent subform "Order". One of the fields of the "Order" subform is "Order complete". The "Order complete" checkbox should be set as soon as the value transferred from the "Order details" subform becomes zero and cleared if it is no longer zero.

I don't care if the "Order complete" field on the "Order" subform is activated from the value in the "Order details" subform (=Count([Picked Up])+Sum([Picked Up])) {preferred for elegance} or from the temporary transferred value in the subform "Order".

I've tried lots of things and now need some advice. It takes a village to raise a programmer.

Thanks,
Dave Ussell

Firstly, i need to know more about the makeup and structure of the order details subform and where this 'value' is stored and/or what it is calculated on exactly.

Secondly, there has to be some trigger to change the order complete status. By that I mean something happens that causes the check to be run on the value.

Can you post the code etc. that you are currently using for all these procedures.

Mary

[DaveUssell]

Thank you for your response.

Not being very sophisticated there really is no code.

This is what happens. There are three tables involved "Order details", "Orders" and "Customers" with the appropriate one of many links. The form and subforms based upon these tables are "subfrmOrderDetails", "subfrmOrders" and "Customers" respectively. All of the data manipulation works fine however, I need to print reports that sometimes only address incomplete orders and other times only completed orders. Therefore, they need to be differentiated. To do this I have a field in the "Orders" table called "Order complete".

In the "Order details" table there is a checkbox that maintains the picked up status. It is checked when each line item (record) is picked up since they may, or may not, all be picked up at the same time. In the form footer of "Order details" there is a local field called "PickedUpCount" which contains the formula =Count([Picked Up])+Sum([Picked Up]) as it's control source. By design this value is zero when no more items are available for pickup.

As a temporary test "probe" on the "subfrmOrders" I have placed a temporary text box named "ToBePickedUp" with the formula =[subfrmOrderDetails].[Form]![PickedUpCount] for the Control Source. This also works fine and merely indicates how many line items (records) are yet to be picked up. The only part of this that I can't seem to decipher is how to set the "Order complete" field on the "subfrmOrders" when either the "PickedUpCount" on "subfrmOrderDetails" or "ToBePickedUp" on "subfrmOrders" equals zero (they track and are really the same field but it would be more elegant to use "PickedUpCount" on "subfrmOrderDetails" because that would make the other superfluous).

In short we have
Customers
>Orders ----- w/field "Order complete" set/unset when the count below is 0
>>Order details ----- Contains and passes # of items remaining to be picked up

I know I do not speak the language well. If you want to see the beast you can do so at ftp://ascii27.com/Siesels.mdb

Incidentally, I forgot this is implemented in Access 2000 in case it makes a difference.

Sincere thanks,
Dave

[MMcCarthy]

Dave,

I've made some changes and it should work for you now. Let me know if you have any problems or don't understand what I've done. The calculated fields are gone and there is an event trigger on the checkbox in the order details subform written in vba.

Siesels_Revised.mdb

Mary