Populate value based on most recent date

[Poohbear0471]

Good afternoon, folks. This should be simple, but I can't seem to get it to work.




Form FrmContactDetails holds contact info for members of my game team.
The Action History section (subFrmMemberActionHistory) tracks when team members have joined, left, been promoted, demoted, etc.


I want the membership category in the FrmContactDetails section to automatically populate with the subform category with the most recent date.


I have QryContactRank that I believe should pull the info from TblMemberHistory, but it pulls all records instead of just the most recent.


How do I get this to pop correctly?


Also, dumb question... The template has macros built in that don't show up in the macros objects (for example, the OnLoad event for FrmContactDetails). The Convert macro to VBA isn't available. How do I do that?

[Welshgasman]

I would use Dmax() with relevant criteria. If the macro is like the embedded queries, that is to be expected?
https://docs.microsoft.com/en-us/office/vba/api/access.application.dmax

[Micron]

What do you expect to see (that would help determine what you need to do) This is a legitimate result based on your query because you are grouping on 2 different fields. Each of these records represents one distinct set of field values because there are 2 ranks, even if for the same member:

ContactID	Rank	MaxOfActionDate
1	Co-Leader	1/25/21
1	Member	9/07/20

I would use Max, not Last. Very rarely would I use Last for anything but an ordered field.

[Micron]

Maybe this?

SELECT TblContacts.ContactID, TblMemberHistory.Category, TblMemberHistory.ActionDate
FROM TblContacts INNER JOIN TblMemberHistory ON TblContacts.ContactID = TblMemberHistory.ContactID
WHERE (((TblMemberHistory.ActionDate)=(SELECT Max(ActionDate) FROM tblMemberHistory WHERE [ContactID] = Forms!FrmContactDetails.SubFrmMemberActionHistory.Form.ContactID)))
ORDER BY TblMemberHistory.ActionDate DESC;

Methinks having a field on both form and subform that you link between is the usual way. I didn't know you could link between a subform control and the parent form recordsource when the parent has no control on it for that field. Report yes, but form?

You have a recordsource error for contactID on the main form?

BTW, I don't think you can convert embedded macros. Standard macros yes, but embedded seems to be no. Most seasoned developers don't use macros which is why I'm not 100% sure.

EDIT - I see that works as a query alone when the form is open but not as a form recordsource. Will keep looking at what you have.

[Micron]

try this
EnPLeaders2.zip

Maybe your filter on the form is just temporary for testing purposes?
As I alluded to, a subfom loads first, then the main form. So if you attempt to restrict subform records based on a record in the main form it won't work without requerying the subform after the main form loads. I chose the main form Current event because I figure at some point, you're going to be navigating through records on the main form at some point.

[CJ_London]

if you need the record to be updateable you don't actually need tblContacts in your query since you have contactID in the history table. or at least change the contactID to the TblMemberHistory table. The subquery may also need to be aliased otherwise access will confuse which iteration of TblMemberHistory you are referring to

Also think you need more test data - the query will return records for all contacts who have an action on the same day.

My offering would be

SELECT *
FROM TblMemberHistory
WHERE (((TblMemberHistory.ContactID)=[Forms]![FrmContactDetails].[ContactID]) AND ((TblMemberHistory.ActionDate)=(SELECT Max(ActionDate) FROM tblMemberHistory T WHERE [ContactID] = Forms!FrmContactDetails.ContactID)))

or

SELECT *
FROM TblMemberHistory 
    INNER JOIN (SELECT ContactID, Max(ActionDate) AS MAD FROM tblMemberHistory WHERE [ContactID] = Forms!FrmContactDetails.ContactID GROUP BY ContactID)  AS m 
       ON (TblMemberHistory.ActionDate = m.MAD) AND (TblMemberHistory.ContactID = m.ContactID)

[Poohbear0471]

I want the Action History subform to display all history records for the member, but the Category field in the top portion to only display the most recent action.

So for this one, the top section should display Co-Leader as Category, but both records showing in the history.

What do you expect to see (that would help determine what you need to do) This is a legitimate result based on your query because you are grouping on 2 different fields. Each of these records represents one distinct set of field values because there are 2 ranks, even if for the same member:

ContactID	Rank	MaxOfActionDate
1	Co-Leader	1/25/21
1	Member	9/07/20

I would use Max, not Last. Very rarely would I use Last for anything but an ordered field.

[Micron]

As noted already, your main form has an issue. You have a control for Category but there is no such field in the form recordsource. I guess you'll want to add tblMemberHistory to qryContactsExtended and add the category field then bind the control to it. It might work in all cases considering the subform record is based on the Max value as previously stated. That record ID would then match the main form ID. However, I'm used to the main form driving the subform records, not the other way around as it is being attempted here.

Next time you post a db I suggest that you provide more than 1 or 2 records in order to provide more complete testing. I don't think I'm alone when I say that I don't mind giving the free help, but I shouldn't have to populate anyone's tables in order to have something to work with, especially when you know what needs to be in what fields in what tables and I don't.