CODEWITHSUNDEEP
sqlms-accessms-access-2010
AxxieD
AxxieD

Reputation: 614

Select Query Doesn't Show All Results

I have a combobox on a subform (ProgramSubform) in Access that's supposed to list report years for a project from a table (Program). Most projects have more than one report year, but the combobox always brings only 2014.

For example, the dropdown for project 5278 should be:

2012
2013
2014

Instead, it's only

2014

This is the select query that I'm using. It was working properly before, but I don't know when it stopped working; no changes were made to the tables or subform.

SELECT Program.ReportYear
FROM Program
WHERE (((Program.ProjNo)='ProgramSubform.ProjNo'));

Any idea why it might have stopped working, or how to fix it?

Upvotes: 3

Views: 2456

Answers (2)

HansUp
HansUp

Reputation: 97101

This WHERE clause asks Access to limit the rows returned by the query to those whose ProjNo values match the text string 'ProgramSubform.ProjNo'

WHERE (((Program.ProjNo)='ProgramSubform.ProjNo'))

But ProgramSubform.ProjNo is actually a data control on a subform. So don't include quotes around its name.

You can use a reference to the control via the parent form in the Forms collection:

WHERE Program.ProjNo= Forms![Parent Form]!ProgramSubform!ProjNo

If you're building the SELECT statement with VBA code in the form, you can include the control's value instead of its name:

"WHERE Program.ProjNo=" & Me!ProgramSubform!ProjNo.Value
"WHERE Program.ProjNo='" & Me!ProgramSubform!ProjNo.Value & "'"

Use the first version for a numeric field or the second for text.

Notes:

  1. I assumed ProgramSubform is the name of the subform control. It could also be the name of the form contained in that subform control. But those names can be different. So make sure you used the subform control name.

  2. This query is used as the Row Source for a combo box on the subform. You want to update the values displayed in the combo whenever ProjNo.Value changes. You can accomplish that by calling the combo's Requery method from the After Update event of ProjNo.

Upvotes: 1

Oscar David Diaz Fortaleché
Oscar David Diaz Fortaleché

Reputation: 134

Pay attention to the brackets, you have three before Program.ProjNo on WHERE clause, but you only need two brackets (in this case) you should use:

SELECT Program.ReportYear
FROM Program
WHERE ((Program.ProjNo='ProgramSubform.ProjNo'));

anyway you will prefer:

SELECT Program.ReportYear
FROM Program
WHERE Program.ProjNo='ProgramSubform.ProjNo';

Upvotes: 0

Related Questions