I have a three table setup for storing a list of events each person
has attended.
1 - Person
2 - Events
3 - Attendee
I have set it up and it is working and I have a subform on each
person's record that shows which events they have attended. However,
on the table, it only shows the person's first name, and I would like
it to show both names. I Changed the lookup to the following
SELECT [Members Names].FirstName+' '+[Members Names].LastName AS Name,
[Members Names].LastName AS Expr2 FROM [Members Names];
And it works, it shows both names, but now when I select in the table
to test it a person and an event, it doesn't show in the subform on
their record.
Does anyone have any thoughts?
Take a look at
http://www.mvps.org/access/lookupfields.htm
for a critique of what many of us consider a misfeature in Access.
You should not be looking at table datasheets for data editing or reviewing
*at all*. Tables are best for storing data; use Forms for viewing and editing
data onscreen. The name data should be stored in the Person table, and *only*
in that table. You can use the Form toolbox combo box tool to create a combo
box on the Form, displaying the name but storing the unique PersonID in the
Attendee table (if that's appropriate); it is not necessary to have a lookup
field in the table in order to do so.
If your Person table does not have some sort of unique PersonID... it should.
Names are not unique. When I was in college there was a Professor John W.
Vinson in the med school; he got one of my paychecks and I got some of his tax
bills because the university and/or the credit union had not learned that
lesson.
--
John W. Vinson JVinson *at* Wysard Of Info *dot* com
--
John W. Vinson [MVP]- Hide quoted text -
- Show quoted text -
I possibly should have explained it better, however for ease I
explained it the way I did.
My table is used, and only used for storing data, I am not editing it
directly, only through queries and forms. Each person does have a
unique ID but since I am creating this database for a mental health
charity, I am trying to make it as user friendly as possible, thus
wanting to show both names of each person and not just a number.
Thanks for the info.
If I remember from a previous thread, Managing a list of events, you have a main form that shows
Events and a subform, which shows the persons who attended the events. The main form and subform
are linked by Link Child Fields: event_id and Link Master Fields: event_id.
Now it seems you want a form to show the events a person has attended. I am not sure of your table
names and column names, but I probably would approach the problem this way.
The main form is based upon the Persons table and the key is person_id. You also want a combo box
in the main form header to look up persons.
There is a subform based on a query joining table Events and table PersonEvents, which you may be
calling Attendees.
This query would be the record source for the main form and also the row source for the combobox.
Let's save it under the name Persons Query
SELECT Persons.person_id, [first_name] & " " & [last_name] AS FullName
FROM Persons
ORDER BY Persons.last_name, Persons.first_name;
This query is the record source for the subform.
Let's save it under the name PersonEvents Query
SELECT PersonEvents.event_id,
Events.event_name,
PersonEvents.person_id,
PersonEvents.attended
FROM PersonEvents
LEFT JOIN Events
ON PersonEvents.event_id = Events.event_id
WHERE PersonEvents.attended = -1;
Main form and subform are linked using
Link Child Fields: person_id
Link Master Fields : person_id.
Put the combobox in the main form header. Your code in the AfterUpdate event of the combobox can
either set a filter, use the FindFirst method of RecordsetClone, or change the main form
recordsource to show the person selected in the combobox.
