Autopopulate form field based on ID associated with most recent date using DLookup

[natkin]

Hi,

I have a form where I am creating a new MED record by first selecting a person's ID from a combo box called "Subject Number". I am then trying to auto populate another form field called "MED_Name_1" based on the person's most recent MED Date in the underlying table (or "" if the person has never taken medication).

The MED_Name_1 and MED Date fields share the same table, Tbl_Meds

I have been able to use the following code to auto populate "MED_Name_1" in the form based on the selection of the Subject Number combo box:

Private Sub Subject_Number_Change()
MEDName_1.Value = DLookup("[Tbl_Meds]![MED_Name_1]", "Tbl_Meds", "[Tbl_Meds]![Subject Number] = Forms![Subsequent Meds Input Form]![Subject Number]")
End Sub

My issue is that the same person can have multiple records in Tbl_Meds with differing MED_Name_1 depending on MED Date and the current code doesn't necessarily auto populate MED_Name_1 for a new record based on the most recent MED Date in the table. I've also tried adding DMax("[MED Date]", "Tbl_Meds") to the DLookup criteria, but not entirely sure how to get it working.

I hope this makes sense. I've spent hours looking at other similar questions, but can't find the same type of question answered. I appreciate any help. Thanks!

-Nina

 

[natkin]

Edit: I realized I may have confused people by writing:

"I have a form where I am creating a new MED record by first selecting a person's ID from a combo box called "Subject Number". I am then trying to auto populate another form field called "MED_Name_1" based on the person's most recent MED Date in the underlying table (or "" if the person has never taken medication)."

What I meant is that I am trying to autopopulate form field "MED_Name_1" based on the selected person from form combo box "Subject Number" as well as by the most recent MED Date for that person saved in the underlying table. At this moment, I can only autopopulate "MED_Name_1" based on the selected person from combo box "Subject Number". I need help writing code to also include a condition about the most recent MED Date.

Hope this is clear.

 

[natkin]

I figured it out! The following code works!

Private Sub Subject_Number_Change()
MEDName_1.Value = DMax("[Tbl_Meds]![MED_Name_1]", "Tbl_Meds", "[Tbl_Meds]![Subject Number] = Forms![Subsequent Meds Input Form]![Subject Number]")
End Sub