Filter SubForm Using ComboBox on Main Form

Question

I have a subform called frmDashBoardSub. Its record source is a table called tblProjects.m It is a continuous form.

I have a main form called frmDashbyProject. It has the same record source, tblProjects. This form has a single, unbound combo box named cboSelectProj. It has no control source, but does have a Row Source of:

SELECT tblProjects.ProjectID, tblProjects.ProjectName FROM tblProjects ORDER BY tblProjects.ProjectName;

When this main form is opened, I want the subform (frmDashBoardSub) to show ALL RESULTS in the tblProjects table.

If (and only if) someone selects a specific project from the combo box, I want the subform to update to show ONLY the selected project.

I am clearly having a mentally deficient day, because I have been looking at this and a million examples all day. It just isn't making sense to me.

If you can help, please have patience and detail out step-by-step how to make this work.

I'm hoping tomorrow my brain will be better -- but.....I have a meeting about this project at 10:30, so I could really use a little help!

Answer 1

You don't need a subform at all for this.  Simply use a bound form and put the unbound combo box in its header.  Use the following query as the form's RecordSource property:

SELECT *

FROM tblProjects

WHERE (ProjectID = Forms!frmDashbyProject!cboSelectProj

OR Forms!frmDashbyProject!cboSelectProj IS NULL)

ORDER BY ProjectName;

The unbound combo box should be set up as flows:

RowSource:     SELECT ProjectID, ProjectName FROM tblProjects ORDER BY ProjectName;

BoundColumn:   1

ColumnCount:    2

ColumnWidths:  0cm

If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

In the combo box's AfterUpdate event procedure requery the form with:

Me.Requery

Answer 2

I just read the above conversation. I am stuck on a similar issue but I have several combo boxes instead of just one.

On my main form I have 4 combo boxes. User selects class ID's from these combo boxes.

I have a subform that must display programs that match these selected Class ID's.

I am able to link one combo box to one subform and it displays the result. But my question is how do I link all these 4 combo boxes to one subform?

I am trying to do two things:

1. How to link 4 combo boxes (All pulled from one table and one field - Class ID) that are on my main form to one subform?
2. How do i display common programs that any of these 4 classes may have in another subform?

Please guide me with details. Thank you!

Answer 3

Many thanks, Ken and John! You guys rock! I'm contemplating either pumpkin cookies or some nice chocolate to get my brain working again.

I don't know why I got so hung up on the form/subform thing. But, I definitely did and spent the day in the weeds.

John - neither the form or subform would have been used for data entry -- the intent was more that of a "dashboard" to give a quick glance at some key fields in the underlying table.

In my mental fog, I got very focused on reinventing the wheel. Tomorrow, fire!

I think some days are just cloudier than others, and I just got stuck behind a really bad idea.

Answer 4

PS:  a few more refinements:

In both the forms' AfterUpdate and AfterDelConfirm event procedures requery the combo box with:

    Me.cboSelectProj.Requery

    Me.Requery

to update its list to catered for any additions, edits, or deletions of records in the form.

Answer 5

One question: WHY do you have the mainform and the subform both bound to the same table? They'll conflict! When you try to update a record for a particular project, that record will be open for updating on both forms, and Access won't be able to resolve which form is the "authorized updater".

If the mainform were unbound, and had only the unbound combo box, you could use the name of the Combo Box as the Master Link Field of the subform; it should then show all records if nothing has been selected in the combo, and only those for the selected project if something has. If this doesn't work, then you will need a little VBA code to set the Recordsource of the subform in the AfterUpdate event of the combo.