Combo Box filter value for a 2nd Combo Box

[tbbrown32]

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.

[RuralGuy]

Did you know that a query can be the RowSource of a ComboBox. Queries can sort.

[tbbrown32]

So you would suggest setting the query as the RowSource and then allow the user to name the parameter value as a particular date they are looking for, which would then populate the list with Incidents for that particular day?

[RuralGuy]

You can reference a form Control in a query.

[nick404]

See if this video on cascading combo boxes helps you at all.

[CJ_London]

duplicate post here

http://www.access-programmers.co.uk/...d.php?t=283646

OP appears to be a serial duplicator

[RuralGuy]

@Nick404: a spot on video for this thread. Thanks.

[tbbrown32]

Thanks nick404. I've created that before in another part of my db, but I'm running into trouble when the combo box needs to be bound. The video (and what I've done in the past) is with the unbound combo boxes, so I'm not sure how to go about it when it needs to be bound and at the same time write data to the table. My approach currently involves using combo boxes and writing to the underlying table, but they do not filter subsequent combo boxes lower in the hierarchy.

[tbbrown32]

Being new to the forums, I was just made aware of the problem with cross posting, so it won't be happening again. My apologies to all

[RuralGuy]

I'll admit I don't think I've cascaded bound ComboBoxes before but I don't believe it will be a problem. I could be wrong here.

[nick404]

I agree with RuralGuy, I don't think the cbo's being bound will cause any issues. Try it out tbbrown and let us know what happens...
Else, try as suggested in posts #2 ->#4

[tbbrown32]

I wasn't able to get it to work, but at this point I think I'll just have the users manually type in instead of selecting from a text box. I had another spot in my database where I tried to do the cascaded combo boxes and it does cascade correctly, but won't write to the underlying tables. I had another spot where they write to the table, but don't cascade. I may just make the users know the department and program hierarchy themselves since I seem to be able to do one or the other but not both. I think maybe the issue is that to get it to write to the table, it is part of a subform linked to the main form and that seems to mess with the queries when I change them.

[tbbrown32]

below is the info for the other spot in my db where it won't work.

I can get the combo boxes to cascade but not write to the table, and I can
get them to write but not cascade. I think the issue is the way I've structured
my forms. For an example (I was trying to get Agency, Dept, and Program boxes to
cascade:

Tables:
Agency
Dept
Program
Incident
3 associative tables connecting Incident to Agency, Dept, and Program
respectively since an incident can be specific to each one of those.

My main form is based on table Incident, with a tabbed structure containing
various subforms. The tab with combo boxes that cascades but does not write
contains the Agency, Dept, and Program combo boxes. The tab that writes but does
not cascade contains combo boxes for Agency, Dept, and Program that reside in
subforms linked to table Incident via Link Master Fields. Any ideas for fixing
the issue of getting writes but no cascades, or cascades but no writes would be
appreciated. I've uploaded a copy of the db in case my explanation makes no
sense. The pertinent tabs are Program and Program Test.

[RuralGuy]

Thanks for the db to play with. That will help a lot.

[RuralGuy]

While I'm coming up to speed on your db, here's a link to read about "Lookup Fields": http://access.mvps.org/access/tencommandments.htm