Solved Pass order status from subform back to main form (1 Viewer)

debsamguru

Member
Local time
Today, 08:59
Joined
Oct 24, 2010
Messages
82
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

Super Moderator
Staff member
Local time
Today, 04:59
Joined
Jan 23, 2006
Messages
15,361
Can you show us the tables and relationships related to this database application?
 

debsamguru

Member
Local time
Today, 08:59
Joined
Oct 24, 2010
Messages
82
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)
 

Attachments

  • database.zip
    2 MB · Views: 299

debsamguru

Member
Local time
Today, 08:59
Joined
Oct 24, 2010
Messages
82
1645037464493.png
 

debsamguru

Member
Local time
Today, 08:59
Joined
Oct 24, 2010
Messages
82
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

Grover Park George
Local time
Today, 01:59
Joined
Nov 25, 2004
Messages
1,775
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

Member
Local time
Today, 08:59
Joined
Oct 24, 2010
Messages
82
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

Grover Park George
Local time
Today, 01:59
Joined
Nov 25, 2004
Messages
1,775
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

Member
Local time
Today, 08:59
Joined
Oct 24, 2010
Messages
82
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
 

Users who are viewing this thread

Top Bottom