In recording Patient data, two of the elements required are Main Consultant and Other Consultant. A table called consultant_list stores ID, name, code and speciality. A combo allows the consultant name to be selected, with the control storing the ID for that consultant in a table as consultant_id. The selection automatically displays the consultant's code and speciality in the bound controls on the form, txtConsultantCode and txtSpeciality.
Problem comes with the other consultant. Additional bound controls for consultant code and speciality automatically complete with the data selected under consultant. So I used unbound controls txtOtherConsultantCode and txtOtherSpeciality and set the AfterUpdate event for cboOtherConsultant to do a DLookup() and fill in the values. Thought that worked fine until I discovered that although it fills in the values OK, the values entered for the current record are also filling in in any previous record on the form, leaving inconsistent data. Also if the other consultant name is cleared, the values in the unbound controls remain, so I wrote code on the LostFocus event to check if cboOtherConsultant is Null and if so to set the associated values to Null also, but of course that also impacts on the other records. Help!
