Adding "All" to combo box - using with query

[jeffshex]

I've tried a few things and still nothing. I've got the union query that brings everything together and the combo box shows <All> at the top.
The thing is I have a report that's query looks in the Form![ReportDialog].....spot.
When the report opens, I get a blank report.
I've made sure that the bound fields and everything are correct. All I am trying to do is have the report show everything, instead of the usual one selection.

I've seen a FAQ on that here, but that didn't do it for me.
Any advice???

 

[hneal98]

just having <All> in the combo box does not do it because you would be looking for an object of "<All>". you have to write some code in the report that would change the query the report is based on. If it detects "<All>" as the selected Item, then turn off the filter, otherwise filter the item selected.

 

[jeffshex]

Yeah, I know.
Take a look at this...see if this makes sense:

http://www.tek-tips.com/faqs.cfm?fid=4538

I've spent awhile looking at it, went crosseyed.

 

[AmigoMobility]

Hello Jeff,

Don't know if you can use it but I thought I would send you this link and see if it helps you out some.

http://www.mvps.org/access/forms/frm0043.htm

HTH,
Shane

 

[hneal98]

I am assuming you put the following query from the faq in the CONTROL SOURCE of the combo box?

SELECT tblMyTable.RecID, tblMyTable.CompanyName FROM tblMyTable UNION SELECT "*", "<All>" FROM tblMyTable ORDER BY tblMyTable.CompanyName

with the correct table name you are using, And then a table name in the Row Source?

 

[jeffshex]

hneal98
I used the format, but no copy and paste. The FAQ says to use that code in the ROW SOURCE.
Should it not be there in the row source??
**I don't have access to try it out now, since I'm home but I can try a few different things tomorrow.**

Am I even on the right track?

Thanks for the advice.

 

[mp9]

Take a look at

http://www22.brinkster.com/accessory/forms/007.shtml

for an expansive explanation on adding the ALL option to your combo and also how to then use that option to do things such as option a filtered form/report/query/whatever.

 

[hneal98]

Sorry, you are correct, it was RowSource. Apparently there are several versions of this out there. Take a look at the link that mp9 posted.

 

[jeffshex]

That brinkster site isn't too bad. Their examples are a little limited though. My report is based off of a query where as theirs is just the table.

Still doing trail and error so no luck yet.

 

[HiBoo]

Another suggestion is to use the REPLACE function in your report. Change Form![ReportDialog].....spot to Replace(Form![ReportDialog].....spot,"<All>","*")

The resulting criteria is "*" instead of "<All>" so instead of returning nothing your query will return all records.

 

[jeffshex]

HiBoo

I'm interested in this.
Could you explain a little further.
An example or something may help.

Where would I find that line/code so I can change it?
Thanks!!!

 

[HiBoo]

In the reports source query you're using a filter to limit the data thats being returned. That filter is Form![ReportDialog].....spot. Now keep in mind that "....spot" is not valid code and you're of course have to modify it to refer to an actual control on the form that's open.

Lets assume the combo box is called cboSelection and is located on a form called frmReports. In the query of the report, the criteria to filter the data is currently Forms!frmReports!cboSelection. You've indicated that cboSelection is a UNION query that returns "<All>" as one of the values. This of course does not work in a query. It's only for display purposes in the control. To return all records in a query with a filter you have to return "*". To do this I recommend changing the code to

 Replace(Forms!frmReports!cboSelection,"<All>", "*")

Hope this helps...

 

[jeffshex]

That makes sense to me.
I will give it a shot.

Thanks again.

 

[jeffshex]

Still no go. It works ok when I select one individual, but the <all> still brings up blank.

Here is the Where statement I'm using:

WHERE ((([tblGeneral Database].[Employment Specialist])=Replace(Forms!frmReportDialog!cboEmploymentSpecialist,"<All>","*")) And ((tblPLACEMENT.[Start Date]) Between Forms!frmReportDialog!StartDate And Forms!frmReportDialog!EndDate));

And this is the union select I'm using (just in case)

select EmploymentSpecialist
From tblEmpSpecialists
UNION select "<All>" from tblEmpSpecialists
ORDER BY EmploymentSpecialist;

Is my replace code off???
Thanks!!!

 

[mp9]

The problem seems to me to be that you are doing an ="*" comparison, which won't pick anything up. You need to do a LIKE "*" instead.

 

[jeffshex]

Yeah, I know it needs a like * in there somewhere, just not positive on where to put it without having it error out. I've been trying a few different things without any luck.

 

[jeffshex]

I think I got it!
I changed my Union Query to this:

select EmploymentSpecialist
From tblEmpSpecialists
UNION select "*" from tblEmpSpecialists
ORDER BY EmploymentSpecialist;

In my source query for my report, I have my criteria say
LIKE Forms!frmReportDialog!cboEmploymentSpecialist

It works because if you select the * as your combo box choice...then your query pulls LIKE *

It seems to be working for me.
If I am forgetting something or overlooking something let me know.