RE: Easy Drop Down Box Question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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: Syntax Error (missing operator) in Query Expresison
    ... Private Function BuildWhereCondition(strControl As String) As String ... Dim varItem As Variant ... Dim strWhere As String ... Case Else 'Multiple Selection ...
    (microsoft.public.access.reports)
  • RE: Creating a query using multiselect list box and text boxes
    ... Dim strWhere As String ... Case Else 'Multiple Selection ...
    (microsoft.public.access.modulesdaovba)
  • Re: Require a field in search form
    ... You have the ampersand as part of the literal string, ... You could test StrWhere at the end of the code: ... If you need to remove a character or two if there is a list box selection: ... Dim StrWhere As String ...
    (microsoft.public.access.formscoding)
  • RE: Printing report based on employee selection
    ... or any number of employees. ... Here is a function that will build the Where string. ... Dim strWhere As String ... Case Else 'Multiple Selection ...
    (microsoft.public.access.reports)
  • RE: build the where clause...
    ... Private Function BuildWhereCondition(strControl As String) As String ... Dim strWhere As String ... and added to an SQL statement to define the rowsource of lbxSource. ... (even though I know there are records with both search criteria used). ...
    (microsoft.public.access.formscoding)