Hi All iam new to Access vba, i need your help, please help me. Quantity allocation.

[kornkarthik]

Hi All,

I am new to VBA, i am trying my best to learn and write it my self , but i dont have so much time, please help me.

I am trying to allocate quantity from Table 2(Quantity_Alloc) to Table 1(CUST_INFO) based on a criteria.

1) First i need to allocate intransit quanitity i.e i have to take intransit quantity and subtract this to CUST_QTY(in table1). For example in the below table i have intransit qty as 20 for part_no = 1. I have to take this intransit_qty and subtract with Cust_qty(table 1) like (20-2= 18), (18-4= 14), (14-5 = 9), (9-7= 2),(2-5 = -3). I have to do thsi upto Intransit_QTY = 0 at the same time i have to allocate date associated with instransit qty that is EXPECTED_DATE_Intransit_QTY. Once Intransit qty = 0, i have to start subtracting PO_QTY to the reamining CUST_QTY (From table1). that is (10-3= 7), (7-10 = -3) upto PO_QTY = 0, at the same time i have to allocate expected_date_PO_QTY. after PO_QTY = 0 then for the remaining qty for part 1, i have to comment telling "NO_QTY". Please let me know if you need additional info.

Please help me. Thanks

Here is the tables. And the output should look like


CUST_INFO (table1)
Part_NO CUST_QTY
1 2
1 4
1 5
1 7
1 2
1 5
1 2
1 3
1 5
2
2
3
3
3

PART_NO,INTRANSIT_QTY, PO_QTY,INTRANSIT_EXPECTED_DATE, INTRANSIT_EXPECTED_DATE
1 20 10 8/5/2011 8/7/2011
2 27 5 8/10/2011 8/12/2011
3 40 20 8/15/2011 8/17/2011

the Final output should look like this

Part_NO, CUST_QTY, Action expected date
1 2 20-2 =18 8/5/2011
1 4 18-4= 14 8/5/2011
1 5 14-5= 9 8/5/2011
1 7 9-7= 2 8/5/2011
1 2 2-2 = 0 8/5/2011
1 5 10-5=5 8/7/2011 (of PO date from table 2)
1 2 5-2= 3 8/7/2011 (of PO date from table 2)
1 3 3-3= 0 8/7/2011 (of PO date from table 2)
1 5 "NO QTY" (Comment)
2 so on ...
2
3
3
3

Here is the code that i have written, its not working.
Private Sub test2()
Dim db As DAO.Database
Dim iqty As Long, poqty As Long, custqty As Long
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("CUST_INFO")
Set rs2 = db.OpenRecordset("Qty_Alloc")
rs2.MoveFirst
Do While rs2.EOF
' rs1.Eno is a field I need to look for in the other table
iqty = rs2!INTRANSIT_QTY
'poqty = rs2!PO_QTY
Do While iqty >= 0
rs1.MoveFirst
Do Until rs2![PART_NO] <> rs1![PART_NO]
Do While rs1.EOF

custqty = rs1![CUST_QTY]

iqty = iqty - custqty
MsgBox (iqty)
If custqty <= iqty Then
rs1![expected Date] = rs2![EXPECTED_DATE]
rs1.Edit
rs1.Update
End If

rs1.MoveNext
Loop
Loop
rs2.MoveNext
Loop
Loop
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
End Sub