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.
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.
