none
Dependent combo box in sub form RRS feed

  • Question

  • I have two tables with table1 – ID (auto number), field1,  field2 and field3  Table2- ID (non auto) , field1, field2,  field3,field4.

     I have created a form in designer with sub form with ID (combo box) bound to table2.I want to populate Field1 and Field 2 when I select ID field. I have this in row source of field1 = [id].[field1] and field2 =[id].[field2]. When I select ID Field its displays #Name in other two fields. Can someone tell me how can I do this?        

    Wednesday, June 1, 2016 12:32 AM

Answers

  • >>>I have tried to follow the instructions but it still displays  #name? in field1 and field2. can some one check it please 

    According to your description, you coulde set Combox Box control's column count property to 3, refer to below:




    then set TextBox control's Control Source property like below:

    =[id].[column](1)

    The result:

    For more information, click here to refer about ComboBox.Column Property (Access)

    • Marked as answer by alex0210 Wednesday, June 8, 2016 10:33 AM
    Monday, June 6, 2016 8:20 AM
  • I also don't quite follow your post.  For sure the #Name means that it does not recognize the control name you are using - whether that be a typo or whatever.....

    Your title states combo box but you don't mention that type control in the post itself.

    A combo box or look up field type can display multiple fields during the selection - but is then bound to 1 field after the selection is made.  The other fields can be called into or written into available fields in your form (or sub form). 

    To call them in you use an unbound field with: =me.ComboboxControlName.column(2)

    this data is display only

    To write them in you use a bound field and in the AfterUpdate event of the combobox you have:

    me.FieldName=me.ComboboxControlName.column(2)

    In both cases the 'column' is literal while the (2) you change to whichever column/field you are dealing with.

    • Marked as answer by alex0210 Wednesday, June 8, 2016 10:31 AM
    Monday, June 6, 2016 1:02 PM

All replies

  • Your question is a little unclear, but I *THINK* you are trying to use two text boxes to display the contents of the second and third columns of your ID combo box.

    If that is what you are trying to do, you need to use the "Column" of your combo box in the reference.  So if the row source of your ID combo box is

    SELECT ID, Field1, Field2, Field3 FROM Table1

    Then the control sources of field1 and field2 should be:

    = ID.Column(1)
    
    and
    
    = ID.Column(2)
    

    Note that the column index is zero based, so the first column is 0, then 1,2, etc


    Miriam Bizup Access MVP

    Wednesday, June 1, 2016 9:05 AM
  • Hi alex0210,

    According to your description, I suggest that you could follow mbizup's suggestion. In addition you could refer to below:
    On the property sheet, on the data tab click on the 3 dot button of the Control Source property:

    =DLookUp("[Field1]","Table2","[Table2].[ID] = " & Str([ComboBoxName].[Text]))

    In addition could you provide more information about your issue, for example screenshot etc., that will help us reproduce and resolve it.

    Thanks for your understanding.

    Thursday, June 2, 2016 8:05 AM
  • Thank you for replies. I have tried to follow the instructions but it still displays  #name? in field1 and field2. can some one check it please 

    I have this in table1

    ID   | Field1 | Field2

    -----|---------|-----------

    1    | A         | B

    2    | C         | D

    When I select 1in ID Field in sub form I want  Field1 and Field2 to auto populate with corresponding rows datas.

    ID   | Field1 | Field2

    -----|---------|-----------

    1    | A         | B

    I have added fields by selecting table1 from sub from wizard. similarly  I have added a combo box and

     replaced the  query  "SELECT [Table1].[ID] FROM [Table1];"   with  

    SELECT ID ,Field1,Field2 FROM Table1
    I have set control sources of field1 and field2  to 

    =[id].[Field1](1)      

    =[id].[Field2](2)





    Saturday, June 4, 2016 2:37 AM
  • >>>I have tried to follow the instructions but it still displays  #name? in field1 and field2. can some one check it please 

    According to your description, you coulde set Combox Box control's column count property to 3, refer to below:




    then set TextBox control's Control Source property like below:

    =[id].[column](1)

    The result:

    For more information, click here to refer about ComboBox.Column Property (Access)

    • Marked as answer by alex0210 Wednesday, June 8, 2016 10:33 AM
    Monday, June 6, 2016 8:20 AM
  • I also don't quite follow your post.  For sure the #Name means that it does not recognize the control name you are using - whether that be a typo or whatever.....

    Your title states combo box but you don't mention that type control in the post itself.

    A combo box or look up field type can display multiple fields during the selection - but is then bound to 1 field after the selection is made.  The other fields can be called into or written into available fields in your form (or sub form). 

    To call them in you use an unbound field with: =me.ComboboxControlName.column(2)

    this data is display only

    To write them in you use a bound field and in the AfterUpdate event of the combobox you have:

    me.FieldName=me.ComboboxControlName.column(2)

    In both cases the 'column' is literal while the (2) you change to whichever column/field you are dealing with.

    • Marked as answer by alex0210 Wednesday, June 8, 2016 10:31 AM
    Monday, June 6, 2016 1:02 PM