I have 3 tables, tblIncidents, tblEmployees, and tblIncidentEmployees. In order to enter data into tblEmployees and associate an EmployeeID with an IncidentID in the associative table, I have a form bound to EmployeeID. On this form I have a subform also bound to EmployeeID that contains a combo box showing a list of all IncidentID in tblIncidents, and when I select an IncidentID it writes the value to the associative table.
This approach works when there are only a few Incidents in the table, but there will soon be hundreds of them and the users will have to scroll for a long time to find the IncidentID they are looking for. I have found a solution that involves unbound combo boxes with the value selected in ComboBox1 affecting/filtering the values displayed in ComboBox2. However, my form needs to be bound to EmployeeID in order to update when I am in different Employee records, so that solution doesn't seem to work. The only way (I think) that this would work is if I could sort the Incidents by date or month and then have that value filter what Incidents display in the next combo box. There is a date field in tblIncidents that I could use for this, but being sort of new to Access I'm not really sure if this is possible or where to even start.