Storing information from combobox in table

[michaelb]

Hello All, Thanks for your help in the past. I have a new problem...



I have two tables 1. TblEmployees 2. TblBranchServed. In the TblEmployees table is a field (MilitaryService) to capture military service branch information. In the second table, TblBranchServed are records listing the branches of service. I created a form with a combobox that is bound to TblBranchedServed that stores the selection from the combobox in the TblEmployees table. My problem is, it stores the key field numeric value in (MilitaryService) and not the second item listed in the combobox which is the branch of service. How can I get it to store the branch of service, i.e. Air Force, Army...

[jzwp11]

There is no need to store both the key field and the name. Additionally, relational databases are generally more efficient with numerical values and that is why they are typically used for joins between two tables (and why you would store the key value rather than the branch name in the table). You can always use a query to bring the key value together with its corresponding name. You can also use some built-in functions within Access to display the branch name value on a form if needed. Additionally, in the combo box, you can hide the key value so that the user only sees the branch name by just setting the column width property of the key field to zero.

[michaelb]

Thank you for your help jzwp11. What you said makes perfect sense. Can you suggest some built-in functions within Access I can use to display the branch name value on a form. Thanks again...

[jzwp11]

If when you make a selection from the combo box, you see the ID field, you can adjust the column widths property for that field to zero. When you do that, the next non-zero width column will be the one that is displayed in the combo box when a selection is made.

Another way is to include the branch name in the combo box (which you probably already have done) on the form. You can simply add an unbound textbox control to the form and set its control property to reference the combo box

=comboboxname.column(x)

Where x represents the column in the combo box's row source that holds the branch name. Just a little word of caution, Access starts counting the columns at zero not 1, so if the combo box's row source looks like this:

SELECT ID, BranchServedName FROM TblBranchServed

Then you would set the column # to 1 to show the branchservedname value
=comboboxname.column(1)

[alansidman]

Look at the videos on this web site for combo boxes.

http://www.datapigtechnologies.com/AccessMain.htm

Scroll down about 1/3.

Alan