Dear all,
I was having a problem whereby I don't know how to write a VBA code to facilitate my sales detail records. For your info. my company is dealing with bottle selling. Usually we got one sale order and that could produce many time of shipment. My problem is like this :
Relationship
1 (sales header) to many (sale detail)
Eg, I got sale order. I enter sale header (sale no, customer) and its sale details (item , qty, status)
Sale header table
(PK)1 ABC customer
Sale detail table
(FK)1 ItemA 10 Pending DateA
The above 1st sale detail line is set to Pending status when entered at dateA . If dateA i ship 2 qty only so, the balance would be 8 .Then, my sale details would like :
1 ItemA 8 Outstanding DateA
1 ItemA 2 ToShip DateA
That means to say i need to change the 1st line of sale details from ToShip to Outstanding and from 10 to 8.
Likewise,2nd line will be created to indicate 2 QTY with status ToShip. Both lines should be updated thru VBA Code when i press a button (To Ship) . In fact, I don't want to manually calculate them by adding new line because it could mess up the calculation if i am going to ship many times and the qty could be too big to calculate by myself
If dateB i ship 2 more the table should look like this :
1 ItemA 6 Outstanding DateA
1 ItemA 2 ToShip DateA
1 ItemA 2 Toship DateB
So, as long as my outstanding item is available, i can ship.
Hope you can understand my questions.
Thanks
I was having a problem whereby I don't know how to write a VBA code to facilitate my sales detail records. For your info. my company is dealing with bottle selling. Usually we got one sale order and that could produce many time of shipment. My problem is like this :
Relationship
1 (sales header) to many (sale detail)
Eg, I got sale order. I enter sale header (sale no, customer) and its sale details (item , qty, status)
Sale header table
(PK)1 ABC customer
Sale detail table
(FK)1 ItemA 10 Pending DateA
The above 1st sale detail line is set to Pending status when entered at dateA . If dateA i ship 2 qty only so, the balance would be 8 .Then, my sale details would like :
1 ItemA 8 Outstanding DateA
1 ItemA 2 ToShip DateA
That means to say i need to change the 1st line of sale details from ToShip to Outstanding and from 10 to 8.
Likewise,2nd line will be created to indicate 2 QTY with status ToShip. Both lines should be updated thru VBA Code when i press a button (To Ship) . In fact, I don't want to manually calculate them by adding new line because it could mess up the calculation if i am going to ship many times and the qty could be too big to calculate by myself
If dateB i ship 2 more the table should look like this :
1 ItemA 6 Outstanding DateA
1 ItemA 2 ToShip DateA
1 ItemA 2 Toship DateB
So, as long as my outstanding item is available, i can ship.
Hope you can understand my questions.
Thanks
