Splitting a record.

[Jarryd]

Hello all,

Does anyone know of a good way to split up a record. First let me give you
some background. I have an orders (Orders) table that holds all the main
stuff for the order. An order can only ever be made up of one type of
product, but the amounts can be broken up in to separate shipments from time
to time. So I have a second table (Order_Details) that holds all the
individual shipments' data for an order. The form for the order is a normal
Single_Form. The details are put in a Datasheet subform of the main form.
to make a shipment unique it has an autonumber field. To link the two
tables together you have an autonumber field in Orders called Ord_Det_Lnk
and in Order_Details you have a field called Ord_Lnk and a relationship
between the two. When I created the subfrom I set the relationship between
it and the main form using those fields, so when an order is created (which
is only on one recorde) it gets a number and when you add a shipment to an
order it gets that number from the main Orders form.

So what I want to do is provide a mechanism to split a shipment. So I
create a button that opens a form. On the form I have two unbound text
boxes and another button. I want to put a value in each text box and when I
press the button it must subtract the values in the text boxes from the
values in certain fields of the current record of the datasheet subform. It
must use the diffences to update those fields in the current record and then
add a new record with values in the text boxes for the values of certain
fields and the same Ord_Lnk value from the original record.

Is this possible? Well I know that it is, but what is the code. I am
trying to work out the code. Please help me. I have been stuck on this for
about a week and I don't want to give up now.

TIA,

Jarryd

 

[John Griffiths]

Hello all,

Does anyone know of a good way to split up a record. First let me give you
some background. I have an orders (Orders) table that holds all the main
stuff for the order. An order can only ever be made up of one type of
product, but the amounts can be broken up in to separate shipments from time
to time. So I have a second table (Order_Details) that holds all the
individual shipments' data for an order. The form for the order is a normal
Single_Form. The details are put in a Datasheet subform of the main form.
to make a shipment unique it has an autonumber field. To link the two
tables together you have an autonumber field in Orders called Ord_Det_Lnk
and in Order_Details you have a field called Ord_Lnk and a relationship
between the two. When I created the subfrom I set the relationship between
it and the main form using those fields, so when an order is created (which
is only on one recorde) it gets a number and when you add a shipment to an
order it gets that number from the main Orders form.

So what I want to do is provide a mechanism to split a shipment. So I
create a button that opens a form. On the form I have two unbound text
boxes and another button. I want to put a value in each text box and when I
press the button it must subtract the values in the text boxes from the
values in certain fields of the current record of the datasheet subform. It
must use the diffences to update those fields in the current record and then
add a new record with values in the text boxes for the values of certain
fields and the same Ord_Lnk value from the original record.

Is this possible? Well I know that it is, but what is the code. I am
trying to work out the code. Please help me. I have been stuck on this for
about a week and I don't want to give up now.

TIA,

Jarryd

Rather than popping up an unbound form, losing the user's context,
validating entries, passing info back, acting on it;
wouldn't it be easier to have a field for the quantity shipped.

This means that on a partial shipment you can provide a button to create a
new shipment for the missing amount while the shipped quantity can be passed
to accounts to get some of the money NOW.

ie shipment for 100 widgets and 55 shipped click the split button and the
quantity ordered is changed to 55 and marked for processing by another
department.
Then create a new shipment for the outstanding 45 widgets (0 shipped).

Would that answer your question?

Regards John