"show all" Combo Box

[JasonSummers]

In my "print reports" form I have multiple combo boxes that are bound to a query. Combo box record sources are from tables that may not be modified. I would like to add an ALL option to each combo box. What is the best way to do this?
Thanks in advance...Jason

 

[JeremyNYC]

Jason,

I'm not sure this is the way you'll want to go, but I have a method that I use all the time.

I base the combo on a UNION query that includes a dummy record for " -All- ", so that it sorts to the top. In the first column, which is hidden, this usually has a value of zero--it's got to be a value that won't be used by any value in the table your getting records from.

Here's the sql for one of these:
SELECT tblUnit.UnitName AS Unit, tblUnit.UnitID AS ID FROM tblUnit WHERE (((tblUnit.UnitID)>1) AND ((tblUnit.Retired)=False)) UNION SELECT " -ALL- " AS Unit, 0 AS ID FROM tblUnit ORDER BY Unit;

There's a button on the form to generate the report. In the code for that, branch with an if statement to see whether or not you'll use the criterion from that combo box. Here's an example of how that code might work:

Private Sub btnGenerate_Click()

If Me.cmbUnit = 0 Then
Call DoCmd.OpenReport(strRpt, acViewPreview)
Else
Call DoCmd.OpenReport("rptDiscrepByUnit", acViewPreview, , "UnitID = " & Me.cmbUnit)
End If

End Sub

Jeremy


==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[mrgulic]

I did it a little different.........

SELECT DISTINCTROW tblAppList.txtAppName, tblAppList.AppID FROM tblAppList UNION Select "(All Applications)" As Bogus, Null as AllChoice From tblAppList ORDER BY tblAppList.txtAppName;

 

[JasonSummers]

Thanks for the input...I look forward to trying both ideas. I have been out of the office and have not had time to implement them. Just wanted to say thanks...JAson

 

[mrgulic]

I was doing the "ALL" thing again and i forgot exactly what i did and i came back here and saw that i had forgotten to add that you have to add an event action that when you select "ALL" in the combo, you have to specify a different rowsource based on he desired results in the AftefUpdate event.

i hope that this is clearer