Combo Box Query

[Mike Hughes]

Please help a stupid person with what may be an easy problem.

I have a form where data is entered. One of the fields on the form is "Name".

I want this field to be a combo box where the user could enter a "Name" and it would then become a part of the combo box list.

Then I would like for the users to have the ability to search the database using the "Name" in that combo box to retreve the records for the "Name" selected.

I have this done.

The problem is that I would like the "Name" in the combo box to be in alphabetical order so the user could find the "Name" quicker.

This is a very small database with only one table and is built in access 2002.

I read something about writing a query to populate the combo box, but everytime I try that the "Name" field in the database gets messed up...I search using the combo box but all the rest of the fields associated with this "Name" don't appear. The data that appears in the other fields for this "Name" are for another "Name". There are navigation buttons at the bottom of the form and they remain unchanged. Like I select a name from the combo box which is record #5 and the name shows in the combo box, but the rest of the information is for record #1. What is it that I'm doing wrong?

 

[Nouba]

After you've set up your form with the necessary controls for each field, put an unbound combo box in the header or footer of your form. The combo box wizard will give you an option for finding records and guide you through the creation process of this control. The query in the Row Source of this combo box should look like

SELECT
  Name
FROM YourTable
WHERE Name Is Not Null
GROUP BY Name
ORDER BY Name

For not getting trouble with the reserved VBA word Name it will be good to rename the field to i.e. Lastname. Otherwise you have to refer to that field in your code by putting square brackets around it.