RE: Easy Drop Down Box Question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Here is the original code from my test database for the row source of the
combo. It works fine. I think you don't need the "Null as AllStates" part.

SELECT EmployeeNum FROM tbllkEmployee UNION Select "(All)" as Bogus From
tbllkEmployee UNION Select "(All)" as Bogus From tbllkEmployee;

Your original post said you wanted to use the value of the combo as part of
your search criteria. For that you will need a query that will include the
State field from your table. The IIf code goes in the Criteria row for that
field.


"Adam" wrote:

I changed the row source to: "SELECT DISTINCT Contacts.[Designated State]
UNION Select Null as AllStates, "All States" as Bogus FROM Contacts"

However, where do I put the IIF statement?

As it stands now, after I changed the row source code, when I open the "PSV"
form and click on the dropdown menu a popup stating "Query input must contain
at least one table or query."

Ideas?

"Klatuu" wrote:

That appears to be correct.

"Adam" wrote:

Thanks for sticking with me. Here is where I am now...

RowSource for "StateV" in the form:
SELECT DISTINCT Contacts.[Designated State] FROM Contacts;

Modifyied IIF form (per your last post):
IIf([forms]![PSV]![StateV]="(All States)","*",[forms]![ PSV]![StateV])

WHERE section of my code that works for anythign except "All States":
WHERE..."AND Contacts.[Designated State]=(Forms!PSV!StateV)"

What I don't understand is how to incorporate the "UNION Select Null as
AllChoice , "(All)" as Bogus" section from the link you provided. Should I
leave the WHERE section as it currently stands and edit my Row Source code as
follows?:

SELECT DISTINCT Contacts.[Designated State] UNION Select Null as AllStates,
"All States" as Bogus FROM Contacts

Thanks for sticking w/ me,
Adam



"Klatuu" wrote:

First, here is a site that shows how to add All to a combo:

http://www.mvps.org/access/forms/frm0043.htm

Then in your query use something like this as the criteria for the state
field:
(Change the Names, this is from one of my Experimental forms.

Like IIf([forms]![zjunk]![combo3]="(All)","*",[forms]![zjunk]![combo3])


"Adam" wrote:

Ok, so I got the combo box going (I can forgo the multiple selection for now)
and it works great when a state is selected.

How do I write the SQL so if the combo box doesn't have a selected state it
will return all states? What if I were to code "All States" as the default
value?

Thanks,
Adam

"Klatuu" wrote:

Okay, if you go with a multi select list box, look in VBA Help for the
ItemsSelected property. It has a good example there of how to loop through
all the selected items.

To help out, here is a function from a form I use that has 7 different list
boxes a user can select multipe criteria from. I use this to build a string
for the filtering

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function


"Adam" wrote:

Thanks for the suggestions! I'll start working through them and will post
again if I run in to trouble.

Adam


"Klatuu" wrote:

You will not be able to use a multi select list box, as Barry suggests, as
criteria in a query. A multi select list box is a different sort of animal.
It doesn't return a value like most controls, because it is, in fact, a
collection. You will have to use VBA to create the Where condition for the
query.

As to how to create the row source, it would be the same for either the
combo or the list boxes.
SELECT DISTINCT [State] FROM Contacts;
This will return one occurance of each state in the State field.

How good are you with VBA, to use a list box to build criteria takes some
medium leverl VBA skill? I can help you with that if you are up to it.
"Adam" wrote:

How do I ake a dropdown box populate values off of the "state" field in the
"contacts" table?

Ideally, I want each state to be listed once, assuming it is already listed
in a record in "contacts".

I plan on using this form field as a query criteria, so if possible, I also
want to make it so I can select all (or a combination?) of the states.

Thanks for the assist.

-Adam
.

Relevant Pages

  • RE: Filtering a subform using many combo boxes
    ... SELECT AssignedPriority, AssignedPriorityDetail FROM tblAssignedPriority ... This will give you a combo box with a two field row source, ... I followed you instructions to the letter and every one of the criteria ... open the query "qryProjects" in design view. ...
    (microsoft.public.access.forms)
  • RE: Easy Drop Down Box Question
    ... "All States" as Bogus FROM Contacts" ... form and click on the dropdown menu a popup stating "Query input must contain ... Then in your query use something like this as the criteria for the state ... Case Else 'Multiple Selection ...
    (microsoft.public.access.forms)
  • RE: criteria failure
    ... "angie" wrote: ... criteria in all three combos the query displays data based on the most recent ... criteria selection, it somehow stores the last criteria selection instead of ...
    (microsoft.public.access.queries)
  • RE: criteria failure
    ... criteria in all three combos the query displays data based on the most recent ... criteria selection, it somehow stores the last criteria selection instead of ...
    (microsoft.public.access.queries)
  • 2 Combo Box Menu from Multiple Query
    ... Once the phase has been selected a second combo box would populate. ... I can not requery combo box 2 based on the selection of combo box 1 ... becuase of the complexity of the query to sort each phase. ... Is there a way to set the row source of combo box 2 to a specific query ...
    (comp.databases.ms-access)