Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2004
    Posts
    10

    Unanswered: Please help in my code

    Dear All,

    We have two tables called OLDDAT and NEWDATas follows :-

    OLDDAT
    SECTION - ITEM NUMBER
    JOB - JOB NUMBER VARCHAR
    MARK - ITS LIKE A ITEM DESCRIPTION VARCHAR
    QTY - NUMBER OF QTY IN PIECES
    WT - TOTAL WEIGHT OF ALL THE PIECES
    LENGTH - LENGTH OF THAT MARK

    NEWDAT
    SNO - AUTONUMBER ( FOR GETTING UNIQUE SERIAL NUMBER FOR BOX)
    SECTION - ITEM -NUMBER
    JOB - JOB NUMBER -VARCHAR
    MARK - ITS LIKE A ITEM DESCRIPTION - VARCHAR
    QTY - NUMBER OF QTY IN PIECES -NUMBER
    WT - TOTAL WEIGHT OF ALL THE PIECES - NUMBER
    LENGTH - LENGTH OF THE MARK - NUMBER
    NOB - NUMBER OF BOXES -NUMBER
    BOXQTY - NUMBER OF PIECES INSIDE EACH BOX BASED - NUMBER

    There are different lengths for each and every item and based on the lengths we need to distribute the quantity into BOXES
    I.E
    CONDITION
    IF LENGTH <= 390
    THEN WT/1000
    ELSE WT/2000
    THIS WT/1000 OR WT/2000 WILL GIVE NUMBER OF BOXES EG.NOB
    AND BASED ON NUMBER OF BOXES U WILL DISTRIBUTE QTY
    LIKE QTY/NOB WILL GIVE NO OF QTY IN EACH BOX AND EACH BOX MUST BE INSERTED INTO NEWDAT TABLE


    lets say eg
    OLDDAT
    section : A
    JOB :A
    MARK :ABC
    QTY :500
    LENGTH :380
    WT:6000

    NEWDAT WILL CONTAIN

    SECTION = A ,JOB =A, MARK=ABC, QTY=500, LENGTH=380,WT=6000 BOXQTY=83.33
    SECTION = A ,JOB =A, MARK=ABC, QTY=500, LENGTH=380,WT=6000 BOXQTY=83.33
    SECTION = A ,JOB =A, MARK=ABC, QTY=500, LENGTH=380,WT=6000 BOXQTY=83.33
    SECTION = A ,JOB =A, MARK=ABC, QTY=500, LENGTH=380,WT=6000 BOXQTY=83.33
    SECTION = A ,JOB =A, MARK=ABC, QTY=500, LENGTH=380,WT=6000 BOXQTY=83.33
    SECTION = A ,JOB =A, MARK=ABC, QTY=500, LENGTH=380,WT=6000 BOXQTY=83.33

    AND I HAVE DONE THIS USING FOLLOWING CODE :-

    Private Sub Command0_Click()
    Dim dbs As Database
    Dim rec As Recordset, Recnew As Recordset, recme As Recordset
    Dim Box As Integer, Twt As Integer, Ebox As Double, i As Integer
    Dim intdone As Boolean

    Set dbs = CurrentDb

    Set recme = dbs.OpenRecordset("Select Mark,Job,Length,Wt,rqty from olddat;")
    recme.MoveFirst
    Do Until recme.EOF
    If recme.Fields(2) < 390 Then
    Box = recme.Fields(3) / 1000
    Ebox = recme.Fields(4) / Box
    ElseIf recme.Fields(2) > 390 Then
    Box = recme.Fields(3) / 2000
    Ebox = recme.Fields(4) / Box
    End If
    Set Recnew = dbs.OpenRecordset("newdat")
    i = 1
    intdone = False
    Do Until intdone = True
    If i > Box Then
    Debug.Print " Comple"
    intdone = True
    Else
    i = i + 1
    With Recnew
    .AddNew
    !Mark = recme.Fields(0)
    !Job = recme.Fields(1)
    !Length = recme.Fields(2)
    !Wt = recme.Fields(3)
    !rqty = recme.Fields(4)
    !Box = Box
    !Ebox = Ebox
    .Update

    End With
    End If
    Loop
    recme.MoveNext
    Loop

    dbs.Close

    End Sub


    My problem is inside each box there must be round quantity like each box must contain 83 instead of 83.33 as it is in

    pieces and when i round it i get the sum of qty of all boxes in newdat as 498 which is less than 500 of olddat in this way i

    am losing 2 pieces which is wrong and to avoid this i need a way in which each box must contain round qty like 83 and

    the remaining qty of 2 pieces must be inserted into newdat as 7'th extra box with this 2 pieces .

    Please help me.

    Best Regards

    Arif

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi

    After you finished your loop that creates the first "full" records you need to work out your remainder:

    RemainingQty=recme.Fields(4)-(int(recme.Fields(4)/Box)*Box)
    ' where Box = recme.Fields(3) / 1000 (from your code)

    If RemainingQty<>0
    'create another record using the RemainingQty
    Endif

    Chris
    Last edited by howey; 12-22-04 at 05:44. Reason: I got the formula wrong !!!

  3. #3
    Join Date
    Dec 2004
    Posts
    10

    Thanks for your help

    Howey can u jus append that in my code as i m struggling
    with the syntax

    Best Regards

    Arif

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Here's the updated code (I haven't tested it). I see your original code put stores "Box" in the record wher "Box" is the calculated number of full boxes. So in your example, all six records will have 6 as the Box value. The way I've written the code below, the 7th record will also have a Box value of 6. Do you need them all to be 7 or should they really be sequenced 1-7 ?

    Code:
    Dim dbs As Database
    Dim rec As Recordset, Recnew As Recordset, recme As Recordset
    Dim Box As Integer, Twt As Integer, Ebox As Double, i As Integer
    Dim intdone As Boolean
    Dim remainder As Integer
    
    
    Set dbs = CurrentDb
    
    Set recme = dbs.OpenRecordset("Select Mark,Job,Length,Wt,rqty from olddat;")
    recme.MoveFirst
    Do Until recme.EOF
        If recme.Fields(2) < 390 Then
            Box = recme.Fields(3) / 1000
            Ebox = recme.Fields(4) / Box
        ElseIf recme.Fields(2) > 390 Then
            Box = recme.Fields(3) / 2000
            Ebox = recme.Fields(4) / Box
        End If
        remainder = recme.Fields(4) - (Int(recme.Fields(4) / Box) * Box)
        
        Set Recnew = dbs.OpenRecordset("newdat")
        i = 1
        intdone = False
        
        Do Until intdone = True
            If i > Box Then
                Debug.Print " Comple"
                intdone = True
            Else
                i = i + 1
                With Recnew
                .AddNew
                !Mark = recme.Fields(0)
                !Job = recme.Fields(1)
                !Length = recme.Fields(2)
                !Wt = recme.Fields(3)
                !rqty = recme.Fields(4)
                !Box = Box
                !Ebox = Ebox
                .Update
                End With
            End If
            If remainder <> 0 Then
                With Recnew
                .AddNew
                !Mark = recme.Fields(0)
                !Job = recme.Fields(1)
                !Length = recme.Fields(2)
                !Wt = recme.Fields(3)
                !rqty = recme.Fields(4)
                !Box = Box
                !Ebox = remainder
                .Update
                End With
            End If
        Loop
    recme.MoveNext
    Loop
    
    dbs.Close
    
    End Sub
    regards
    Chris

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Just a quick thought for your remainder Formula

    how about changing this
    Code:
    remainder = recme.Fields(4) - (Int(recme.Fields(4) / Box) * Box)
    to
    Code:
    remainder=recme.Fields(4) mod Box
    Just a thought
    Dave

  6. #6
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Code:
    remainder=recme.Fields(4) mod Box
    Good point. I must admit I didn't know mod was available in VB. I'm not that great on VB.

    Chris

  7. #7
    Join Date
    Dec 2004
    Posts
    10

    Dear Chris

    HI chris

    I need a 7th box and that 7 box must contain the remaining values.I am very thankfull for your help. Meanwhile i will test your code and check.

    Regards

    Arif

  8. #8
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    The 7th record will contain the remaining quantity. The question I am asking is what is the point on the "Box" field in your code. In your example it will store 6 as the value of Box for all six records. If there are going to be 7 records then shouldn't that value be 7 ?
    Chris

  9. #9
    Join Date
    Dec 2004
    Posts
    10

    Chris Thanks v much

    HI chris

    I tested the your additional code

    but there were some changes had to be made like the if condition given by you must come in outer range.I made the change and its very good.

    thanks for all the support but there is one criteria left
    the box number must be displayed as 7 in the seventh box i m pasting the recent revised code as follows :-

    Private Sub Command0_Click()
    Dim dbs As Database
    Dim rec As Recordset, Recnew As Recordset, recme As Recordset
    Dim Box As Integer, Twt As Integer, Ebox As Double, i As Integer
    Dim intdone As Boolean
    Dim remainder As Integer


    Set dbs = CurrentDb

    Set recme = dbs.OpenRecordset("Select Mark,Job,Length,Wt,rqty from olddat;")
    recme.MoveFirst
    Do Until recme.EOF
    If recme.Fields(2) < 390 Then
    Box = recme.Fields(3) / 1000
    Ebox = recme.Fields(4) / Box
    ElseIf recme.Fields(2) > 390 Then
    Box = recme.Fields(3) / 2000
    Ebox = recme.Fields(4) / Box
    End If

    remainder = recme.Fields(4) - (Int(recme.Fields(4) / Box) * Box)




    Set Recnew = dbs.OpenRecordset("newdat")
    i = 1
    intdone = False
    Do Until intdone = True
    If i > Box Then
    Debug.Print " Comple"
    intdone = True
    Else
    i = i + 1
    With Recnew
    .AddNew
    !Mark = recme.Fields(0)
    !Job = recme.Fields(1)
    !Length = recme.Fields(2)
    !Wt = recme.Fields(3)
    !rqty = recme.Fields(4)
    !Box = Box
    !Ebox = Ebox
    .Update

    End With
    End If




    Loop

    If remainder <> 0 Then
    With Recnew
    .AddNew
    !Mark = recme.Fields(0)
    !Job = recme.Fields(1)
    !Length = recme.Fields(2)
    !Wt = recme.Fields(3)
    !rqty = recme.Fields(4)
    !Box = Box
    !Ebox = remainder
    .Update
    End With

    End If



    recme.MoveNext
    Loop

    dbs.Close

    End Sub


    Please workout for 7th box

    Best Regards

    Arif

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •