Values not showing in Access form combo box

Question

I have an application in Access 2003 that I am working on. In it, I have an employee table, which is connected to two other tables. The two connected tables are tables that hold a few fixed KeyWords. In my main employee table, I just have the ID from the other table, rather than having the whole word.

I wanted to make a form for entering data into these tables, so I made a query from the three tables that shows the all the regular fields of the employee table except instead of those two ID's, I showed the words themselves.

I then made the form and set the query as the RecordSource. The fields that have keywords are Combo boxes on my form, and their ControlSource is the keyword field from the query (like I mentioned earlier, the value can only be one keyword out of a list). Now, the problem I face is this: When I want to see a single record, it shows the correct value in the ComboBox, but when dropdown the menu, it doesnt show any of the other values.

What is the best way to do this? Am I doing this in the wrong way?

Thank you

Answer 1

You have set the query as the recordsource for the form. A form only shows (and ties to) ONE record at a time.

Since you want the combo box to show all the values, you should set the RecordSource of the ComboBox to your query.

Answer 2

ControlSource is the column name of the table where any user entry will be saved.

RowSource is where you type the name of the saved query or manual query typed directly into that field, which is used to provide a list of available drop down options for the combo box.

RowSourceType should be set to Table/Query, which is based on aforementioned RowSource method.

Now to correct the problem of only displaying what you begin to type (i.e. "Allow AutoCorrect" in Property Sheet > Other) or already have saved for that record without other options appearing after clicking the drop down is the following:

1. In Design View, click the combobox in question.
2. Property Sheet > Format > Column Count = set the desired number of columns to display in drop down.
3. Property Sheet > Format > Column Widths = set the desired width of column in the drop down.
4. Property Sheet > Format > List Width = set the desired width of the drop down itself.
5. Property Sheet > Data > Bound Column = set to the column of query table (i.e. 1 for ID and 2 for list options, if you only have two entries)

Note: If your desired drop down options are column two and not column one (makes things easier btw) then do the following change from above:

2. Property Sheet > Format > Column Count = set the desired number of columns to two.
3. Property Sheet > Format > Column Widths = add two entries 0",1" which means the ID field is not visible (0") and the options field is one-inch.

The easiest way to sort this out in the beginning is to view the column header names to know what you are looking at in the meantime by doing the following: Property Sheet > Format > Column Heads = set to yes. At least you will know what is showing and whether or not you are on the right track.

Answer 3

I'd approach this slightly differently, set the combo box Row Sources to include both the id and text fields (you can hide the ID column if required by setting it's width to zero). Now add the ID fields to the employee query; you shouldn't need to join the other tables to the Employee table in this query, these fields are foreign keys and the combo boxes should show the text for you automatically. Set the form Record Source to this query, now set the combo box Control Source to the appropriate ID value from the form Record Source.

If you let the wizard build a form for you based on the Employee table you will see this approach in action.

Answer 4

I had this same issue in access 2016 and the issue was with my datatype. The combo box didnt accept 'Longtext' so i just changed datatype to 'shorttext' instead and all the values appeared.