Re: How to Find the Date of Last Change in Inventory

The code should go where ever you want to get the information. Since I don't
know how your app is constructed, it would be hard to say. My first guess is
it would be a command button and the code in the Click Event.

It would also depend on how you want to present the data.

Be aware that the code I posted may not work as we expected, and may need
some tweeking to get exactly what you are after. I wrote it on the fly with
no way to test it.

"doyle60@xxxxxxx" wrote:

> Thanks. I have done the following, as you suggested:
>
> SLOBInventorytbl:
> The table which holds all the data. The keys are Style, Color,
> SLOBDate.
>
> SlobInv1StyColqry:
> I created this query which groups on Style and Color. It is based on
> SLOBInventorytbl.
>
> SLOBInv2StyColDatUntqry:
> I created this query, based on SLOBInventorytbl, with these fields in
> this order: Style, Color, SLOBDate, OnHand. (OnHand (Units) is the
> value, a quantity of units in the warehouse.) There are no "Grouped
> By"s on any of these fields because the table's keys make it
> superfluous. The Style and Color fields are marked Ascending. The
> SLOBDate field is marked Descending, as you instructed.
>
> I'm not sure what to do with your code. But I did rename the fields.
> Here it is now:
>
> Set qdf = Currentdb.QueryDefs("SlobInv1StyColqry")
> Set rstItems = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
> rst.MoveLast
> rst.MoveFirst
> Do While Not rstItems.EOF
> Set qdf = Currentdb.QueryDefs("SLOBInv2StyColDatUntqry")
> qdf.Parameters(0) = rstItems![Style]
> qdf.Parameters(1) = rstItems![Color]
> Set rstInventory = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
> rst.MoveLast
> rst.MoveFirst
> dtmLastDate = rstInventory![SLOBDate]
> intCount = rstInventory![OnHand]
> rstInventory.MoveNext
> If rstInventory.EOF Then
> 'No movement here
> End If
> Do While Not rstInventory.EOF
> If rstInventory!OnHand = intCount Then
> dtmFirstDate = rstInventory!Units
> Else
> 'Now you have the count and dates to calculate
> Exit Do
> End If
> rstInventory.MoveNext
> Loop
> rstInventory.Close
> rstItems.MoveNext
> Loop
> __________________________________
>
> Thanks,
>
> Matt
>
>
.