Need help with drop down list in access

[vkumar]

I have a drop down list in a table with name and commission rate. When I choose the name it displays but I want both the name and the commission rate to display in my table. I want to use a data entry form to enter values in the table and I want the same in the form too (when I choose a name I want the corresponding commission rate to show up). Thank you for your help

[jzwp11]

Although Access has the capability of having lookup fields in the tables, it is generally not a good idea to use them. This site explains the problems the table level lookups can cause. Also you should not be repeating information that is stored in one table in a second table,you would just use a foreign key that joins the information in the two tables.

The Lookups (combo/list boxes) are best left for forms.

If you bring the commission rate into the combo box, you can use another textbox control on your form to reference any information you brought in via the combo box using an expression: =comboboxname.column(x) where x= the column number in the combo's row source property that holds the data. Note: the columns in the combo box's row source start counting at zero not 1

So if the row source was: SELECT salespersonname, commissionrate FROM tablename, the column number for the commission rate would be 1.

[vkumar]

Thank you for your reply. I have changed my form and it is working now.

Although Access has the capability of having lookup fields in the tables, it is generally not a good idea to use them. This site explains the problems the table level lookups can cause. Also you should not be repeating information that is stored in one table in a second table,you would just use a foreign key that joins the information in the two tables.

The Lookups (combo/list boxes) are best left for forms.

If you bring the commission rate into the combo box, you can use another textbox control on your form to reference any information you brought in via the combo box using an expression: =comboboxname.column(x) where x= the column number in the combo's row source property that holds the data. Note: the columns in the combo box's row source start counting at zero not 1

So if the row source was: SELECT salespersonname, commissionrate FROM tablename, the column number for the commission rate would be 1.

[jzwp11]

You're welcome. Glad to hear that you got the form working!