Filter combo box on subform to display selections relating Main Form

[janerussel]

My Main form has two key fields. [ComplaintNo] and [IncidentNo]. Each record has a [ComplaintNo], but only some have [IncidentNo].

The Subform is related to main form by [IncidentNo]. It is based on a table "Colors". "Colors" table has an IncidentNo field and a color field. There can be many IncidentNos that have a corresponding color.

The subform contains a combo box with a list of colors. I would like the combo box to contain only the colors that relate to the incident number on the record on the main form. Each record on the main form can have a different set of colors depending on what the Incident No is.

Users can populate the Colors table separately with all the colors that apply to a particular incident.

I can't get the combo box to filter the colors so only the ones related to the incident are displayed.

Any solutions?

 

[DougP]

What is it doing, showing all records, or none or are you getting an error?

DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[janerussel]

Thanks for responding. Yes it is showing all records. I tried typing [forms]![Mainform]![IncidentID] in the criteria for the underlying combo box query. But this doesn't seem to work. If I type an incident number in the criteria on the underlying query, the list will filter on that incident. But I need it to automatically filter by the incidentID in the main form.

Each incident has a different set of colors that belong to it. So I need to have the combo box display only those colors.

 

[San22]

considering Form1 and Form2
Form1 having complaintNo and Incident No(IncNo=name of text box),CommandButton.
Form2 having IncidentNo and Color(Combo = cbo=name of the combo)
in the combo u have created go to recordsource query builder
select IncidentNo,Color as the field values
in the show box disable Incident Field.
i.e the tick mark should not be there.
in the criteria right click and select build.
select Form1.IncNo
on the command button onClick
Type
cbo.Requery.
This should work.
Any problem do get back.