List Box form from table RRS feed

  • Question

  • Hello folks,

    I am a newbie in MS Access and I would like to create a list box form.

    So, I have selected "List Box" from Design then selected "I want the list box to get...." ---> selected table with Customers and got an error (picture below).

    My question is, what am I doing wrong.

    And one more question :) I would like to use this (just to select one customer) like a condition to import data from teradata's table. Therefore, what kind of relation I need to create?

    Generally speaking, my idea is to import data from teradata table based on Customer Name criteria that I put in form.

    Thank you in advance.

    • Edited by tocy1980 Tuesday, February 25, 2020 10:10 PM
    Tuesday, February 25, 2020 10:09 PM

All replies

  • Design the CustomerList table. Perhaps it has no fields yet?

    Another test is to download the Northwind sample application, and try a quick test with its Customers table.

    -Tom. Microsoft Access MVP

    Tuesday, February 25, 2020 10:20 PM
  • Did you mean on this? It has field and data as well. It is not an empty table.

    • Edited by tocy1980 Tuesday, February 25, 2020 10:45 PM
    Tuesday, February 25, 2020 10:44 PM
  • Let's start with:

    CustomerID Autonumber, PrimaryKey

    CustomerName short text, 100 chars, required

    (the Northwind sample application could be a template for you, hint, hint. Find it under File > New)

    -Tom. Microsoft Access MVP

    Tuesday, February 25, 2020 10:51 PM
  • Perhaps it would help to take a look at how a list box is constructed, using an example from the DatabaseBasics.zip demo file in my public databases folder at:


    In this little demo file the section on 'retrieving data from the database' contains a form in which two methods of using a list box to return selected data are illustrated.  The structure of each list box is the same.

    Firstly, the RowSource property of the list box is the following query:

    SELECT Contacts.ContactID, FirstName & " " & LastName & ", " & City
    FROM Cities INNER JOIN Contacts ON Cities.CityID = Contacts.CityID
    ORDER BY Contacts.LastName, Contacts.FirstName;

    The query is not saved as a querydef object, but is defined solely by the above SQL statement as the RowSource property.  It returns all contacts from the Contacts table, but, because personal names can legitimately be duplicated, concatenates the contact's first and last names with the city in which the contact is located to distinguish between any duplicate names.  This requires the Cities table to be also included in the query, joined to the Contacts table on CityID.

    As well as the contact names and city returned in the second column, the query returns the primary key column of the Contacts table, ContactID.  This is an autonumber column, so all values returned are distinct.  It is this column which is used to identify the rows to be returned in a report opened from a button on my form.  The contact names cannot be used for this as they are not distinct.  When dealing with personal names, or any other attribute whose values might legitimately be duplicated, a numeric 'surrogate key' should always be used to identify each row.  Normally this will be an autonumber.

    When a row is selected in a list box which allows only one row to be selected at a time, the value of the list box is that in the column designated as the BoundColumn property.  In this case the BoundColumn property is 1, so it is the first column, ContactID which provides the control's value.  Showing this column in the list box would be pointless, however, as autonumber values are arbitrary and merely guarantee distinct values, with no semantic significance.  Consequently the first column is hidden by setting the first dimension of the ColumnWidths property of the list box to zero.  Only the names/city are then shown in the list, but the value of the control when a row is selected is that of the hidden ContactID column.  Note BTW that the non-key columns in my Contacts table are of Short Text data type, not Long Text.  The latter, which used to be called Memo fields, are usually for storing lengthy descriptive text, not values of attributes like customer names.  A Short Text column holds values of up to 255 characters.

    My list box differs from yours in another respect.  Mine has a MultiSelect property of Simple, which allows multiple contacts to be selected by clicking on each in turn.  In your case, as you appear to want to select one row at a time only, the MultiSelect property would be left as its default of None.

    Because my list box is multi-select it processes the selections rather differently than would be the case with your list box.  Rather than having a value it has an ItemsSelected collection.  In your case you'd simply refer to the list box by name to return its current value.  What you'd then do with that value you haven't as yet said other than that it's connected with the import of data from Teradata.  At this stage, however, the priority is to set up the list box correctly.

    Ken Sheridan, Stafford, England

    Wednesday, February 26, 2020 12:26 AM