I am using Access 2013.
I have two combo boxes in a subform. The first combo box, Facility, limits the contents of the second combo box, Unit, using an AfterUpdate macro that simply employs the requery command and references the Unit control.
The record source for the Unit combo box contains a select statement that refers back to the Facility selection (SELECT tblFacilityUnit.UnitID, tblUnits.UnitName, tblFacilityUnit.FacilityID FROM tblUnits INNER JOIN tblFacilityUnit ON tblUnits.UnitID = tblFacilityUnit.UnitID WHERE (((tblFacilityUnit.FacilityID)=[Forms]![frmErrorMain]![frmErrorDetailSUB].[Form]![Facility])) ORDER BY tblUnits.UnitName
, and it is working just fine --- at least for the first record.
I am using a datasheet view form for data entry.
When I enter the FIRST record in the datasheet, everything works great. However, when I move to the second (or any subsequent record), as soon as I make a selection in the Facility combo box (forcing the AfterUpdate event to fire, the contents of the Unit combo box for EVERY record (other than the current record) are "wiped out."
The data doesn't disappear -- it is still actually in the underlying table -- I just can no longer see it in the form. Clearly, that is not what I had intended. Users need to be able to see the data they are entering.
I would greatly appreciate any suggestions!
I have two combo boxes in a subform. The first combo box, Facility, limits the contents of the second combo box, Unit, using an AfterUpdate macro that simply employs the requery command and references the Unit control.
The record source for the Unit combo box contains a select statement that refers back to the Facility selection (SELECT tblFacilityUnit.UnitID, tblUnits.UnitName, tblFacilityUnit.FacilityID FROM tblUnits INNER JOIN tblFacilityUnit ON tblUnits.UnitID = tblFacilityUnit.UnitID WHERE (((tblFacilityUnit.FacilityID)=[Forms]![frmErrorMain]![frmErrorDetailSUB].[Form]![Facility])) ORDER BY tblUnits.UnitName

I am using a datasheet view form for data entry.
When I enter the FIRST record in the datasheet, everything works great. However, when I move to the second (or any subsequent record), as soon as I make a selection in the Facility combo box (forcing the AfterUpdate event to fire, the contents of the Unit combo box for EVERY record (other than the current record) are "wiped out."
The data doesn't disappear -- it is still actually in the underlying table -- I just can no longer see it in the form. Clearly, that is not what I had intended. Users need to be able to see the data they are entering.
I would greatly appreciate any suggestions!