Query to Match fill Date corresponding to item arrival date (1 Viewer)

[Gladis]

here is the modification.

Hi arnelgp,

Thanks for the help So far.

I am able to use this for the most part. I am running into issues here and there.

Allocation is still not functioning 100% .
For example. Item Number 1570C898.
when I enter it in Item number field and click Update data. it calculates the correct dates.
but, when I leave it blank I am only getting the first date (2022-04-07).


I tried to go through the code and fix it. I am totally lost on the allocation sections.

what is the reason behind opening this Query in recordset "qryInboundAllocation" before populating the table "allocateZ"?
Data for "Expr1" Comes from another query "qryTotalAllocate" based of that table. isn't that kind of back word?

        r_out.MoveFirst
        
        Do While Not .EOF
            sItemID = ![Item No 1]
            If r_x Is Nothing Then
                Set r_x = db.OpenRecordset( _
                                                "select T.* from qryInboundAllocation As T " & _
                                                "where " & _
                                                "T.[exp qty] > [B][COLOR=rgb(184, 49, 47)]T.Expr1[/COLOR][/B] " & _
                                                "order by T.[exp eta];", dbOpenDynaset)
            End If
            r_x.Filter = "[item id] = '" & sItemID & "'"
            Set r_in = r_x.OpenRecordset
            
            If Not (r_in.BOF And r_in.EOF) Then
                r_in.MoveFirst
                remain = ![order qty]
                qty = min(remain, r_in![exp qty] - r_in!expr1)
                r_z.AddNew
                r_z!id = r_in!id
                r_z![item id] = sItemID
                r_z!allocate_qty = qty
                r_z.Update
                DBEngine.Idle dbRefreshCache
                .Edit
                ![EXP ETA] = r_in![EXP ETA]
                .Update
                .Bookmark = .LastModified
                remain = remain - qty
                Do While remain <> 0
                    'r_in.Close

 

[arnelgp]

For example. Item Number 1570C898.
when I enter it in Item number field and click Update data. it calculates the correct dates.

just use 1 button and need to enter the Item Number first.
so there is only 1 calculation that will produce the Correct result.

EDIT:
there is change in the Ordering in the resulting subform.
i made it to calculate, now, based on Order Date (oldest to newest), and Order-No (smallest to highest).
so that old request get allocation first than those with newer request (date).