Doesn't work with combo box with ALL options

[cindyLiu]

Hello there,



I need to use a combo box with all options in the month list. But only "ALL" shows the correct result. Rest, no matter which month I select, the result still shows the same result as selecting "ALL".
Please help!

here is my query:

SELECT distinct ReMonth As Filter, ReMonth from TBL_Student
UNION select distinct "*" As Filter, "All" as ReMonty from TBL_Student
ORDER BY ReMonth;

Thanks,
Cindy

[June7]

That's the query for combobox RowSource.

What is query that uses combobox as criteria?

[cindyLiu]

yes, that is the query for combo box RowSource. after select a month (or All) from the combo box, then a report should be opened. here is the test query which is for the report.

SELECT TBL_Student.[Temp-ID], TBL_Student.ReMonth, TBL_Student.Today, TBL_Student.Last_Name, TBL_Student.First_Name, TBL_Student.Gender, TBL_Student.BirthDate, TBL_Student.SchoolYear, TBL_Student.HomeLanguage, TBL_Student.Sibling, TBL_Student.Parent_LastName, TBL_Student.Parent_FirstName, TBL_Student.Parent_Phone, TBL_Student.Relationship
FROM TBL_Student;

Thanks,
Cindy

[cindyLiu]

Maybe need some VB code in the report?
Thanks,
Cindy

[Welshgasman]

yes, that is the query for combo box RowSource. after select a month (or All) from the combo box, then a report should be opened. here is the test query which is for the report.

SELECT TBL_Student.[Temp-ID], TBL_Student.ReMonth, TBL_Student.Today, TBL_Student.Last_Name, TBL_Student.First_Name, TBL_Student.Gender, TBL_Student.BirthDate, TBL_Student.SchoolYear, TBL_Student.HomeLanguage, TBL_Student.Sibling, TBL_Student.Parent_LastName, TBL_Student.Parent_FirstName, TBL_Student.Parent_Phone, TBL_Student.Relationship
FROM TBL_Student;

Thanks,
Cindy

No criteria in that SQL?
You have at least have to add criteria if criteria needs to be applied.

As you have it now, that selects everything.

You could just open the report with criteria, if needed and leave the query alone.
That is how I would probably approach it.

[cindyLiu]

for this report, I use 3 combo box for selecting inputs:
select month, Siteassigned, and Registrar. I use cascading combos for the last 2 combo box, and they work fine. But when I added the Select Month (pass the month), then the result for the select month is not correct.

I can attached a simple Access database to show you. but I don't know how to do it.

Thanks,
Cindy

[Micron]

Look at the top of the forum window for "How to Attach Files" link.

[cindyLiu]

Simple1.accdbSimple 2.accdb


please add all the tables from simple1 to simple2.

Thanks,
Yixin

[Micron]

for this report, I use 3 combo box for selecting inputs:
select month, Siteassigned, and Registrar. I use cascading combos for the last 2 combo box, and they work fine. But when I added the Select Month (pass the month), then the result for the select month is not correct.

Maybe the combos work fine as cascading combos, but don't you have your answer in post 5? You're not making use of the combo selections in that query sql so sure, it's going to behave as if you chose All. You need a Where clause in that sql to filter the results.

[cindyLiu]

this part doesn't work, so i removed: WHERE (((TBL_Student.Month)=[Forms]![FRM_OfficeRegistrar]![MonthSelect])

here is the query for Q_Report1 before:

SELECT TBL_Student.[Temp-ID], TBL_Student.Month, TBL_Student.Today, TBL_RegistrarTab.SIS_ID, TBL_Student.Last_Name, TBL_Student.First_Name, TBL_Student.Gender, TBL_Student.BirthDate, TBL_Student.SchoolYear, TBL_Student.HomeLanguage, TBL_Student.Sibling, TBL_Student.Parent_LastName, TBL_Student.Parent_FirstName, TBL_Student.Parent_Phone, TBL_Student.Relationship, TBL_Student.ReSiteAssigned, TBL_Student.Registrar, TBL_Student.ReAptDate, TBL_Student.Comments, TBL_RegistrarTab.Status, TBL_CompletionTab.StudentAttending
FROM (TBL_Student LEFT JOIN TBL_RegistrarTab ON TBL_Student.[Temp-ID] = TBL_RegistrarTab.[Temp-ID]) LEFT JOIN TBL_CompletionTab ON TBL_Student.[Temp-ID] = TBL_CompletionTab.[Temp-ID]
WHERE (((TBL_Student.Month)=[Forms]![FRM_OfficeRegistrar]![MonthSelect]) AND ((TBL_Student.ReSiteAssigned)=[Forms]![FRM_OfficeRegistrar]![ReSiteCom]) AND ((TBL_Student.Registrar)=[Forms]![FRM_OfficeRegistrar]![RegistrarCom]) AND ((TBL_CompletionTab.StudentAttending)=False));

[Micron]

I'd like to copy that and test, but it pastes as nonsense - probably because you didn't use code tags, but I don't think I've ever seen that problem here before.
Here's what works

WHERE (((TBL_Student.ReSiteAssigned)=[Forms]![FRM_OfficeRegistrar]![ReSiteCom]) AND 
((TBL_Student.Registrar)=[Forms]![FRM_OfficeRegistrar]![RegistrarCom]) AND ((TBL_CompletionTab.StudentAttending)=False));

but it looks the same as yours. Are you altering the query and opening the report without saving query design changes? If so, it will open the report based on the last saved version of the query. Maybe just bad data, but it sure is the wrong date in the report (based on the field label).



Not related, but
- if you're going to require all fields to have data, you should not open the report if any required ones are empty
- I would definitely advise against using reserved words for anything (IsLoaded has got to be reserved, and you're using it as a function name). Month also a no-no. Today is specific to Excel AFAIK, so while likely OK in Access, I still wouldn't use. Besides, it doesn't make sense as a field name as you're using it (to me at least).
- shouldn't use any special characters in object names either (save for underscore). You're using dash/minus signs.
- spelling of female might come back to bite you.

[cindyLiu]

I changed my query, and only select "All" got empty, rest months work fine.
how can i fix my query?

SELECT TBL_Student.[Temp-ID], TBL_Student.ReMonth, TBL_Student.Today, TBL_RegistrarTab.SIS_ID, TBL_Student.Last_Name, TBL_Student.First_Name, TBL_Student.Gender, TBL_Student.BirthDate, TBL_Student.SchoolYear, TBL_Student.HomeLanguage, TBL_Student.Sibling, TBL_Student.Parent_LastName, TBL_Student.Parent_FirstName, TBL_Student.Parent_Phone, TBL_Student.Relationship, TBL_Student.ReSiteAssigned, TBL_Student.Registrar, TBL_Student.ReAptDate, TBL_Student.Comments, TBL_RegistrarTab.Status, TBL_CompletionTab.StudentAttending
FROM (TBL_Student LEFT JOIN TBL_RegistrarTab ON TBL_Student.[Temp-ID] = TBL_RegistrarTab.[Temp-ID]) LEFT JOIN TBL_CompletionTab ON TBL_Student.[Temp-ID] = TBL_CompletionTab.[Temp-ID]
WHERE (((TBL_Student.ReMonth)=[Forms]![FRM_OfficeRegistrar]![MonthSelect]) AND ((TBL_Student.ReSiteAssigned)=[Forms]![FRM_OfficeRegistrar]![ReSiteCom]) AND ((TBL_Student.Registrar)=[Forms]![FRM_OfficeRegistrar]![RegistrarCom]));

[Micron]

What I'd probably do is build the sql in code and (perhaps) remove the constraint to use all controls. Yes, you could probably do this with only the query by incorporating Is Null in the sql but it makes for a bit of a mess in design view if you need to work with it later. However, maybe your issue is bad data. I'm getting 4 records with September/Dunn Loring/Andera, one of which is for student id 2222 and you don't even have that student id in student table. That is because of the outer joins in your query. I would think they should be inner joins. Gotta walk the mutt before I do any more looking.

EDIT - so with All I do get records and you do not. Strange. Maybe I played with something else and forgot about it. Will post your db later. Make sure the tables you posted are the ones you're really using.

[Micron]

Many ways to skin this cat
- use query that has a test for null in the criteria
- code as if all criteria inputs must be chosen
- code for any optional choices; trickier but more dynamic

I took the last approach, or else your code needed to also be modified so that once chosen, values could not be deleted, which is possible as you have designed the form. I don't see the point in the IsLoaded function (at least not as written) so put it back if you need it (it's commented out). One thing I learned here is that you cannot modify the recordsource property for a report that you're opening in Print Preview, so it required a work around. I used OpenArgs property for the report (it can handle some 32K characters). There are other options I could have used that I probably shouldn't go into as this is probably complicated enough for you as it is.

You also should have Option Explicit in every module (I do) - the setting is in vb editor. Caused me grief because of a mis-spelling. I also added the NoData event in case options chosen result in no records.

I presume that your tables db is a separate back end, so I linked your tables db to the front end (the one with the forms/reports/etc.). I don't see the point of having the student table in the front end, though.

Anyway, play with it and see what you think. In the end, your major problem with All is that your query contains no criteria for the month values, so it wouldn't matter what you chose.

Simple 2.zip

[cindyLiu]

Thanks, Micron!
but "All" still does not work.
In Oracle, we can use this in query: in ('August','July','June','September').
So I changed my query, but it still doesn't work.


SELECT distinct ReMonth As Filter, ReMonth from TBL_Student
UNION select distinct "*" As Filter, "All" as ReMonty from TBL_Student
WHERE Remonth in ('August','July','June','September')
ORDER BY ReMonth;