If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Make a relationship table ID Field, a drop-down?
How would I get the drop-down to display the recognizable Name and input the
corresponding Key? I've got a couple of tables, one for expenses, and one for payee information. Rent Check - Condo Asso. So I've got a one to many relationship setup, where one or more expenses link to a single payee. In the expense table, I have the foreign ID field linked to the primary key in the payee table. Good so far. The problem: I’d like to make PayeeID field a drop-down box with a list of the payee’s from the payee table. Which I can with the PayeeID data type = List Box and Row Source Type = Table/Query. The problem seems to be with the Row Source. I can make it list, in the Payee Table, either the Primary Key (a list of numbers I don’t recognize) or the PayeeName (words that make sense). Having it list words that make sense, is the only way to go, but when I select “Condo” for example, it tries to put the text “Condo” in the field, not the Primary Key, so I get the error “The value you entered isn’t valid for this field” – “For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.” How would I get the drop-down to display the recognizable Name and input the corresponding Key? |
#2
|
|||
|
|||
Aaron
Given the group you posted in (tablesdbdesign) and your description, I'm going to guess that you are trying to do this inside a table, rather than in a form. I'll further speculate that you are trying to use the "lookup" data type. If so, this is what's causing your "problem". The Access lookup datatype appears to be storing the text value, when in fact, it is storing the key value of the looked up table. This causes confusion when trying, say, to query your table, since your natural inclination is to try to find the values you see (the text). A scan of this 'group will reveal a strong consensus to NOT using the lookup data type, replacing it with the long numeric (or other suitable data type consistent with the key value in the looked up table). Another strong recommendation is to NOT work directly in the tables, but rather, to use forms. If you create a form, based on your table, and add a combo box, with a row source of the looked up table, you can see the text value in your form, but you can store the key value in your primary table. The difference with this from your situation is that you will KNOW that it is a key (not the text description). And anyone using the database after you will not have to be advised that this particular table has a potentially confusing lookup data type! -- Good luck Jeff Boyce Access MVP |
#3
|
|||
|
|||
Thanks Jeff,
You are correct, I'm working in the tables to edit and add entries in my database. I've found it more intuitive to work with a large volume of fields at once then being able to only view one at a time, but I'll try the forms now again, and see if they're more helpful. I did find a solution to my problem though. I wasn't able to discern what payee a particular expense went to because the relationship linked field would only display a number, and a drop-down list would also only display a number. But, in keeping the Lookup/Listbox control, I set Bound Column to '0', which will display what ever SQL function I create but actually input the Primary Key that entry is linked to. So now, in the field, it displays the PayeeName correctly, but also input the PK and makes the link. I appreciate your help and will stick around to keep learning. ~Aaron "Jeff Boyce" wrote: Aaron Given the group you posted in (tablesdbdesign) and your description, I'm going to guess that you are trying to do this inside a table, rather than in a form. I'll further speculate that you are trying to use the "lookup" data type. If so, this is what's causing your "problem". The Access lookup datatype appears to be storing the text value, when in fact, it is storing the key value of the looked up table. This causes confusion when trying, say, to query your table, since your natural inclination is to try to find the values you see (the text). A scan of this 'group will reveal a strong consensus to NOT using the lookup data type, replacing it with the long numeric (or other suitable data type consistent with the key value in the looked up table). Another strong recommendation is to NOT work directly in the tables, but rather, to use forms. If you create a form, based on your table, and add a combo box, with a row source of the looked up table, you can see the text value in your form, but you can store the key value in your primary table. The difference with this from your situation is that you will KNOW that it is a key (not the text description). And anyone using the database after you will not have to be advised that this particular table has a potentially confusing lookup data type! -- Good luck Jeff Boyce Access MVP |
#4
|
|||
|
|||
On Thu, 9 Sep 2004 07:31:07 -0700, "Aaron"
wrote: You are correct, I'm working in the tables to edit and add entries in my database. I've found it more intuitive to work with a large volume of fields at once then being able to only view one at a time, but I'll try the forms now again, and see if they're more helpful. Note that a Form is NOT limited to viewing one record at a time! Change the default view of the form from Single to Continuous and you can see and edit multiple records onscreen. John W. Vinson[MVP] (no longer chatting for now) |
#5
|
|||
|
|||
"John Vinson" wrote:
Note that a Form is NOT limited to viewing one record at a time! Change the default view of the form from Single to Continuous and you can see and edit multiple records onscreen. Thanks John, but I'm not sure where to change this setting, I've done help searches for how to, but didn't find anything useful. ~Aaron |
#6
|
|||
|
|||
On Thu, 9 Sep 2004 14:51:09 -0700, "Aaron"
wrote: "John Vinson" wrote: Note that a Form is NOT limited to viewing one record at a time! Change the default view of the form from Single to Continuous and you can see and edit multiple records onscreen. Thanks John, but I'm not sure where to change this setting, I've done help searches for how to, but didn't find anything useful. ~Aaron Open the Form in design view. Select View... Properties from the menu; or right mouseclick the little square at the upper left intersection of the rulers and select Properties. On the "All" tab the "Default View" property is sixth from the top (in Access2002, it might be different in other versions). John W. Vinson[MVP] (no longer chatting for now) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Update table field value from form entry | S Stewart | Using Forms | 2 | September 1st, 2004 05:51 PM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |
Adding auto-numbered field screws up Table order | Carl | Database Design | 5 | May 30th, 2004 03:25 AM |
Cannot join 1:M table into M:M tables | Tom | Database Design | 4 | May 19th, 2004 10:16 PM |