Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Bound Control? (Access 2003)

    I have a form that has a combo box with a list of association names. For database puroses, the association names have a district number for sorting.
    The association name combo box appears first on the first, followed by the district number text box. I would like the district number to automatically appear in the text box based on the association name. This used to happen automatically, but our systems have changed since I first built this database, and I cannot figure out how to make it happen again. Attached is a screen shot of the form, the controls that I'm describing are on the upper right hand side.

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bound Control? (Access 2003)

    The combo box should have a hidden column for the district number. A column is hidden by setting its column width to 0 in the Column Widths property.
    Let's assume that the text box is named txtDistrict, that the combo box is named cboAssociation, and that the first (visible) column of the combo box contains the association name, and the second (hidden) column the district number.

    Method 1:
    Set the Control Source of the text box to =[cboAssociation].[Column](1)

    Method 2:
    Leave the Control Source of the text box blank, and put code in the After Update event of the combo box:

    Private Sub cboAssociation_AfterUpdate()
    Me.txtDistrict = Me.cboAssociation.Column(1)
    End Sub

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bound Control? (Access 2003)

    (An hour later, and still no screenshot)

    Yet another method. It only works if the form is bound to a table or query and if the combo box is bound to a field from this table or query.
    Create a new query based on the table or query that acts as record source of the form plus the table that lists the associations.
    Join the two on the field that uniquely identifies the association.
    Double click the join line.
    Select the option to return all records from the table/query behind the form.
    Click OK.
    Add all fields (or *) from the table/query to the query grid, plus the district number from the associations table.
    Save this query.
    Use it as record source for the form instead of the original table/query.
    You can now bind the text box to the district number field (i.e. set its Control Source to that field).

  4. #4
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bound Control? (Access 2003)

    Hi Hans,

    Sorry about the screen shot, the file size is too large to let me attach, and I've been in meeting the last 2 hours.

    In cboSociety, the column widths are set to 0"; 2"; 0". The first column is the primary key, the second is the society name and the third is the district number.

    Using both methods you've suggested (and replacing [Column](1) with [Column](3)), the txtDistrictCode still appears blank. Do I need to set the column width for column 3 to 2"?

    Thanks, Gabi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bound Control? (Access 2003)

    No, you must use [Column](2) because the columns start numbering at 0: Column(0) is the first column, Column(1) the second one and Column(2) the third one.

  6. #6
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bound Control? (Access 2003)

    Worked like a champ!

    Thanks Hans, you've saved the day once again!

Posting Permissions

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