| Author |
Message |
    
Rastro
Citizen
Username: Rastro
Post Number: 3234
Registered: 5-2004
| | Posted on Thursday, May 25, 2006 - 3:10 pm: | 
|
I have to create a small application in Access. No choice about the tool. I'm slogging through, but one thing I'm not sure about is how to bind a drop down list value to an key value.
Let's say I have a database with two tables. One is Person, with a field called State. The other table is a list of states. I need to use an ID for state, not the state's name. I want to have a drop down list where the user sees the state name, but the state ID is what populates the table.
In a web app, I would have something like:
<select>
<option>State 1</option>
<option>State 2</option>
<option>State 3</option>
<option>State 4</option>
</select>
And the field "state" would get the value, not the name. But in Access, I only see a way to bind the actual displayed value, and not the lookup table's primary key.
Any suggestions?
JR |
wnb
Citizen
Username: Wnb
Post Number: 385
Registered: 8-2001
| | Posted on Thursday, May 25, 2006 - 3:16 pm: |
|
This is more correctly handled as a field restriction (to one of 50 values) in the Person table than a separate State table altogether, unless the value of "state" is going to be given other data attributes warranting a table.
Sorry I don't know how to do that in Access, but that's the direction I'd look in. That State table seems quite a bit of overkill for the task at hand.
|
Rastro
Citizen
Username: Rastro
Post Number: 3235
Registered: 5-2004
| | Posted on Thursday, May 25, 2006 - 3:23 pm: |
|
I oversimplified. The field is actually linked to a lookup table whose values will have to be updated periodically. |
wnb
Citizen
Username: Wnb
Post Number: 388
Registered: 8-2001
| | Posted on Friday, May 26, 2006 - 4:21 pm: |
|
In the version of Access I have here, which is 2002, there is a sample database called "Northwind Sample Database" under the Help menu. See if you have that.
IN that database is a Products table. Pull it up in design mode. There is a Supplier field there which is a pull-down list, linking to the Supplier table. It looks to populate Products with a Supplier ID but display Supplier Name in the selection. I think this is what you're trying to do, and this seems to do it.
Looks like it has the following SQL statement bound to it, which is :
SELECT DISTINCT Suppliers.SupplierID, Suppliers.CompanyName FROM Suppliers ORDER BY Suppliers.CompanyName;
|
Rastro
Citizen
Username: Rastro
Post Number: 3251
Registered: 5-2004
| | Posted on Friday, May 26, 2006 - 5:07 pm: |
|
Hmm.. that just might work. Better than the hack I have, where both columns are there, but the first column has a width of zero.
Earlster also sent me a good suggestion. At first, I couldn't figure out what he sent, but I finally got it. |
|
Closed: New threads not accepted on this page