read/write in VBA

[sargentrs]

I'm new to modules/VBA so please forgive any stupid questions

I'm trying to write a function in a query to allocate material from work orders to fill sales order demand. It's currently a many to many relationship so I'm getting multiple records per sales order for each work order for the same item. I have 2 tables:

Table name: tblSalesOrderDemand
Fields:
strID strSO lngReqd dteDueDate

482 407751 50 6/4/09
482 407752 50 6/10/09

Table name: tblWorkOrders
Fields:
strWONum strID lngQty
77501-99 482 25
77502-99 482 30
77503-99 482 100

Desired result:

tblWorkOrderAlloc

Fields:
strID strSO lngReqd dteDue strWONum lngAlloc lngWOAvail
482 407751 50 6/4/09 77501-99 25 0
482 407751 50 6/4/09 77502-99 25 5
482 407752 50 6/10/09 77502-99 5 0
482 407752 50 6/10/09 77503-99 45 55

I have hundreds of sales order demand records and 1-10+ work orders for each item. At this point, I'm not concerned with the due date on the work order (it's a dynamic manufacturing environment and changes too fast!)

The only way I can see to accomplish this would be to read tblSalesOrderDemand, find the associted strID in tblWorkOrders, loop thru it consuming lngQty until EOF and writing out to tblWorkOrderAlloc until next strID.

As I'm a beginner in writing functions/modules, the only thing I've been able to write is If-Then statements and those get me so far.

Any suggestions?

 

[PearlGI]

Hi

I have a solution that works for the example given.

Step 1: Create the following query and call it 'qryWoAlloc'

SELECT tblWorkOrderAlloc.strWONum, Sum(tblWorkOrderAlloc.lngAlloc) AS Alloc
FROM tblWorkOrderAlloc
GROUP BY tblWorkOrderAlloc.strWONum;

Step 2: Create the following module and run it:

Sub Allocations()
Dim db As Database, rsWO As Recordset, rsSO As Recordset, rsAlloc As Recordset, _
    lngQty As Long, blnSOFilled As Boolean
Set db = CurrentDb
Set rsSO = db.OpenRecordset("tblSalesOrderDemand")
Set rsAlloc = db.OpenRecordset("tblWorkOrderAlloc")
Do Until rsSO.EOF
    lngQty = rsSO!lngreqd
    blnSOFilled = False
    Do Until blnSOFilled = True

        Set rsWO = db.OpenRecordset("SELECT tblWorkOrders.strWONum, tblWorkOrders.strID, [lngQty]-nz([Alloc]) AS lngUnAlloc " & _
            "FROM tblWorkOrders LEFT JOIN qryWOAlloc ON tblWorkOrders.strWONum = qryWOAlloc.strWONum " & _
            "WHERE (((tblWorkOrders.strID) = """ & rsSO!strid & """) And (([lngQty] - nz([Alloc])) > 0)) " & _
            "ORDER BY tblWorkOrders.strWONum;")
        With rsAlloc
            .AddNew
            !strid = rsSO!strid
            !strso = rsSO!strso
            !lngreqd = rsSO!lngreqd
            !dteduedate = rsSO!dteduedate
            !strWONum = rsWO!strWONum
            If lngQty < rsWO!lngunalloc Then
                !lngAlloc = lngQty
                !lngwoavail = rsWO!lngunalloc - lngQty
                blnSOFilled = True
            Else
                !lngAlloc = rsWO!lngunalloc
                lngQty = lngQty - rsWO!lngunalloc
                !lngwoavail = 0
                rsWO.MoveNext
            End If
            .Update
        End With
    Loop
    rsSO.MoveNext
Loop
End Sub

In case you get any problems, I attach my working version:

 

[sargentrs]

Thank you, I'll try it out. However, when I tried to open your sample database I got a "missing or broken reference" error to "dao2535.tlb version 3.5". Do I really need that?

 

[sargentrs]

After reviewing your code with my actual data, I realized I left out some additional information and criteria:

I'm using Office 2007 Professional.

If lngReqd = 0, then lngAlloc = 0

If strWONum is Null, then strWONum = "None" and lngQty = 0 and lngAlloc = 0.

The goal is to produce a list of all strID, which strWONum and lngQty are allocated on which dteDueDate. If no strWONum exists, show me I need one for the lngReqd quantity.

Sorry if I'm being a pain! Thanks a lot!