asked on

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;
DeptName
Advertisement
Writing
Television
Entertainment

so when Advertisement is selected, i want the combo box to populate 34 as the ID in my table. How can i accomplish this?

mbizup

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 Fye

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.

peterslove53

ASKER

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

mbizup

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.

mbizup

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)

peterslove53

ASKER

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

peterslove53

ASKER

could you test that. I am getting a different result

Dale Fye

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.

PatHartman

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).

peterslove53

ASKER

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

peterslove53

ASKER

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)

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial