Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Sunrise, Florida
    Thanked 0 Times in 0 Posts

    ListBox (Access 2K)

    I have a query ("MyRecords") that links two tables ("Subject" & "Action") by a common "Number" Data Type field ("SubjectID"). The "Subject" table has only two fields, "SubjectID" and "SubjectName". The "SubjectID" field is a required field in both tables. The "MyRecords" query is the source for a form also called "MyRecords". For those instance when I don't remember if a particular record exists in the "Subject" table, I would like to have a "command button" that would bring up some sort of "listbox" or "combobox" that would display the names in the "Subject" table in alpha order. If the name exists, I want to click on it and have the "SubjectID" entered in the form. Otherwise, I would like to be able to add a new name to the "Subject" table. If anyone has, and could share with me the instructions/code to accomplish this, I would greatly appreciate it. Thanks in advance!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: ListBox (Access 2K)

    I think your form could be based on the Action table alone. If you want to ise the query, it should contain the SubjectID field from the Action table, not the one from the Subject table.

    You can put a combo box on the form, bound to the SubjectID field, with the following properties:
    - Column Count: 2
    - Column Widths: 0";1"
    - Control Source: SubjectID
    - Row Source Type: Table/Query
    - Row Source: either the name of a query based on the Subject table that sorts the records on SubjectName, or the equivalent SQL string:

    SELECT * FROM Subject ORDER BY SubjectName

    - Bound Column: 1
    - Limit to List: Yes

    To be able to add new names, you must use the On Not In List event. This is easiest if SubjectID in the Subject table is an AutoNumber field. See <post#=314477>post 314477</post#> for examples of using the On Not In List event.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts