Adding "new" or "other" to a query populated dropdown list?
jonceramic
My apologies for asking something that I'm sure has been answered
for. But, my google searching can't find a proper set of keywords.
I would like to add "New..." or "other..." or "Show All" to my combo
boxes.
I'm tired of having to put buttons beside them for "special options".
Current case: I've got a regular old dropdown combo box that I'm
populating with all of the available dates in a table that has many
dates. That's easy.
But, I don't know how to insert the string "New...".
Only option I've dreamed up is a Union query where I have a fake
"options" seeding table that has one row with the word "New..." typed
in. There's gotta be an easier way, right?
(I'm of course going to script a bunch of shenanigans off of the
"after update" when it equals "New...".)
TIA,
Jon
Salad
> Hi All,
>
> My apologies for asking something that I'm sure has been answered
> for. But, my google searching can't find a proper set of keywords.
>
> I would like to add "New..." or "other..." or "Show All" to my combo
> boxes.
>
> I'm tired of having to put buttons beside them for "special options".
>
> Current case: I've got a regular old dropdown combo box that I'm
> populating with all of the available dates in a table that has many
> dates. That's easy.
>
> But, I don't know how to insert the string "New...".
>
> Only option I've dreamed up is a Union query where I have a fake
> "options" seeding table that has one row with the word "New..." typed
> in. There's gotta be an easier way, right?
It's what I do.
Select ID, Name, Status From Table1 Union
Select 0,"All",Null From Table1
Order By 2
Then I check the ID in processing the value. If 0, I know it's all. I
usually put All in parentheses because I want it at the top of the list.
timmg
> jonceramic wrote:
> > Hi All,
>
> > My apologies for asking something that I'm sure has been answered
> > for. But, my google searching can't find a proper set of keywords.
>
> > I would like to add "New..." or "other..." or "Show All" to my combo
> > boxes.
>
> > I'm tired of having to put buttons beside them for "special options".
>
> > Current case: I've got a regular old dropdown combo box that I'm
> > populating with all of the available dates in a table that has many
> > dates. That's easy.
>
> > But, I don't know how to insert the string "New...".
>
> > Only option I've dreamed up is a Union query where I have a fake
> > "options" seeding table that has one row with the word "New..." typed
> > in. There's gotta be an easier way, right?
>
> It's what I do.
> Select ID, Name, Status From Table1 Union
> Select 0,"All",Null From Table1
> Order By 2
>
> Then I check the ID in processing the value. If 0, I know it's all. I
> usually put All in parentheses because I want it at the top of the list.
I use "*" instead of 0 - then the query criteria is <Like [Forms]!
[frm_Rec_Determ]![cboDESK]>
Tim Mills-Groninger
jonceramic
> > Hi All,
>
> > My apologies for asking something that I'm sure has been answered
> > for. But, my google searching can't find a proper set of keywords.
>
> > I would like to add "New..." or "other..." or "Show All" to my combo
> > boxes.
>
> > I'm tired of having to put buttons beside them for "special options".
>
> > Current case: I've got a regular old dropdown combo box that I'm
> > populating with all of the available dates in a table that has many
> > dates. That's easy.
>
> > But, I don't know how to insert the string "New...".
>
> > Only option I've dreamed up is a Union query where I have a fake
> > "options" seeding table that has one row with the word "New..." typed
> > in. There's gotta be an easier way, right?
>
> It's what I do.
> Select ID, Name, Status From Table1 Union
> Select 0,"All",Null From Table1
> Order By 2
>
> Then I check the ID in processing the value. If 0, I know it's all. I
> usually put All in parentheses because I want it at the top of the list.
Very nice. I like that. So, since you're selecting "values" rather
than fields with your union, you could put anything in.
Awesome! Many thanks.
Now, if you could conditional format the "All", that would be even
better. (e.g. the Sorting dropdowns they use in Excel for their basic
sorting/table feature.) But, I know when to quit when I'm ahead. LOL
Jon
p.s. Tim, the "*" idea is great too.
Salad
That'd work.
I usually do something like this
Dim strF as String
If Me.Combo <> 0 then strF = "ID = " & Me.Combo
...other checks if required to create the filter then
Me.Filter = strF
Me.FilterOn = (strF <> "")
I might have a SQL with various union statements so I use the first col
as a flag...anything positive is a record, anything less/equal zero then
special process is required in determining the filter.
>
> Tim Mills-Groninger
jonceramic
Certainly a good method.
Sidenote as an intermediate VBA user:
I used to spend hours, days, months constructing separate filter
statements for each control. Every time I added a new combo or radio
button group, I'd have to edit the code for a ton of controls. (For
instance, the one I'm working on has "SurveyID", "PlantID", and
"SurveyDate" as combo's that people can change on the fly. Plus a
reset button to send them back to null. I used to put all the logic
under each one. What a friggin' mess. Especially if I added a button
to the mix.)
Now, I simplify. Each "filterable" control has an afterupdate line of
strFilter = SetFilter.
Then, I create a SetFilter Function that constructs my strFilter by
looking at all of the controls that might affect what's displayed.
The function is typically lots of
IF not isnull(me.control) then
strFilter = iif(strfilter = "", "", " and ") & ---new me.control
criteria---
end if
What I've found is that using SetFilter as a function allows me to
edit in one place one time. New control selector? 1 new if/then.
New search logic? Modify one if/then. And, if I need to UPDATE on
what's displayed to the end user, I can use runsql easily. The last
line is just "WHERE " & SetFilter.
Much simpler than some of the old hack methods I originally "taught"
myself. LOL
J