Adding All to List Box

[brett621]

Hello everyone,



I have an issue with adding an "All" option to a list box. I have searched and tried things I have found but have been met with nothing but failure. Here is my issue.

I have a table which lists office locations and utilities for those locations. As you can see with the sample information below some of the locations will have multiple utilities as different companies provide gas/electric while some have a common customer.

Location Utility
12 Northern Elec
12 Northern Gas
13 Atlantic Gas/Electric

On a form I have a list box that will generate information related to transactions to be invoiced for each different utility. The goal is to have the list box let the user select which invoice information to generate. Currently it gives options for each of the utilities and will correctly generate the invoice information separately, having to click the "Generate" button after changing the selection. More often I would like to be able to generate all three invoices with one click, so having an "All" option would be helpful, while still having the option to invoice separately due to some special invoicing needs.

[RuralGuy]

I would use a UNION query for the RowSource of the ListBox.

[brett621]

I may be missing something but that will give me all the location numbers in the listbox would it not? What I was trying to do is have the list box only list each utility once and then it would use the list box to determine the correct locations (there are hundreds split between only three or four utilities).

[RuralGuy]

What is the Row Source of the List Box now?

[brett621]

This is working for now to generate the correct information but will only give me each utility as a separate option with no way to select all and still have the option to generate separately:


Select Distinct Utility from TblLocUtility


In the tblLocUtility there are two fields Location and Utility.

[RuralGuy]

So why can't you use:
Select "All" As Utility from TblLocUtility
Union Select Distinct Utility from TblLocUtility

[June7]

Are you using a parameterized query as the report RecordSource? An "All" option in the list will not return any records because no record has "All" as a value. I don't use parameterized queries. I use VBA.

DoCmd.OpenReport "reportname", , , IIf(Me.combobox = "All", "", "UtilityID=" & Me.combobox)