Need help for Complex VBA (1 Viewer)

[craisbeck]

I am working on a project for Cartonization in a warehouse system. If a carton# has 2 or more styles that are less in length than the longest item, I need to update the shorter in height of the small items to zero. I have attached the database in an effort to clarify this.

I have some understanding of Access, but get confused in the VBA area. Please be specific as possible. Thanks!!!!!!!!!!!

 

[dsigner]

May be I just don't understand the basic warehouse system being used but a look at your db gives me no understanding of what you are trying to do. If someone else has specific experience of this type of operation then ignore this message. If not and you would like me to look at it then please explain the basics - not the Access/VBA, just what you are trying to do.

 

[craisbeck]

Reply: Need more Info

I have attached a word doc that has an example that may help you out.

 

[dsigner]

A Carton is a box for storing products in.

Cartons come in different sizes but are always a rectalinear box

Styles are products which require a rectalinear amount of space (your wooden block)

The object is to pack the carton as efficiently as possible.

There must be other constraints. Are the styles grouped purely for storage in which case I would expect there to be a single style to a carton? Alternatively the styles are a pick list for an order or work sheet number in which case there would be a varying collection of style for each pick list. The requirement would be to fit them into the smallest single carton or efficiently multi-carton if the pick list exceeded the capacity of any single carton.

I am confused because I can't identify the pick list although there are clearly multiple styles going into one carton.

Because you are adding heights it looks as though all the styles within a container must be packed in the same orientation (eg same way up) is this true?

Once I can identify an actual algorithm here it should be possible to code it into VBA but you may find that it is more difficult to educate me to that point thanit would be to solve the coding for yourself.

What I really am failing to get is the simple warehousing process. Is this just a way of taking incoming deliveries and getting them onto the racking as efficiently as possible or does the process start with styles in the own locations/bins and the carton is for some sort of order fulfillment?

If I was doing this process by hand (and a calculator) what would the instructing piece of paper tell me to do, where would I find the bits and what would I do with the packed carton. Once that is clear my experience suggests that the computing bit is not too difficult.

 

[craisbeck]

The goal of the VBA is to identify what the CUBIC INCHES that a group of styles(items) can fit into to ship to a customer. You will notice that in the table the carton # is repeated. Styles are assigned to a carton.

The only thing I am looking for is the minmum cube of the box. The length dimension is always the largest length of any one style within the carton.

Please refer to the example below.
Carton Style Length Width Height
1000 7418 12 7 3
1000 5296 6 8 3
1000 3741 5 3 2

Step 1) Find the longest legnth dimension
Dimension 1= 12
Step 2) Find the widest width dimension
Dimension 2= 8
Step 3) If the sum of any two length dimensions is less than the longest length dimension then set the shorter in height of the items to zero
6+5 is less than 12 set shorter on to zero
Style 3741 hieght is 2(less than 5296) set to zero

Carton Style Length Width Height
1000 7418 12 7 3
1000 5296 6 8 3
1000 3741 5 3 0

Step 4) If the sum of any two width dimensions is less than the widest width dimension then set the shorter in height of the items to zero
I 7+3 is longer than 8; end if

Step 5) Sum the height of all styles to determine height dimension

RESULTSIM 1 12
Dim 2 8
DIM 3 6

Step 6: Set the "Carton length" to largest DIM
Set the "Carton width" to the second larggest dimesnion
Set the "carton highet" as the smalles t dimension
Multiple each dim toghether and set as CUBE.

Carton Legnth Height Width Cube
1000 12 8 6 576

 

[dsigner]

The attached zip file shows the VBA code needed to manipulate the test file.
I have put the test data into a table and created a form with two buttons.
One runs the calculation and puts the answers in the boxes, the other resets the data and the boxes.
Clearly I have not understood the carton packing but it should be clear how the data is being manipulated.
I have built the code round your spec so you can see which bit is doing what.
My own comments are preceded with * to distinguish them.
You still have quite a lot of work to do to turn this into a program.
At the least it should iterate through a file for several carton calculations and you will also need full error handling.
I am not very happy about destroying (zeroing) the fields in the datafile since good practise is to leave the data set unchanged but have done this because you asked for it.

I Hope that this gives you the start needed to use recordsets to do anything you need with data from here on.