Need to upadate a field on main form to match a specific record on subform

[Mubashir sabir]

I have a main form with Patient demographics and a subform with different messages about that patient. It's a 1 to many relationship. The subform is sorted by date, with the latest message on top.



What I want to do is create a field on the main form and copy the latest message in it. So when I go back to a different form with continuous view with all he patients listed I can also display the latest message next to the names.

I'm a doc and not an IT person so not much background in SQL, any knowledge I have of Access is mostly through Google and fine folks like yourselves.
Hope how I explained it makes sense.

Thanks.

[ranman256]

it would be 2 queries
Q1, get the max of date: select [patiendID], MAX([DateOfEvent]) from table

then with this query get all the records, by joining [patientID] and [DateOfEvent]:
Q2 =
SELECT table.PatientID, table.Badge, table.DateOfEvent
FROM table INNER JOIN Q1 ON (table.DateOfEvent = Q1.MaxOfDateOfEvent) AND (table.PatientID = Q1.PatientID)
ORDER BY Q1.PatientID;

[Mubashir sabir]

Thanks. I will try it and let you know

[Mubashir sabir]

After researching your method I cam across the DLookUp Function. I seemed a bit easier to understand but it is not working. I made an uncontrolled box on the subform with the data to be the following:

=DLookUp("[Message]","[Patient Contact Info Table]","[MR#]='" & [Forms]![New Contact Info Subform].[MR#] & "' And [Date of Contact]= '" & [Forms]![New Contact Info Subform].[Latest Date]) & "'"

[Message] is a memo type field, [Patient Contact Info Table] is where I am looking and want the [MR#] and [Date of Contact] to match the [MR#] and [Latest Date] on the subform

[Latest Date] is an uncontrolled box with the data value being Max([Date of Contact])

I get a #Name? error. I'm sure I spelled all the fields correctly

Can you perhaps see what I may be doing wrong. I'm thinking perhaps DLOOKUP will not work with memo type field?

[Mubashir sabir]

I corrected it a bit, noticing my closing parenthesis was in the wrong place, but still no luck. So now it looks like

=DLookUp("[Message]","[Patient Contact Info Table]","[MR#]='" & [Forms]![New Contact Info Subform].[MR#] & "' And [Date of Contact]= '" & [Forms]![New Contact Info Subform].[Latest Date] & "'")

Giving me same error: #Name?