How do I add "All" to ComboBox and use as report criteria?

[vwgolfman]

Hi,

I have a ComboBox on a form with a CommandButton next to it.
The ComboBox is populated with the contents of a field in a table.
When the CommandButton is clicked, a report is opened in preview mode.

The Record Source for the report (query behind the report) uses the ComboBox value as a criteria.

I wish to add "All" to the ComboBox dropdown.

What would be the best way of doing this and how would I do it please?

Thank you.

 

[MSAccessRookie]

One way to do this could be

I am sure there are 100 easier ways, but this little trick might work. If the Combo Box had a Record Source that was a query similar to:

SELECT distinct("All") FROM Table1
Union ALL
(
Select ClientName from Table1
order by ClientName
);

then the resulting list should look like

"All"
{Client List in Alpha order}

 

[vwgolfman]

Hi and thanks,

Some other questions come from this...

The Record Source for the report (query behind the report) uses the value selected in the ComboBox as a criteria.

How can I ensure that if "All" is selected, it has no effect on the query?
i.e. doesn't filter at all on the criteria.

Another thing I would like to know is how can I make my ComboBox so that the user cannot type values into it?

Thanks you

 

[MSAccessRookie]

Hi and thanks,

Some other questions come from this...

The Record Source for the report (query behind the report) uses the value selected in the ComboBox as a criteria.

How can I ensure that if "All" is selected, it has no effect on the query?
i.e. doesn't filter at all on the criteria.

Another thing I would like to know is how can I make my ComboBox so that the user cannot type values into it?

Thanks you

If you use a query like the one that I suggested, then "All" is a legitimate returned value from the Union Query and will not cause any problems.

The other question I will leave to another person to answer.

 

[vwgolfman]

Hi again...

...the problem is "All" will not return all values in the query for that particular field.

The field only has 3 possible values (the ComboBox only shows 3 items).

The query behind the report has the following for that particular field as a criteria...
[Forms]![frmMenu]![Combo99]

If the user selects "All" then I want ALL records to show in the report.

How can I do this please?
Do I need to make the ComboBox return "*" or something?

 

[Brianwarnock]

I think that you want a Null row in the Combo box read Jon K 's query by Form in references
LINK

Brian

 

[vwgolfman]

Thank you very much Brian.
I will try that.

Would you also happen to know how I can make the ComboBox dropdown so that the user CANNOT type values into it?

 

[Brianwarnock]

In the properties I think you set the limit to list to yes.

Brian

 

[vwgolfman]

If I do this Brian, then the user can still type into the box as long as the text is available in the dropdown itself.

I was hoping not to allow any typing at all but I guess it doesn't matter as the control will still return the same value.

 

[boblarson]

All you have to do is to include the ALL in the combo just like was suggested, make sure there is no criteria in the query for the report itself, and in the code to open the report you just use:

If Me.ComboNameHere = "ALL" Then
   DoCmd.OpenReport "YourReportName", acViewPreview
Else
   DoCmd.OpenReport "YourReportName", acViewPreview, , "[YourFieldNameHere]='" & Me.YourComboNameHere & "'"
End If

 

[Lazy Days]

I have a query set up something like what you are wanting. My query looks at different shifts or all of them. Here is the criteria I have set for it.

Like "*" & [Enter a Shift or Press OK To See All Shifts] & "*"

 

[Brianwarnock]

Bob
hadn't noticed that we were in Modules and VBA thought that we were discussing doing it directly in a query , hence Jon K's excellent discussion, which I think would have led to Lazy Day's method if Nulls not an issue, but i thought a newbie should read it all.

Brian

 

[KenHigg]

Here's a sample that may help:

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=19302&d=1193756793

 

[vwgolfman]

Thank you for your help guys but I now have another problem.

Here is the SELECT clause which the ComboBox gets its data from...

SELECT BusinessModel, BusinessModel FROM tblData UNION SELECT '*', '(All)' FROM tblData;

This part is functioning correctly; it returns two columns, I am hiding and binding to column 1.
It returns "*" if the user selects "(All)" and the Business Model if the user selects a Business Model.


Once the user makes a selection (or selects "All") a Report is then opened (showing the SUM of Bank Branches) and populated using the following Query as its record source......

SELECT Sum(tblData.BankBranches) AS BankBranches
FROM tblData
GROUP BY tblData.BusinessModel
HAVING tblData.BusinessModel Like [Forms]![frmMenu]![Combo112];

The PROBLEM I have is that if the user selects "All" in the ComboBox then the report shows the SUM for each and every Business Model (I want it to show the OVERALL SUM).

How can I do this please?

 

[Brianwarnock]

Then you need a different query, test the value in the combo in the command button code and run one query if "All" is selected and another if not.

Brian

 

[vwgolfman]

Hi Brian,

I am in no way doubting you and I really appreciate your advice but I was hoping it would be done in the same query.
Are you sure it's not possible?

There is in fact a lot more to the query (I have simplified it for the example here) and I would dearly like to use it for either situation (ALL selected or Business Model selected).

 

[twoplustwo]

Use the combo box onclick event and build the SQL for the recordsource in there.

I added a dummy "name" in my Staff table for this.

I then chuck a where clause for the SQL in an IF statement using something like:

If sTeamMember <> "ALL" Then 'If ALL ignore WHERE statement
    sSQL = sSQL & " WHERE tblDfOwner.DF_Name = '" & sTeamMember & "'"
End If

Basically when the name selected doesn't equal "all" return those specific records.

When "all" is selected then get all records.