Require assistance in the following: In Access 2003 how to display a current data field entry from a sub form (child by identifier) in the main form after the sub form was updated?

The data field name in the sub form is for a Supervisor, as an employee changes Supervisors and the sub form is updated based on end date of 12/31/9999 the main form Supervisor data field should display the current Supervisors name. Can this be down through a select query?

Any help anyone can provide would be appreciated.

From the sub form, Me.Parent.Supervisor.text = Me.Supervisor.Text. (I can never remember: the property might be .Value...)

Thank you Brian I'm assuming that I am using an event procedure under After Update within the sub form would that be correct?

What you can do is put an AfterUpdate event in the supervisor's end date. If the end date is null and that means the supervisor still works there, then

Me.Parent.txtSupervisor = DLookUp("[strSupervisor]", "tblSupervisors", "[dtmEndDate] Is Null")

This is using fake names for the supervisor textbox, the fields in the table, and the table name listing the supervisors. Replace those with your real names.

The .Value (rather than the .Text) property is not needed but can be specified.

Also put that code in the main form's ON OPEN event.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thank you for the feedback. Are there any good resources’ regarding event procedures for a beginner to intermediate level that anyone is aware of? Books or websites? Any recommendations would help, please let me know, thank you.

The information provided worked great here is what I ended up doing:

On the main form under the Supervisor data field control source I used the following, =DLookUp("[Name]","qryName","[qryName]![EmployeeID]=Forms!frm_main_employee!frm_team_sub.Form!team_su pervisor")

Then I added an auto number id to the subform table [teams] and created a select query to order the inputted data as descending which now displays the current entry within the field.