Link to home
Start Free TrialLog in
Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America

asked on

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

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
Avatar of jerryb30
jerryb30
Flag of United States of America image

Is ProgramRecID text or numeric?
What results do you get?
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
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]
I am thinking you need the order by in both parts of the query, or at least the first part.
Avatar of Karen Schaefer

ASKER

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 attachedUser generated image
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
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];
if I use an orderby in both statements the last order by overrides the first.
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
that did the trick, disregard my last question.  Thanks for the assist.
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
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

"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
it is Zero, and it will no update to goto the correct record.

k
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.
Well, I thought all (no pun) was working, and only the sorting was not correct ...?
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
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
looking for code that will display all records if "All" is selected.

k
If the union query (not on the form) is working, make another query whose source is the union query, applying your sorts.
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
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
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