Solved Pass order status from subform back to main form

[debsamguru]

This is more of a logic question than a programming question (potentially).

Status of an order and an order line can be:
1. Active
2. Part-shipped
3. Shipped
4. Invoiced
5, Paid

These are in a StatusT with a description of the status and just the StatusID is attached to the OrderHeaderT record and the OrderItemsT record.

An order can be made up of many order lines which may have a mixture of statuses i.e. Active, Part-shipped or Shipped. The status Part-shipped is allocated when the QuantityRequired - QuantityReceived.

The order lines are displayed in the subform OrderDetailsSubF whilst the main form (with the OrderHeader details) is OrderDetailsF.

What I want to do is when one of the order line status' changes to Part-shipped, I want the OrderHeaderT.StatusID to reflect that. I also want the OrderHeaderT.StatusID to reflect Shipped if all of the items have been shipped.

Can anyone think of the logic that would allow me to do that?

Thanks

 

[jdraw]

Can you show us the tables and relationships related to this database application?

 

[debsamguru]

Here's the database - please note that it's in a state of flux.

The route to where I want this to apply is

Main Menu - Customers (choose DEB) - Orders - Order Details. (Use Customer PO. Number TEST2001 as the best example)

 

[debsamguru]

 

[debsamguru]

OK, so I've worked out the logic - how do I get the main form to reprocess a field once something in the subform has changed? Is it a case of re-querying the form?

 

[GPGeorge]

OK, so I've worked out the logic - how do I get the main form to reprocess a field once something in the subform has changed? Is it a case of re-querying the form?

Use the After update event of the subform to requery the calculated control on the main form.

 

[debsamguru]

So I've got it to display the correct data on the Main Form. However, what I really want to do is change the value of the field (OrderHeaderT.StatusID) with this passed data.

 

[GPGeorge]

So I've got it to display the correct data on the Main Form. However, what I really want to do is change the value of the field (OrderHeaderT.StatusID) with this passed data.

This raises a couple of points.

One, why store the status redundantly in the orderheader table at all? It's always calculable from the statuses of the individual order items. Duplicating that in two places is risky and generally not advisable because it is too easy for the calculated value to go out of synch with the source values.

Two, it should be easy enough to change the calculated control on the main form to a bound control and update its value when you think it needs to be changed. I would not advise that, of course, but it's just a matter of writing a query to update the field and executing the query.

 

[debsamguru]

There are other statuses that are only allocated at OrderHeaderT level (Invoiced and Paid). Will I have to do an sql update to update the OrderHeaderT record? Allocating the data to a bound field on closing the form gives me an error message 'You can't assign a value to this object'.

Private Sub Form_Close()

MsgBox "OrderHeader Status = " & Me.StatusID
MsgBox "StatusSum = " & Me.StatusSum
Me.StatusID.Value = Me.StatusSum

End Sub

 

[debsamguru]

Solved this by adding a query update in the Close statement