change value based on 2 different types of criteria? (1 Viewer)

[spacepro]

Hi All,

Ok I run code to assign a field to fill a vehicle ("LoadNumber"), which assigns it by Site and ProductCube per vehicle.

So now I have 4 products that will be placed on top of another 4 products(not likely to change).

Currently the records will display as follows:

Purchase Order  Product   Qty      Site       LoadNumber  Rows

12345                   A            12    London         1                 4
12345                   B            100  London         1                17
12345                   E             88    London         2                13

Now product A + B will be standing on the floor and product E will go on top. I want to assign the record with product E to Loadnumber 1, because they will go on top of product B.

I will need some sort of trigger to define the products that go on top, but stuck a bit on the coding of it, maybe something like:

product = rst.fields("Product")
select case Product

Case is = Product E
if rst.fields("Qty") <> then
.edit
.fields("LoadNumber") = product B loadnumber
end if
if rst.fields("Qty") > product B then
edit qty and addnew record with balance and change loadnumber accordingly.
End if

The above code is absolutely terrible but hopefully if gives you an idea of what I am trying to achieve.

Currently at the moment I am running code to assign the 8 products loadnumber to 0 then manually editing the loadnumber though a form.

If any clarification is required please post.

Any help is appreciated.

Not got my head around defining variables in DAO to achieve what I need.

Any help is much appreciated.

my original post concerning the vehicle fill is located here
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=59184

Kind regards

Andy

 

[spacepro]

Below is the current code I use to check and allocate the vehicles.
Each vehicle has a max of 21 rows.

'Declare variables and Set the Database and Recordset
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim NumofRec As Long
Dim CurrentLanes, StoredLanes, TotalLanes As Double

Set MyDB = CurrentDb()
Set rst = MyDB.OpenRecordset("qryCardiff", dbOpenDynaset)
'Error Handler if Data is none existent
If numrec = Null Then
Exit Sub
End If


'Begin Loop for Checking Records
StartProcess:
StartOverNum = 0
StartOver:
StartOverNum = StartOverNum + 1
'Displays Status Bar Message
StsBar = SysCmd(acSysCmdSetStatus, "Calculating Lanes Usage, Number of Loops: " & StartOverNum)


rst.MoveLast  'Moves to Last Record
NumofRec = rst.RecordCount  'Defines Number of Records

StoredLanes = 0  'Variable set to 0 to reset load

rst.MoveFirst   ' Moves to the first Record


For iassign = 1 To NumofRec

CurrentLanes = rst.Fields("Rows")
TotalLanes = CurrentLanes + StoredLanes

Select Case TotalLanes

Case Is = 21
    StoredLanes = 0
    If i = NumofRec Then
        GoTo AssignTruck:
    End If
    rst.MoveNext

Case Is < 21
    StoredLanes = StoredLanes + CurrentLanes
    If i = NumofRec Then
        GoTo AssignTruck:
    End If
    rst.MoveNext

Case Is > 21
    balancelanes = TotalLanes - 21
    'Store current records value
    CurrentLanes = rst.Fields("Rows")
    'Calculated what the value of current records should be to add up to 21
    UpdatedLanes = CurrentLanes - balancelanes
    Actual = rst.Fields("Rows") - balancelanes
    'Copies Data for the New Record
    
    CopyProduct = rst.Fields("Product")
    CopyCube = rst.Fields("ProductCube")
    CopyLDC = rst.Fields("LDC")
    CopyQty = rst.Fields("Qty")
    CopyActual = rst.Fields("RowsActual") - balancelanes
    Changeqty = Int(rst.Fields("RowsActual") * rst.Fields("Productcube"))
    newqty = Int(rst.Fields("RowsActual") * rst.Fields("Productcube")) - rst.Fields("Qty")
    CopyPurchaseOrder = rst.Fields("PurchaseOrder")
    CopyLineNo = rst.Fields("LineNo")
    With rst
        'Change current record's value to the calculated value
        .Edit
        .Fields("Rows") = UpdatedLanes
        .Fields("RowsActual") = CopyActual
        .Fields("DateModified") = Date
        .Fields("TimeModified") = Time()
        .Update
        'Add new record
        .AddNew
        'Enter values for new record
        'Add one to current lane since the balance should go to a new lane
        .Fields("Rows") = balancelanes
        .Fields("Product") = CopyProduct
        .Fields("ProductCube") = CopyCube
        .Fields("PurchaseOrder") = CopyPurchaseOrder
        .Fields("LineNo") = CopyLineNo
        .Fields("LDC") = CopyLDC
        .Fields("Qty") = newqty
        .Fields("RowsActual") = balancelanes
        .Fields("DateModified") = Date
        .Fields("TimeModified") = Time()
        .Update
    End With
    StoredLanes = 0
    rst.MoveNext
End Select

Next iassign

StsBar = SysCmd(acSysCmdSetStatus, "Checking Lanes Assignment, Number of Loops: " & StartOverNum)


rst.MoveLast
NumofRec = rst.RecordCount
StoredLanes = 0
rst.MoveFirst

'Loop to check if all the lane assignments are OK,
'If not, go back to assigning loop
For iCheck = 1 To NumofRec

CurrentLanes = rst.Fields("Rows")
TotalLanes = CurrentLanes + StoredLanes

Select Case TotalLanes

Case Is = 21
    StoredLanes = 0
    rst.MoveNext

Case Is < 21
    StoredLanes = StoredLanes + CurrentLanes
    rst.MoveNext

Case Is > 21
    GoTo StartOver:

End Select

Next iCheck

AssignTruck:
Forms!frmmax.Requery
StsBar = SysCmd(acSysCmdSetStatus, "Assigning Trucks...")
rst.MoveLast
NumofRec = rst.RecordCount
LoadMax = Forms!frmmax!Max.value + 1
TruckNum = LoadMax
StoredLanes = 0

rst.MoveFirst

For n = 1 To NumofRec

CurrentLanes = rst.Fields("Rows")

TotalLanes = CurrentLanes + StoredLanes

Select Case TotalLanes

Case Is = 21
    With rst
        .Edit
        .Fields("LoadNumber") = TruckNum
        .Fields("DateModified") = Date
        .Fields("TimeModified") = Time()
        .Update
    End With
    TruckNum = TruckNum + 1
    StoredLanes = 0

Case Is < 21
    With rst
        .Edit
        .Fields("LoadNumber") = TruckNum
        .Fields("DateModified") = Date
        .Fields("TimeModified") = Time()
        .Update
    End With
    StoredLanes = StoredLanes + CurrentLanes

Case Is > 21
    'This should not happen
    MsgBox "Something is wrong!", vbCritical, "Coding Problem ..."
    
End Select

rst.MoveNext

Next n

StsBar = SysCmd(acSysCmdSetStatus, " ")
Set rst = Nothing
Set MyDB = Nothing
Me.Requery
Forms!frmmax.Requery

End Sub

Andy