Update Set Value based on Max(Date)

[Lou P]

Greetings;

Please advise if I have posted this in the wrong forum, incorrectly or haven't given the right information...TYVM

Using MS Access 2010 I am tracking inventory "Items" in a database and the "Events" associated with the Items as they occur. I want the "CurrentStatus" to set to the latest Event automatically.

I am trying to update the "CurrentStatus" on the ItemEvents subform to automatically set the "CurrentStatus" to "Yes" for the Event with the latest date.

Then set the "CurrentStatus" to "No" for the previous latest Event's "CurrentStatus".

Problem is I can't even get the first part of set the "CurrentStatus" to "Yes" for the Max(EventDate) to work. Much less to clear the previous one.

The two tables are tblItem:
ITEMID
ItemCategory
ItemNomen
ItemType
ItemDescription
ItemModel
ItemSerialNumber
ItemChassis
ItemOperationalStatus
ItemManufacturer
ItemVendor
ItemCost
ItemCostEstimated
ItemPurchaseDate
ItemWarranty
ItemEarExpireDate
ItemBarCOde
ItemRmks
ItemImage




and tblItemEvents :
ITEMEVENTID
ITEMID
ORGID
ItemEventType
EventDate
CurrentStatus
OpStatus
EventRemarks


Items are linked to the associated Events using the PK [tblItem.ITEMID] and FK [tblItemEvents.ITEMID], and the Events with [tblItemEvents.ITEMEVENTID].


The subItemEvents subform, resides in the ctlItemEvents control, on the frmItem main form.


The EventDates are sorted in Descending order in the subform.


I intended to place the Event Procedure in the EventDate After_Update.


DoCmd.SetWarnings False

DoCmd.RunSQL "UPDATE tblItem INNER JOIN tblItemEvents ON tblItem.ITEMID=tblItemEvents.ITEMID " & _
"SET tblItemEvents.CurrentStatus = Yes " & _
"WHERE tblItem.[ITEMID] = [Forms]![frmItem]![ctlItemEvents].[Form]![ITEMID] AND tblItemEvents.ITEMEventID = [Forms]![frmItem]![ctlItemEvents].[Form]![ITEMEventID] AND Max(tblItemEvents.EventDate);"

DoCmd.SetWarnings True


Me.Requery



OR...should I be doing the SQL UPDATE in reverse? Set all of the Event "CurrentStatus" to "NO" for each ITEM, and then set the Item's latest Event CurrentStatus to "YES"?


OR perhaps doing it with an IF-THEN-ELSE function?


Any advice would be greatly appreciated. Thanks.

 

[Lou P]

Re: Update Set Value based on Max(Date)...New Code

Help...I've redone the code...
__________________________
Private Sub LOCATION_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblItem INNER JOIN tblItemEvents ON tblItem.ITEMID=tblItemEvents.ITEMID " & _
"SET tblItemEvents.CurrentStatus = No " & _
"WHERE tblItem.[ITEMID] = [Forms]![frmItem]![ctlItemEvents].[Form]![ITEMID] AND tblItemEvents.ITEMEventID <> [Forms]![frmItem]![ctlItemEvents].[Form]![ITEMEventID];"
DoCmd.SetWarnings True
Me.Refresh
Forms!frmItem!ctlItemEvents.Requery
End Sub
_____________________________________
This works fine except that ig the "LOCATION" is edited in any of the old Event records it changes all of the "CurrentStatus" to "NO".
I've tried every Event trigger and this is the only one that comes close to working.
Is there a way to have it not update the record with the latest date?
Woould appreciate any help. Thanks!