A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Make a relationship table ID Field, a drop-down?



 
 
Thread Tools Display Modes
  #1  
Old September 9th, 2004, 05:11 AM
Aaron
external usenet poster
 
Posts: n/a
Default 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  
Old September 9th, 2004, 01:16 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

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  
Old September 9th, 2004, 03:31 PM
Aaron
external usenet poster
 
Posts: n/a
Default

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  
Old September 9th, 2004, 06:18 PM
John Vinson
external usenet poster
 
Posts: n/a
Default

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  
Old September 9th, 2004, 10:51 PM
Aaron
external usenet poster
 
Posts: n/a
Default

"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  
Old September 10th, 2004, 07:33 PM
John Vinson
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 12:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.