I Want Combo box to populate IDs when text is selected [MS Access]

I have a combo box;
DeptID, DeptName
34, Advertisement
45, Writing
50, Television
67, Entertainment

When a record is selected, my combo box populates the ID.  I want to have the combo box be just the DeptName column but to populate the DeptID when DeptName is selected: like this;

so when Advertisement is selected, i want the combo box to populate 34 as the ID in my table. How can i accomplish this?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Keep the rowsource of the combo box the same, with the column count = 2....

But set the WIDTH of the ID column to zero.

This will store the ID, but display the text.
Dale FyeOwner, Dev-Soln LLCCommented:
1.  Make sure you have a DeptID field in the table that is the recordsource of the form.

2.  Modify the combo box so that the BoundColumn = 1, then set the column count property to 2 and the column width property to something like: 0, 1

This will hide the DeptID from view in the combo box, but store that value rather then the text in the DeptID column of your datasheet.
peterslove53Author Commented:
when i hide the ID column , columnWidths 0 ; 3.75, i get just the DeptName coulumn and tyhat is what gets populated in my table instead of the ID. I want the ID not the Name
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Check the other properties...

Column Count should be 2

Bound Column should be 1

You can also try recreating the combo from scratch, using the combo box wizard, selecting the option to HIDE the first/ID column.... this will also set the combo up the way you want it to.
Also, your rowsource property should be something like

SELECT DeptID, DeptName FROM Departments

Open in new window

(With the ID as the first column in the SELECT statement)
peterslove53Author Commented:
Thatz exactly my setting.
Column Count should be 2
Bound Column should be 1
 columnWidths 0 ; 3.75

but still not the ID is populated but the text
peterslove53Author Commented:
could you test that. I am getting a different result
Dale FyeOwner, Dev-Soln LLCCommented:
I'm guessing that your table is actually configured with a DeptID column, but that in design view, it is configured as a lookup, and therefore displays the DeptName column from some other table.

This is a common pitfall with new developers caused by Microsoft trying to be helpful, when in fact, this is just down right confusing.
REMOVE the lookup from your table.  Much is written regarding how many problems using table level lookups causes.    Once you do that, you may need to change forms/reports to use queries that join to the lookup table if they also need to show the text value and you don't want to use a combobox (as you shouldn't for a report).
peterslove53Author Commented:
i got it.  There was just one missing element. After the combobox is created, and the settings adjusted as above;
Column Count  2
Bound Column  1
 columnWidths 0 ; 3.75

i added this code in the after update event, so even though just the DeptName is displayed, the DeptID is what is populated in the text field

Private Sub Combo32_AfterUpdate()
Me.DeptID = Me.Combo32.Column(0)
End Sub
peterslove53Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for Dale Fye (Access MVP)'s comment #a40727890
Assisted answer: 0 points for peterslove53's comment #a40728245

for the following reason:

Thanks Dale Fye (Access MVP)
Dale FyeOwner, Dev-Soln LLCCommented:
Well, if your combo box is bound to that field in the table, then it will automatically do that for you, no code required.

Set the ControlSource of the combo box to [DeptID]

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.