Select on name fields and enter the ID

[Guest]

Hi

Here's the problem. I have two tables, People and Memberships. When entry is done in Memberships, the People.ID field needs to be entered. However, it is something of a pain to go and look this up manually every time

What I would like is to be able to select based on People.LastName, People.FirstName, but have the People.ID for the selected person be what is inputted into the Memberships.PeopleID.

Thanks

Glenn

 

[Guest]

Glenn, If I am not mistaken, you will need to create a relationship between People.PeopleID and Membership.PeopleID. If that does not work use an event to make Membership.PeopleID = [People.PeopleID]. I am fairly new to Access (about 2 months) but Both have worked for me.

----- Glenn Glazer wrote: -----

Hi,

Here's the problem. I have two tables, People and Memberships. When entry is done in Memberships, the People.ID field needs to be entered. However, it is something of a pain to go and look this up manually every time.

What I would like is to be able to select based on People.LastName, People.FirstName, but have the People.ID for the selected person be what is inputted into the Memberships.PeopleID.

Thanks!

Glenn

 

[Guest]

Hi, Tims

Actually, you responded before I could delete the question. After I posted, I did a search and in the thread called "Showing data from other table" I found my answer. Basically, you have to set up an n column combo box to select the rows you want to see and then create an AfterUpdate event to write the column you want into the field.

There ought to be some standardized way of doing this with a wizard or such - every table with a foreign key will need something like it

Thanks

Glen

----- Tims wrote: ----


Glenn, If I am not mistaken, you will need to create a relationship between People.PeopleID and Membership.PeopleID. If that does not work use an event to make Membership.PeopleID = [People.PeopleID]. I am fairly new to Access (about 2 months) but Both have worked for me

----- Glenn Glazer wrote: ----

Hi

Here's the problem. I have two tables, People and Memberships. When entry is done in Memberships, the People.ID field needs to be entered. However, it is something of a pain to go and look this up manually every time

What I would like is to be able to select based on People.LastName, People.FirstName, but have the People.ID for the selected person be what is inputted into the Memberships.PeopleID.

Thanks

Glenn

 

[Steve Schapel]

Glenn,

Don't be too hasty to "delete the question" (even if it were possible to
do so!). In response to reading your follow-up post, I had a look at
the other thread you mentioned, and the advice given there is poor. You
are making this more complicated than necessary. Just put a combobox on
your Memberships form, bound to the ID field, and the row source of the
combobox is a query based on the People table, with 2 columns being ID
and FullName defined as [LastName] & ", " & [FirstName]. Set the
properties of the combobox such that Column Count is 2 (to include ID,
and Fullname), and set the Column Widths property to 0;x (where x is
wide enough to display the members' names). And the Bound Column
property is 1. Ok, now when you select a member from the combobox list,
the ID is the data that will be entered into the Membership table, the
member's name is what you will see on the form, and the world is a happy
place to be.

 

[John Vinson]

What I would like is to be able to select based on People.LastName, People.FirstName, but have the People.ID for the selected person be what is inputted into the Memberships.PeopleID.

This is a very good thing to want to do, and is fact routine in any
Access database; most developers who use Autonumbers at all don't even
make them *visible* to the user, much less require the user to look
them up and type them in!

I'd suggest you use a Form to do your data entry (table datasheets are
*very* limited); on the Form, create a Combo Box bound to the
Memberships ID field.

The Row Source of the Combo Box could be a Query like

SELECT People.ID, People.LastName & ", " & People.FirstName AS
FullName FROM People ORDER BY LastName, FirstName;

If you create this query first (in the grid, or by copying and pasting
this SQL into the SQL window), the Combo Box wizard will walk you
through setting up the combo. This will *store* the ID while
*displaying* the name.