Selecting a query does not display all results
I have a combobox on a (ProgramSubform) in Access that should display the reporting years for a project from a table (Programs). Most projects have more than one reporting year, but only 2014 is always displayed in the combo box.
For example, the dropdown list for project 5278 should be:
2012 2013 2014
Instead, he only
2014
This is the select query I'm using. It used to work correctly, but I don't know when it stopped working; no changes were made to the tables or sub-format.
SELECT Program.ReportYear
FROM Program
WHERE (((Program.ProjNo)='ProgramSubform.ProjNo'));
Any idea why it might have stopped working or how to fix it?
source to share
This clause WHERE
requests 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 subform data management. Therefore, do not include quotation marks around your name.
You can reference the control through the parent form in the collection Forms
:
WHERE Program.ProjNo= Forms![Parent Form]!ProgramSubform!ProjNo
If you create a statement SELECT
with VBA code on a form, you can include a watchdog 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 version for text.
Notes:
-
I assumed ProgramSubform is the name of the subform control. It can also be the name of the form contained in this subform control. But these names can be different. Therefore, make sure to use the subform control name.
-
This query is used as the source of the rows for the combo box in the subform. You want to update the values displayed in the combo when changed
ProjNo.Value
. You can accomplish this by calling the combo methodRequery
from the After Update eventProjNo
.
source to share
Note the parentheses, you have three before the Program.ProjNo clause in the WHERE, but you only need two parentheses (in this case), you should use:
SELECT Program.ReportYear
FROM Program
WHERE ((Program.ProjNo='ProgramSubform.ProjNo'));
you would prefer either way:
SELECT Program.ReportYear
FROM Program
WHERE Program.ProjNo='ProgramSubform.ProjNo';
source to share