Transferring Latest Entry from Subform to Field in Main form on Load

[jlclark4]

Hey,

See screenshot below. I would like the latest entry for the Subform Status History (tblStatusHistory subform) also be visible in the field CurrentStatus in the main form (frmSystemsTracker) when loaded (and write to the main table). Is this possible? The subform is linked to the main from via the "ID" field name (neither visible in the screenshot)

Any thoughts are greatly appreciated!

I have three seperate tables/forms:

tblFollowUpInfo -- tblFollowUpInfo subform
tblStatusHistory -- tblStatusHistory subform


tblSystemsData -- frmSystemsTracker

[June7]

Why duplicate this value to the main table? That violates relational database principle not to duplicate data. It is just bad idea to save dependent data that can be calculated whenever needed.

You need to retrieve the status associated with the latest date. I suggest nested domain aggregate functions. In textbox ControlSource like:

=DLookup("Status", "tblStatusHistory", "[Date]=#" & DMax("[Date]","tblStatusHistory","WorkOrderID=" & [WorkOrderID]) & "#")

Then will need code to save the value to table. The trick is figuring out what event to put the code in:
Me!Status=Me.StatusTextboxName

What do you want to happen if a new entry is made to the History subform?

[jlclark4]

I would jsut assume it would update the status each time they opened. If they put in a new status, they would just have to close to see the current status portion update.

Now, the field name of Date is dte (in both the form and subform). And the subforms are linked by ID. So should the textbox read:

=DLookup("Status", "tblStatusHistory", "[Dte]=#" & DMax("[Dte]","tblStatusHistory","ID=" & [ID]) & "#")

[June7]

Looks right. Try it.

[jlclark4]

Did not work. I ended up with #Error.

[jlclark4]

Spelling error. It works. Thanks so much!

[June7]

That often means Access can't find name or there is syntax error.

I don't know if you have misspelled a name but syntax looks correct.

However, my suggested expression was incomplete. The outer DLookup also needs ID as criteria.

=DLookup("Status", "tblStatusHistory", "[Dte]=#" & DMax("[Dte]","tblStatusHistory","ID=" & [ID]) & "# AND ID=" & [ID])

If you want to provide db for analysis, follow instructions at bottom of my post.