Need Advice on VBA code for inserting records

[Paulsburbon]

I have an access database that takes orders and then when it is approved would take every item on the order and create a record in a shipment table based on availablity and then create any number of packages with and number of items in that package. In my product table I have a field that is how many fit in a package. So if two different items are marked as two in one package and someone orders 1 of each the system creates one package. The problem I have is flushing this system out. I have products like catalogs that any number can fit into a package. I marked them as zero in a package. That I have no problem getting correctly into a package. but after that it is a challenge for me.

I've revised this like three times and cannot make this work correctly.
I just need to loop thru my recordset and make it so that if I make an order that has 3 of one item and three of another and they fit 2 in a box that I will come up with one shipment with 3 packages and in one of those packages it shows that it contains one of each item. Can someone help me out tonight?

 

[PNGBill]

Do you have your basic loop setup?

If you do, you could try and create some Variables that can hold the order qty and other relevant data and as you loop it will sort the variables and amend the qtys accordingly and supply a final packaging list.

This could require records be added to a TblPackage that will hold the final Packing List for each Order ID.

The tools you will need to do this will be sql or rst's to append the records and the loop plus a hand full of variables within your vba to be manipulated and as a Carton is Full, add the record to TblPackage and then continue looping until all the items and qtys are depleted from the variables.

Trust this makes sence.

 

[Paulsburbon]

Bill thank you so much for your reponse!!!!

Ok I never thought of doing it starting from the total amount and count down. So should I be using an array variable? Just to let you know I might ask some foolish questions out of being extremely tired and I'm an idiot.

So Before doing any sql to add to the tables (which I already have functions set up with input variables so I know what info needs to be added) I would get variables like the item that is going in and the total amount. How do I make the switch to the next item when the carton is not full and there is another item in the recordset?

Again Bill thank you for the quick response.

Paul

 

[PNGBill]

I imagine each loop would deal with one item.
It would need to identify what part of a carton it would fit into ie 1/3rd, 1/2 etc.
It could check any other issues as it loops eg can't be packed with certain other items. This issue may require additional fields in your SKU table ??

These things are tricky to an extent but more importantly, unique to your products and your packing facilities.

Do you just have One Carton option ?

Imagine a realistic way a warehouse would pack an order.

They would assemble all the items in a defined area.
They would open one box to start with and pack one item in this box.
Is the box full, if so, close and put to one side.
go to the next item.
If this item can be packed in the same box, do, if not, open a new box and pack the item in this.
continue this process until there are no items remaining for packing.

To do this virtually you will need a Table to use, say TblPackage.

Depending on what fields you have in your current Order Table you could impact the qty's from one to the other as the code looped.

The code will use a variable for the ItemToPack (SKU) and variables for the OrderQty and PackedQty for this item taken from the relevant two tables.

Sql would select an item from the OrderTable where the OrderQty less PackedQty >0 (still items to pack) and update the above 3 variables accordingly.

The loop will run and the variables will be reset to the next item and it's relevant qtys.

All this is theory and as you put it into practice you may find some issues that need to be worked out as the code proceeds.

Another variable may be the open cartons as you may have 2 or 3 of these as the code runs until an items comes along that can fill one of these cartons.

Sorry I haven't got a Final solution but I am sure the above will work, with a bit of assistance and head scratching.

If you have a field that gives some idea of carton packing issues you could sort by this and start with the items that take up the most room in a box eg over half of a box items packed first, gives you a Max number of open cartons (at any one time) that will then be filled with the smaller items until they can be sealed and put aside.

Some Head Scratching no doubt.

 

[Paulsburbon]

Bill,

Thank you thank you thank you! I have two more problems but it works! I have been dealing with this for too long and I was over complicating every part. Here is the code I came up with:

Private Function AutoSetUpPackage(ByRef connection As ADODB.connection, _
    ByRef RecordSet As ADODB.RecordSet, _
    ByVal ShipmentID As String, _
    ByVal ItemsPerPackage As Integer, _
    ByRef result As String, _
    ByVal LastItem As Boolean)
    '------------------------------------------------------------------------
    ' Memeber: Database.modShipment.AutoSetUpPackage
    ' Created by:  Administrator
    ' Machine Name:  VMPAUL
    ' Date - Time:  9/5/2010  - 11:06:53 AM
    ' Description:
    '------------------------------------------------------------------------
    On Error GoTo AutoSetUpPackage_Error
    'Recordsets
    Dim strPackageID As String
    
    'Qty Dimensions
    Dim intPackagedQty As Integer
    Dim intItemTotalQty As Integer
    Dim intFitInPackageQty As Integer
    
    'Weight
    Dim intItemWeight As Integer
    Dim intPackageWeight As Integer
    
    'Counters
    Dim blnOpenPackage As Boolean

    'Reset Items in Packages
    intPackagedQty = 0
    intItemTotalQty = 0
    intItemWeight = 0
    intPackageWeight = 0
    intFitInPackageQty = CInt(ItemsPerPackage)
    blnOpenPackage = False
    
    Do Until RecordSet.EOF
        intItemTotalQty = RecordSet!intQtyTakenFromInventory
        If IsNull(DLookup("intWeight", "tblProducts", "idsProductID = " & RecordSet!lngProductID)) = False Then
        intItemWeight = DLookup("intWeight", "tblProducts", "idsProductID = " & RecordSet!lngProductID)
        Else
        intItemWeight = 1
        End If
    
        Do Until intItemTotalQty = 0
            'Check if there already is an open package.
            If blnOpenPackage = True Then
                'Check to see if items will complete fill the package
                'This will not be a full package
                If intItemTotalQty < intFitInPackageQty - intPackagedQty Then
                    connection.Execute FillPackageWithItems(strPackageID, RecordSet!idsOrderDetailID, intItemTotalQty)
                    intPackageWeight = intPackageWeight + (intItemWeight * intItemTotalQty)
                    intPackagedQty = intItemTotalQty
                    intItemTotalQty = intItemTotalQty - intItemTotalQty
                    blnOpenPackage = True
                'This is a full package
                Else
                    connection.Execute FillPackageWithItems(strPackageID, RecordSet!idsOrderDetailID, intFitInPackageQty - intPackagedQty)
                    intPackageWeight = intPackageWeight + (intItemWeight * intItemTotalQty)
                    intPackagedQty = intItemTotalQty
                    intItemTotalQty = intItemTotalQty - (intFitInPackageQty - intPackagedQty)
                    blnOpenPackage = False
                End If
            'New Package
            Else
                'Create the package
                connection.Execute CreatePackage(ShipmentID, CStr(0))
                intPackageWeight = 0
                intPackagedQty = 0
                strPackageID = CStr(GetLastIDInserted(connection))
                'Check to see if items will complete fill the package
                'This will not be a full package
                If intItemTotalQty < intFitInPackageQty - intPackagedQty Then
                    connection.Execute FillPackageWithItems(strPackageID, RecordSet!idsOrderDetailID, intItemTotalQty)
                    intPackageWeight = intPackageWeight + (intItemWeight * intItemTotalQty)
                    intPackagedQty = intItemTotalQty
                    intItemTotalQty = intItemTotalQty - intItemTotalQty
                    blnOpenPackage = True
                'This is a full package
                Else
                    connection.Execute FillPackageWithItems(strPackageID, RecordSet!idsOrderDetailID, intFitInPackageQty - intPackagedQty)
                    intPackageWeight = intPackageWeight + (intItemWeight * (intFitInPackageQty - intPackagedQty))
                    intPackagedQty = intFitInPackageQty - intPackagedQty
                    intItemTotalQty = intItemTotalQty - intPackagedQty
                    blnOpenPackage = False
                End If
            End If
            
            'Check to update the weight of a package that is not quite full
            If LastItem = True Then
                blnOpenPackage = False
                connection.Execute UpdatePackageWeight(strPackageID, intPackageWeight)
                intPackagedQty = 0
            Else
                If blnOpenPackage = True Then
                Else
                connection.Execute UpdatePackageWeight(strPackageID, intPackageWeight)
                intPackagedQty = 0
                End If
            End If
        Loop
    
    RecordSet.MoveNext
    Loop
    
PROC_EXIT:
    Exit Function

AutoSetUpPackage_Error:
    MsgBox Err.Number & " " & Err.Description & " ,Line: " & Erl & " In Database.modShipment.AutoSetUpPackage"
    Err.Clear
    Resume PROC_EXIT
End Function

My Problem is that in order to update the weight when the package is created can't decide on how to let the function know that a package that is not full but there are no more items to add should be close and the weight updated. I tried passing in a variable to check but it's not quite working out.

Again becuase of the time you took to think it out it made me able to get this to work. Thank you.

Paul

 

[PNGBill]

Great news.

You have packed all the items and the loops have stopped but some packages are blnOpenPackage = True.

Then could you have one line of code at the end, after the loop, blnOpenPackage = False
?