Is it possible to add a list box to a query?

Not without more understanding of the business logic and the data that you're storing than I have... or unfortunately than I have the time to acquire. There are several suggestions for resources here:

Jeff Conrad's resources page:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:

http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:

http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:

http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:

http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:

http://allenbrowne.com/links.html#Tutorials

John W. Vinson/MVP

If you open the SELECT DISTINCT... query itself, do you see the customer names you expect?

What are the ColumnCount, ColumnWidths and Bound Column properties of the combo? All three should be equal to 1.

Why are you using LIKE? the LIKE operator uses wildcards so you can use partial matches (e.g. a criterion of LIKE "J*" will find all names starting with J); here you're apparently searching for exact matches, for which the = operator will be more efficient than LIKE.

John W. Vinson/MVP

The only thing I can think that would be different is that the customer list is not in a table by itself, it is in a field in a table with all the other fields.  So, in the report John suggested above, I have all the fields, including "customer name."

Do you mean  that you reenter the customer name again every time you have a repeat sale? If so, you're not taking advantage of the fact that Access is a relational database, or perhaps you don't really want repeat business! Normally one would have a Customers table, with one row per customer, and an autonumber (or other datatype) CustomerID. This will prevent having annoyances like customers "Robert Johnson" and "Bob Johnson" and "Robert M. Johnson" and no easy way to group them as all the same person (if they in fact are the same person).

If that's what you're doing, then you'll need to use a criterion such as

LIKE [Forms]![yourformname]![cboCustomer] & "*"

And you should also change the RowSource of the combo to

SELECT DISTINCT [Trailer History].[Customer Name] FROM [Trailer History] ORDER BY [Trailer History].[Customer Name];

and set the combo's column count to 1; in this case you want to search for the customer name,  not for the TrailerHistory ID.

John W. Vinson/MVP

1.  Create a form and name it frmSwtichboard

2.  Make it open on Start-Up of Access - go to Access Options...

http://www.regina-whipp.com/index_files/Access2007Options.htm

...and where it says Display Form:...

http://www.regina-whipp.com/index_files/CurrentDatabase.htm

...select frmSwitchboard.

3.  Put a combo box on frmSwitchboard and name it cboCustomer and make the RowSource your Customer list.

--

Gina Whipp

Microsoft MVP (Access)

Please post all replies to the forum where everyone can benefit.

Let me suggest an idea for your consideration:

Create a Form named frmSwitchboard and define it as the automatic form. Put appropriate controls on it to open the other user forms in your database. Also put on it a Combo Box (not a listbox, I'll explain below)  based on the Customers table, showing the customer names in alphabetical order.

Create a Query using a reference to this combo box as a criterion: e.g.

=[Forms]![frmSwitchboard]![cboCustomer]

Include all the fields that you want to see in this query.

Base a Form (or a Report, or perhaps both) on the Query. Lay out the data as you want to see it on the screen. Let's call this frmDisplay.

Put a Macro or code in the AfterUpdate event of cboCustomer to open frmDisplay.

The user will only need to do one thing - select a customer name from the combo box. Combo boxes "autocomplete" - if the user types BAC into the combo, it will jump to the first customer name that starts with BAC (Bacchus Wine Distributors say <g>). User hits <Tab> or <Enter>, and frmDisplay appears showing the data for that customer.

The user never needs to see the query design window or the query datasheet - just a combo box (on the automatic switchboard form) and the Form displaying the data that they want to see.

Would that meet your needs?

John W. Vinson/MVP

Note that we cannot see your database, and have no idea how your table is structured, tablenames or fieldnames or the like.

That said, you can create a Query based on your table. To have that query limit the records it contains to those where the field named MyField matches the user's selection in the listbox named MyListbox on the form named MyForm, you would type

=[Forms]![MyForm]![MyListBox]

on the Criteria grid line in the query window, under the field MyField.

It may be instructive to change the view of your query to SQL view and study how the choices on the query design grid map to text in the SQL view of the query; note that the latter - the SQL text - is the "real" query; the query grid is only a tool to make it easier to build SQL strings, and some advanced queries can only be done in SQL view.

John W. Vinson/MVP

Example 1 shows if you want it to pull records that match in your query after a selection is made in the List Box...

SELECT YourTable.YourField, YourTable.AnotherField

FROM YourTable

WHERE (((YourTable.YourField)=[Forms]![frmMainMenu]![ListBox]));

Example two shows getting a subset of records after a selection is made in the List Box...

SELECT YourTable.YourField, [Forms]![frmMainMenu]![ListBox]

FROM YourTable;

--

Gina Whipp

Microsoft MVP (Access)

Please post all replies to the forum where everyone can benefit.

You cannot add a list box to a query.  You can add it to the form and then set the criteria of a field or field of the query to *read* the list box on the form.

--

Gina Whipp

Microsoft MVP (Access)

Please post all replies to the forum where everyone can benefit.