Adding (All) to a combo box

[d9pierce1]

Hi all,
I have an unbound form that i am using to open a report.Has a combo on it that populates with HelperValues from a table. I want to add an "All" to the combo so that it will put all the records on that form to my report.



The after update event is:

Private Sub CboMemberRoleSelect_AfterUpdate()
     DoCmd.OpenReport "Report2", acViewPreview, , "MemberRoleID=" & Me.CboMemberRoleSelect
     DoCmd.Close acForm, Me.Name
End Sub

The RowSource is:

SELECT HelperID, HelperTypeID,HelperValue FROM tblHelper UNION SELECT 0, '(All)' FROM tblHelper WHERE HelperTypeID=14 ORDER BY [HelperValue];

Error is:
The number of columns in the two selected tables or queries of a union query do not match

Without the UNION SELECT 0, '(All)' FROM tblHelper in the rowsource I can select the HelperValue i want and it opens the report just fine to that specific selection. I want to add the "All" so that it will
open report to all of the helpervalues....not just one.

Hope this makes some sense,

Thanks
Dave

[Micron]

It's simple really. Each part of a UNION query must have the same number of fields as the message says. Either add another bogus field to one query part or restrict both parts to one column, whichever looks and works better. If you're showing 2 or 3 columns I imagine the bogus columns are going to look strange.

[d9pierce1]

SELECT HelperID, HelperTypeID,HelperValue FROM tblHelper UNION SELECT 0, '(All)' FROM tblBogus UNION SELECT 0, '(All)' FROM tblBog UNION SELECT 0, '(All)' FROM tblHelp WHERE HelperTypeID=14 ORDER BY [HelperValue];

I am going to guess this is not what you are talking about...Never having to do this before, what the heck?
Thanks

[d9pierce1]

I tried this but still no success. Get a totally new error when trying this one out....

SELECT HelperID, HelperValue FROM tblHelper UNION SELECT 0,  '(All)' FROM tblBogus UNION SELECT 1, '(All)' FROM tblBogus WHERE HelperTypeID=14 ORDER BY [HelperValue];

[d9pierce1]

OK, I got this to actually work with one exception, it gives me all the names in the tblHelper, its like the ware clause is not working?

SELECT HelperID, HelperValue FROM tblHelper UNION SELECT '0', ' All' FROM tblHelper WHERE HelperTypeID=14 ORDER BY [HelperValue];

[Micron]

Repeating sections in multiple UNIONs is not what I had in mind. If the first query returns 3 fields, the second query must also contain 3 fields. In fact, every one after the first must contain 3. Using your original, more like

SELECT '(All)',0,0 UNION SELECT HelperID, HelperTypeID,HelperValue FROM  tblHelper WHERE HelperTypeID=14 ORDER BY [HelperValue];

I have no idea if that will work because the data in your fields isn't known.

[d9pierce1]

I did get this to limit the list to what is in the Where statement (HelperTypeID=14) and the All is in the list but when you select all, it opens report blank

SELECT HelperID, HelperValue FROM tblHelper WHERE HelperTypeID=14  UNION SELECT '0', '(All)' FROM tblHelper ORDER BY [HelperValue];

[d9pierce1]

Hi Micron,
Thanks for all the suggestions. I was able to get this to do what I wanted yet it if I click on all it does not populate the report with any data. Do I need to put some vba in that to make that happen? If I select all it should do all of the fields that are in that tbl i would think....

SELECT HelperID, HelperTypeID, HelperValue FROM tblHelper WHERE HelperTypeID=14 UNION SELECT 0,0,'(All)' FROM tblHelper ORDER BY [HelperValue];

Data is HelperID = AutoNum, HelperTypeID = Number, HelperValue = Text

[Micron]

If you pick a value from such a combo, you filter a report or form based on that value. If you choose ALL you don't filter.
OR you open the form/report with a recordsource that has a WHERE clause using the value or use a recordsource that doesn't use a WHERE clause.

[d9pierce1]

Hi all,
I thank you for the input. I ended up going with a list box and after reviewing some Allen Brown info, i was able to solve this and make it even better. Thank you Allen.... So, i will consider this solved.
dave