Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm a n00b at Access, so I might not be approaching this in an Access-y way.

Say I have two tables, A and B. Every A belongs to a B, so A has a column B_id which is constrained to match the id column of some row in the B table.

So far, so good. This, I've done.

Now I want to create a form for people to create new rows in the A table. I'd like to give them a dropdown to choose which B this A belongs to, but I don't want to just display a list of valid ids - I'd rather show some other information from each B which would be more human readable - something derived from the other columns. For simplicity, let's say I'd rather display B's Name column.

I tried to just modify the SELECT statement used for the dropdown box, but that didn't work since it tried to populate the A's B_id column with values from B's Name column. What I need is have a dropdown that displays DLOOKUP("Name", "B", [B_id]), but returns value B_id.

I know how I'd do such a thing in HTML, but not in Access. Help?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

What you want to do is return more than 1 field of data in the row source for your combo box, only display the field(s) you want to be shown, and then bind the control to the correct column.

Here's an explanation of what you want with pictures that walks you through it: http://www.techonthenet.com/access/comboboxes/bind_index.php

share|improve this answer
    
Excellent, seemed to work so far. I'm using Access 2010 and I don't see a Bound Column option to set, but it seems to be working even without that. Thanks! –  rampion Oct 26 '12 at 18:18
    
Bound Column is on the Data Tab of the properties of the Combo Box (just confirmed in Access 2010.) –  Daniel Cook Oct 26 '12 at 18:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.