Linking various records to one main record issues...

[CustomDBIOM]

Sorry this is quite hard to explain. And I cant work out how to do it?

Any ideas?

Or has anyone got a good Materials Control Database to use?

Currently when a shipping notice (MSN) is raised all the PO information can be entered such as PO number, description, etc. However, if PO materials such as valves are spread across a number of shipments what is requested is that the number of items field is automatically deducted from the next MSN - the example below explains:

Total PO - 100 valves
MSN 1 - 30 valves (remaining 70)
MSN 2 - 30 valves (remaining 40)
MSN 3 - 40 valves (remaining 0)

It has being requested that the system automatically calculates the remaining valves to ship this is proving to be extremely difficult,

 

[CustomDBIOM]

OK ill try explaining another way.

It is possible to do this.

Main Form -> Subform
     1                1
                       2
                       3 etc.

The numbers being records. So I can have 3 records in a subform linked to 1 record on a main form right?

So can I do this:

Main Form -> Another Form -> Subform with 'another'
     1                1                                 1
                                                         2
                                                         3
                       2                                 1
                                                          2
                                                          3
                       3 etc.                           1
                                                          2
                                                          3

Hard to explain still can someone say if you dont understand at all etc?

From there I wish to do a calculation which gives me the remainder items left from the Main form. So the main form has 6 items ordered. Then the another form start a fresh details. The subform pickups up from the MAIN form that there is 6 to send. Record one sends out 2, so 4 are remaining. When i goto record 2 of the subform, I then want it to TELL me I have 4 remaining.

Can anyone help?

Thanks!

 

[mdemarte]

Search for records about a running sum. Then, substract the running sum from the original amount.

 

[CustomDBIOM]

Whats a running sum can you explain please?!

 

[mdemarte]

To get the sum in the subform for MSN, if the fields are MSNTotal, create an unbound text box with the data source as follows

=Sum([MSNTotal])

To get the total left, you have to reference that text box on the MSN subform. So, if the MSN subform is called frmsubMSN and the sum text box is called SumMSN and the PO total is called POTotal -- then the total left would be

= POTotal - [frmsubMSN].Form!SumMSN

Substitute in your field and form names and you should be all set.

 

[CustomDBIOM]

Brilliant thank you ever so much