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

natkin

New Member
Joined
Jan 7, 2014
Messages
3
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:

Code:
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

natkin

New Member
Joined
Jan 7, 2014
Messages
3
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

New Member
Joined
Jan 7, 2014
Messages
3
I figured it out! The following code works!

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top