Subform not displaying data

[weircolin]

Hi There

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?

Colin

 

[John W. Vinson]

Hi There

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?

Colin

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.

 

[weircolin]

Hi There

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?

Colin

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.

 

[John W. Vinson]

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.

Well... sure. Sorry if I was implying otherwise.

Your users should see only the Form; on the Form there can be a combo box with
a rowsource such as

SELECT PersonID, LastName & ", " & FirstName FROM Persons ORDER BY LastName,
FirstName;

bound to the PersonID field in the related table. That way the computer sees
the ID and the user sees the full text name, and they're both happy.

If that's what you're doing and it's not working please explain in more
detail.