Unanswered: combobox in subform with multiple detail sections
I have a form that has a subform embedded in it. When the subform is displayed, there are multiple detail lines displayed (each line is coming from a different record in a linked table). The system tracks court cases and their hearing dates. The master file is named *Master* and the detail table is named *Hearings*. The linkage between the tables works fine. In the *Hearings* table is a field named *UPI* that is used in a combobox to link (via query) to a third table named *tblAgency* which contains a description of the UPI. The UPI_name field is not stored in the the table *Hearings*, only the UPI number.
Problem #1: When I enter data into the detail section of the subform and select a UPI with the combobox, the description from *tblAgency* is placed in the *UPI_name* field on the form. The problem is that is placed not only in the *UPI_name* field for the current record, but for all records displayed on the subform. Example:
In the table *tblAgency*:
UPI Valid name for UPI
123 Agency A
456 Agency B
What shows on form:
Hearing Date UPI UPI_name
01/01/2004 123 Agency A
06/01/2004 456 Agency A
How can I get the correct UPI name to show on each detail line?
Problem #2: After I find a record and display the info for the court case, the *UPI_name* is not displayed in the subform. How can I populate the field at this time? Obviously I want each line to have the proper *UPI_name* displayed.