Linking Table & Form Fields RRS feed

  • General discussion

  • I'll start by saying I'm fairly new to working with databases, though it isn't taking me long to get the hang of it. I am in the process of creating a database for personnel tracking. Each activity has a code. I already have a table with all of the activity codes and descriptions. What I want is when I enter the activity code into one combo box, the activity description automatically generates in a field next to it. Ideas? Any help is greatly appreciated.
    Tuesday, July 7, 2015 9:15 PM

All replies

  • If you are saying that you want to display the corresponding description to the activity code, that's easy enough to do.  You can have your combobox collect the code and description in it's underlying query. 

    "SELECT ActivityCode, ActivityDescription FROM Activities"

    In your combobox, set the following properties:

    • Column Count: 2
    • Column Widths: 1";1"
    • List Width: 2.25"  (I always set the list width to be .25" larger than the total size of the columns to account for the scroll bar)
    • Bound Column: 1
    • Limit To List: Yes

    Now create a text box to sit beside the combo box.  Set the controlsource property as =cbxActivity.Column(1).

    That's it.  Now when your combo box has a value, your text box will display the second column of the combo box, description.

    Hope this helps out.  If you have questions or comments, please feel free to post back.

    Tuesday, July 7, 2015 9:44 PM
  • Firstly, do not store the description in a column (field) in the referencing table; only the code foreign key.  To store the description also would introduce redundancy and the table would not be normalized to Third Normal Form.  Consequently it would be open to the risk of update anomalies.

    Do you really need to see the code at all, or can you just select an activity by description, which is usually more intuitive in this sort of context?  If so, in a form to select the activity use a combo box set up as follows:

    Name:                cboActivity
    ControlSource:   ActivityCode

    RowSource:       SELECT ActivityCode, ActivityDescription
                              FROM Activities
                              ORDER BY ActivityDescription;

    BoundColumn:    1
    ColumnCount:     2
    ColumnWidths:   0cm

    If your units are imperial rather than metric Access will change it automatically.  You'll be able to select an activity from the list, which will be ordered alphabetically, but the value of the control and hence the column to which it is bound will be the code.  If you want to see the code for the selected activity add a text box with a ControlSource property of =cboActivity

    Remember that if you've used spaces or other special characters in table or column names (not recommended) you must wrap them in square brackets [like this] when referencing them.

    Ken Sheridan, Stafford, England

    Tuesday, July 7, 2015 10:09 PM