none
Need to have 1 combo box fill to other fields with data RRS feed

  • Question

  • I have a database built with access 365

    I have a form that has 3 drop down combo box on the top, (Driver Name) (Driver ID) (Unit #)

    how can I get the form to fill in the (Driver ID) (Unit #) after I select the drivers name or visa versa with the other fields, like if select the (Drivers ID) then it would fill in the Drivers Name 7 Unit # 

    Sunday, January 20, 2019 5:15 AM

All replies

  • You only need two combo boxes in your form, set up as follows:

    Name:                  cboDriverName  
    ControlSource:    [Driver ID]
    RowSource:         SELECT [Driver ID], [Driver Name] FROM [YourDriversTable] ORDER BY [Driver Name];
    BoundColumn:    1
    ColumnCount:     2
    ColumnWidths:   0cm

    and:

    Name:                  cboDriverID  
    ControlSource:    [Driver ID]
    RowSource:         SELECT [Unit #], [Driver ID] FROM [YourDriversTable] ORDER BY [Driver ID];
    BoundColumn:    2
    ColumnCount:     2
    ColumnWidths:   0cm

    Add an unbound text box to the form set up as follows:

    Name:                  txtUnit
    ControlSource:    =[cboDriverID.[Column](0)

    Note that the referencing table into which data is being entered should not include a Unit # column unless a driver's unit can change and you wish their unit # at the time each earlier record was inserted to be retained in that record in the event of the driver's unit subsequently being change.  If this is the case the controls should be set up as follows:

    Name:                 cboDriverName  
    ControlSource:    [Driver ID]
    RowSource:         SELECT [Driver ID], [Unit #], [Driver Name] FROM [YourDriversTable] ORDER BY [Driver Name];
    BoundColumn:    1
    ColumnCount:     3
    ColumnWidths:   0cm;0cm;8cm

    and:

    Name:                  cboDriverID  
    ControlSource:    [Driver ID]
    RowSource:         SELECT [Unit #], [Driver ID] FROM [YourDriversTable] ORDER BY [Driver ID];
    BoundColumn:    2
    ColumnCount:     2
    ColumnWidths:   0cm

    Name:                 txtUnit
    ControlSource:    [Unit #]

    The following code would be put in the AfterUpdate event procedure of cboDriverName :

        Me.txtUnit = Me.ActiveControl.Column(1)

    and the following in the  AfterUpdate event procedure of cboDriverID

        Me.txtUnit = Me.ActiveControl.Column(0)


    Ken Sheridan, Stafford, England

    Sunday, January 20, 2019 12:55 PM