Help With Problems Creating Listboxes In A Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have looked everywhere through these discussions but cannot seem to find my
particular situation. It is as follows:

I open a blank form and in design view, I drag and drop several list boxes
onto it and point them to a query. There are 4 listboxes in total - Company,
Year, Quarter, Phase.

I need the listbox to drop down for each listbox (not to show duplicates)
and select the criteria to update against the query. I then need it to
automatically run a report from the updated query.

My first objective is to get the listbox to function properly and update the
query and return results. It is currently showing the selectable options in
the lists (including duplicates) but will not allow me to select them. I
then do not know the next step to make it update the query and then run the
query.

Many thanks in advance for any advice.
 
First, are these listboxes or combo boxes (you mentioned "drop downs")? What
is the query you are using for the Row Source? Instead of "SELECT ...." you
may need to try "SELECT DISTINCT ...". How many columns in the box? Also,
how many fields are being returned by the query used as the Row Source? Have
you set a Control Source for the boxes or are they unbound?

To use the boxes to filter a query, you would refer to the boxes in the
Criteria of the query. The form must be open when the query is used for it
to see the boxes. Remember, the Bound Column of the box is where the Value
will come from. This may not be the column that you see displayed.

Example Criteria:
Forms!frmMyForm!cboCompany
 
I would like to use List boxes if possible - Meaning, I may decide to select
criteria for all 4 categories or maybe just 2. List boxes are drop downs,
right?

I would like 4 listboxes that all have input into the query. (Company, Year,
Quarter and Phase)

I am not sure what you mean by Rowsource. I have assigned the same fields
in the query as the control source in the listboxes (Example - Company
listbox = Company field in query). I am assuming that this will correctly
bound the criteria. This will be used as a non-updating function.

I don't understand how to refer to the boxes in the criteria of the query.
I tried to set the criteria using your example format. After I did that,
nothing was showing in my listboxes on my form. I am also confused about
your statement regarding "the form must be open when the query is used to see
the boxes".

Sorry to seem like a little slow at this. I have never developed this type
of
functionality in a dB before.

Thanks for your help!
 
Listboxes will show multiple items in the list and display as many as it can
within the size of the listbox. Combo boxes have a single textbox with a
drop down arrow to open a list of items. However, both work similarly.

The Control Source is used to bind the control to a field in the form's
recordset or to create a calculated control. The Row Source is used to fill
the list with selectable items.

The box is capable of more than one column. In a combo box, the first
visible column is what will display in the textbox portion, although all
visible columns will be seen in the drop down list. This is frequently used
when you have a "lookup table" that contains a list of items, such as a name
prefix list (Mr, Mrs, Dr, etc). If this table has an ID field as well as the
text field, you may want both fields in the combo box. The ID field would be
meaningless to the user, so you would set the width of this field to zero
(hide it) and set this field as the Bound Column. In the table that you were
storing a person's name, you would then store the value of this ID field,
not the text. Later, such as in a report, when you wanted to have the name
say "Dr. John Doe", you would use a query to supply data to the report. You
would add both of these tables to the query and link them on the ID field
(the common field between them). This will allow you to get the text value
from the lookup table.

Another use (the one you appear to be wanting) for the combo box is to use
it as an unbound control to select items as filters for a query. For this,
you would use a query for the Row Source of the combo box. The query would
select values from the recordset (table) that you are wanting to filter. You
would then refer to this combo box in the query (another query, not the one
used as the Row Source) that is being used to filter the recordset.

Example:
I have a table named Table1 with the fields Address, City, State, and Zip. I
want a combo box to select which states I want to see. The combo box is
called cboState and is on a form called frmForm1. The query for the Row
Source of the combo box would be:

SELECT DISTINCT State FROM Table1 ORDER BY State;

The distinct keyword would give me each state only once in the drop down
list. I would then use a query to filter the table's results by the state
selected in the combo box.

SELECT Table1.Address, Table1.City, Table1.State, Table1.Zip
FROM Table1
WHERE Table1.State Like "*" & Forms!frmForm1!cboState & "*";

The Like operator will return items using "pattern matching". The asterisks
are "wild cards". If I asked for items "Like *ee*" it would find beep, been,
or any other word with "ee" in it. The advantage of doing this instead of

WHERE Table1.State = Forms!frmForm1!cboState

Is that if you don't make a selection in the combo box (i.e. the box is
Null) then you get all records returned.

Like "*" & Null & "*" will evaluate to Like "**"

As you can see, this is wild cards only (there is nothing to limit the
selection), so you get everything.

You would repeat the above for each of the combo boxes you want on your
form. In the final query (the one you're trying to filter using the combo
boxes), you would have only the one query, with criteria as mentioned above
for each of the combo boxes listed under their associated fields in the
query design window.

The view above is the SQL view of the query. In the query design view
window, you would place

Like "*" & Forms!frmForm1!cboState & "*"

in the Criteria box under the State field. Access would then generate the
SQL as displayed above. It will probably add a few parentheses to the SQL,
it tends to generate more of these than it needs, but that's usually not a
problem.
 
Back
Top