include the Word "All" in a combo and have it displayed at top of Combo

Karen Schaefer
Karen Schaefer used Ask the Experts™
on
Help with displaying "All" at the top of list on a combo, but then have the rest of the list in ascending order by Program Title.

What am I missing?

SELECT tblPrograms.ProgramRecID, tblPrograms.[Program Title], tblPrograms.[Program Type]
FROM tblPrograms
GROUP BY tblPrograms.ProgramRecID, tblPrograms.[Program Title], tblPrograms.[Program Type]

union

SELECT "0" as ProgramRecID, "ALL" as [Program Title],"ALL" as [Program Type] FROM tblPrograms 

order by ProgramRecID, [Program Title]

Open in new window


K
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Is ProgramRecID text or numeric?
What results do you get?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
How about this, but why are you Ordering By ID, then Title ?

SELECT tblPrograms.ProgramRecID, tblPrograms.[Program Title], tblPrograms.[Program Type]
FROM tblPrograms
GROUP BY tblPrograms.ProgramRecID, tblPrograms.[Program Title], tblPrograms.[Program Type]

union

SELECT -1 as ProgramRecID, <"ALL>" as [Program Title],"ALL" as [Program Type] FROM tblPrograms

order by ProgramRecID, [Program Title]

Commented:
I am thinking you need the order by in both parts of the query, or at least the first part.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Karen SchaeferBI ANALYST

Author

Commented:
SELECT tblPrograms.ProgramRecID, tblPrograms.[Program Title], tblPrograms.[Program Type]
FROM tblPrograms
GROUP BY tblPrograms.ProgramRecID, tblPrograms.[Program Title], tblPrograms.[Program Type]

UNION SELECT "-1" as ProgramRecID, "ALL" as [Program Title],"ALL" as [Program Type] FROM tblPrograms
ORDER BY [Program Title];


Your example returns the following:
See attachedqryresults
I would like it to look like

ProgramRecID      Program Title      Program Type
-1                      ALL                       ALL
1                       A&P                       Academic
2                       Advanced Mfg      
3                       Aircraft Assembly      
4                       Basic Electronics      
5                       Composites      Academic
7                       Electronics      
8                       Fiber Optics
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
OK ... if you Order By Title, then Put <> around ALL

SELECT tblPrograms.ProgramRecID, tblPrograms.[Program Title], tblPrograms.[Program Type]
FROM tblPrograms
GROUP BY tblPrograms.ProgramRecID, tblPrograms.[Program Title], tblPrograms.[Program Type]

UNION SELECT "0" as ProgramRecID, "<ALL>" as [Program Title],"ALL" as [Program Type] FROM tblPrograms
ORDER BY [Program Title];
Karen SchaeferBI ANALYST

Author

Commented:
if I use an orderby in both statements the last order by overrides the first.
Database Architect / Application Developer
Top Expert 2007
Commented:
You can only have ORDER BY at the end of last statement


SELECT tblPrograms.ProgramRecID, tblPrograms.[Program Title], tblPrograms.[Program Type]
FROM tblPrograms
GROUP BY tblPrograms.ProgramRecID, tblPrograms.[Program Title], tblPrograms.[Program Type]

UNION SELECT "0" as ProgramRecID, "<ALL>" as [Program Title],"ALL" as [Program Type] FROM tblPrograms
ORDER BY 2


The 2 means column 2, from Left to Right.
Karen SchaeferBI ANALYST

Author

Commented:
that did the trick, disregard my last question.  Thanks for the assist.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Cool.

FYI ... here are 3 more examples - for future reference - what can be done:

SELECT EmpName, 5 FROM tblEmp
UNION SELECT "<Add New>", 0 FROM tblEMP
UNION SELECT "-----------", 1 FROM tblEMP
UNION SELECT "<Select All>", 2 FROM tblEMP
UNION SELECT "-----------", 3 FROM tblEMP
ORDER BY 2;


SELECT DISTINCT CLng(EmpID), 2 FROM tblEmp
UNION SELECT "ALL", 1 FROM tblEMP
ORDER BY 2;


SELECT EmpName, 2 FROM tblEmp GROUP BY EmpName
UNION SELECT "AUTO", 1 FROM tblEMP
ORDER BY 2;

Of course, you need to put code in the Combo AfterUpdate event to deal with the special cases, etc.

mx
Karen SchaeferBI ANALYST

Author

Commented:
what a minute by placing the -1 in the RecId it has an isssue with my afterupdate code.  I need to lookup record on form based on the selection of the combo.  it worked fine until I change the add of the  "ALL"

Private Sub cboSearch_AfterUpdate()
    Dim rs As Object

   On Error GoTo cboSearch_AfterUpdate_Error

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ProgramRecID] = " & Me![cboSearch].Column(0)
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

   On Error GoTo 0
   Exit Sub

cboSearch_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboSearch_AfterUpdate of VBA Document Form_frmPrograms"

End Sub

Open in new window

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"what a minute by placing the -1 in the RecId it has an isssue with my afterupdate code. "
Well, if you are only sorting by Title now ... then you don't need the minus 1 (-1) ... just make it zero (0) like before ... assuming that was working ?

mx
Karen SchaeferBI ANALYST

Author

Commented:
it is Zero, and it will no update to goto the correct record.

k
Karen SchaeferBI ANALYST

Author

Commented:
what do I need to do to the afterupdate code to handle the All - if all then display all records, else use the selected recordID to retrieve the current record.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Well, I thought all (no pun) was working, and only the sorting was not correct ...?
Karen SchaeferBI ANALYST

Author

Commented:
ok lets step back a little.

I am attempting to include All in the combo, then retrieve the record and display the correct records on multiple subforms.  
 if all then each subform should display all Programs and related info.

If a particular record id is chosen then each subform's  should be limited to the selected RecordID.

what is the best approach for this
Karen SchaeferBI ANALYST

Author

Commented:
I thought so - I was checking the Query results not the functionality of the actual combo on the form.  When I changed the record source it broke the form.

k
Karen SchaeferBI ANALYST

Author

Commented:
looking for code that will display all records if "All" is selected.

k

Commented:
If the union query (not on the form) is working, make another query whose source is the union query, applying your sorts.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Subforms? Recordsource broke ?  wow.

Basically, it's this:

Private Sub cboSearch_AfterUpdate()

    If Me.cboSearch=0 Then
              ' Handle ALL case here
              ' whatever code or method to deal with ALL
    Else
              ' Handle a single record case here
              'by whatever method is necessary per your setup

    End If




End Sub
Karen SchaeferBI ANALYST

Author

Commented:
the problem is my subforms originally where linked by Master/Child links and now I will need to rewrite all the queries for all the attached subforms.  I was hoping to change little as possible.  this was suppose to be a quick fix.

Yes the form worked great until I wanted to add the ALL.

K
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
I'm just noting this:

"Help with displaying "All" at the top of list on a combo, but then have the rest of the list in ascending order by Program Title."

And we fixed that.  Dealing with making 'ALL" happen is really something entirely different, and they are many ways, depending on your setup.

It really ... should be a new Q.

mx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial