Avatar of Karen Schaefer
Karen Schaefer
Flag 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
Microsoft AccessSQL

Avatar of undefined
Last Comment
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

8/22/2022 - Mon
jerryb30

Is ProgramRecID text or numeric?
What results do you get?
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]
jerryb30

I am thinking you need the order by in both parts of the query, or at least the first part.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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 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 - Former Microsoft Access MVP)

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 Schaefer

ASKER
if I use an orderby in both statements the last order by overrides the first.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Karen Schaefer

ASKER
that did the trick, disregard my last question.  Thanks for the assist.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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 Schaefer

ASKER
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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"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 Schaefer

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

k
Karen Schaefer

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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

ASKER
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 Schaefer

ASKER
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Karen Schaefer

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

k
jerryb30

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 - Former Microsoft Access MVP)

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Karen Schaefer

ASKER
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 - Former Microsoft Access MVP)

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