Share via

Access - Forms - Combo AND List Boxes - display multiple fields

Anonymous
2022-08-09T12:44:18+00:00

Dear World,

I am creating a project in Access but am repeatedly encountering the same problem concerning how the content of Combo Boxes in both Tables and Forms are displayed.

The project relates to a training school, which has tables created for Students, Instructors, Lessons, Classrooms etc.

For example - the Instructor Table has four fields: the primary key field of ID, then fields for Surname, Forename and Telephone (each named with "Instructor" as a prefix).

The Lesson Form (and Lesson Table) then uses a combo box to select the appropriate Instructor from the Instructor Table - with the combo box created with the primary key field hidden, and then having the Forename field and Surname field visible.

When I am in the Datasheet view of the Table or in the Form view of the Form, the dropdown arrow and combo list appears with the Forename and Surname of the instructors being visible to select from - so far so good, the combo box is working fine.

However, when I select an Instructor and move on to the next field or record, the data displayed on the Form or in the Table is not both the Forename and Surname, it is only the first field, in this case Forename.

Clicking on the Field again shows the full details in the combo box, so the data can be reviewed, but I would like all the combo box fields to be visible on the Form (and in the table field for that matter).

I have the same issue elsewhere with Lesson Numbers, Titles and Descriptions, where the combo box displays the required fields but when moving away from the field only the first field is left displayed.

I have attempted to use a List Box to overcome the problem but that appeared to function the same way.

Your help would be appreciated - but be aware I'm not SQL familiar, so the project has been created using the Wizards and Design options, and any response that jumps heavily in SQL will leave me even more confused!

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2022-08-09T12:59:15+00:00

    Create a query based on the Instructor Table.

    Add the ID field to the first column.

    Create a calculated field in the second column:

    FullName: [Surname] & ", " & [ForeName]

    or

    FullName: [ForeName] & " " & [Surname]

    according to your preference.

    Save the query and use it as Row Source for the instructor combo box.

    Set the Column Count property of the combo box to 2 and the Column Widths property to 0.

    It will display the full name both in the text box part and in the dropdown list part of the combo box.

    1 person found this answer helpful.
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,810 Reputation points Volunteer Moderator
    2022-08-09T16:59:47+00:00

    One other point. You mention dropdowns in tables and Forms. This is NOT recommended.

    First users should never have direct access to tables. This can be dangerous and compromise data integrity. ALL interaction with data should be done through forms.

    Second, lookup fields on the table level is not recommended. This is because it masks what is stored in the tables. And can confuse the novice developer. Lookups should be done on forms using list controls like combobox and list boxes.

    0 comments
  2. Anonymous
    2022-08-09T13:30:49+00:00

    Another superb response, and also extremely quick - nicely pitched given my level of keyboard bashing ability.

    Much appreciated and problem solved.

    0 comments
  3. Anonymous
    2022-08-09T13:29:57+00:00

    Superb response, and extremely quick, much appreciated and problem solved.

    0 comments
  4. George Hepworth 22,680 Reputation points Volunteer Moderator
    2022-08-09T12:57:10+00:00

    Combo boxes can only display a single field, or column, in the closed state, which is what you describe. In the dropdown state, all of the fields, or columns are visible, which is also what you describe.

    In order to show concatenated columns, such as "First Name Last Name" in the closed state of the combo box, you must do that in the query which is used as the rowsource for the combo box, and you must make that concatenated column the visible one.

    To show the other columns, or fields, on the form itself, you can provide unbound textbox controls for them. Set their control sources to:

    =cboYourComboBoxNameGoesHere.Column(2)

    =cboYourComboBoxNameGoesHere.Column(3)

    and so on.

    The column(Index) indicates which column in the combo box to select. Combo boxes are indexed from left to right, starting with 0.

    That means column(0) is the hidden column for the ID field. Column(1) is the second column, and that's the one I'm proposing you add for the concatenated Display value, [FirstName] & " " & [LastName]. Column(3) is [FirstName], Column(4) is [LastName] and so on.

    It's impossible to work with a relational database application without learning about SQL, so that request is really not going to serve you well in the long run. It's a basic skill that you need to acquire sooner, rather than later.

    0 comments