Alternative to lookups in tables - questions

[Russell]

Hi,

I've been reading a lot about not using lookups in tables lately.
Thus far, what I've been able to understand from my reading is this:

- Do not use lookup fields in tables because they hide what's really
stored (e.g. 'Joe Bloggs' is displayed, '4' is stored as the ID)

- Instead, use combo boxes on forms, taking their values from tables or
queries.

The area that has caused me problems with lookups is this:
I'm designing a database with three main tables: tblClients, tblSurveys
and tblSurveyItems.
Each record in tblSurveys is linked to a Client, using a lookup to the
ClientName record in tblClients.
Each record in tblSurveys has at least one related record in
tblSurveyItems.
This is because each overall survey (tblSurveys) can consist of one or
more surveys (tblSurveyItems).

I have a form displaying data from tblSurveys, with a combo box to
select ClientName. There is also a button, which opens another form
showing related records from tblSurveyItems, filtering on the SurveyID.

I've created a textbox on the form for tblSurveyItems and set it's
value to =Forms!frmSurveys!ClientName.
This should return the client name as it appears on the Surveys form.
However, due to the table-based lookup, the unique ID of the client is
returned instead.

My questions are:
- What do I need to do to my database to resolve the issue I've
described? I've tried using combo boxes on forms instead of table-based
lookups, but the unique ID is still returned instead of the real
ClientName.

- If I do use combo boxes on forms instead of table-based lookups, what
effect does this have on cascade update and delete?
If a record from tblSurveys is deleted in my database, all the related
records from tblSurveyItems need to be deleted as well.

I feel that I've only heard half the story of how to design a database
without using table-based lookups. If anyone would care to fill me in
on the full procedure, I'd be very grateful.

(I know the description of the database is rather lengthy - if anyone
would prefer to look at the real version instead of reading about it,
I'll gladly email over the file)

Thanks for your help!

[MacDermott]

>

I've created a textbox on the form for tblSurveyItems and set it's
value to =Forms!frmSurveys!ClientName.
This should return the client name as it appears on the Surveys form.
However, due to the table-based lookup, the unique ID of the client is
returned instead.
Set the ControlSource for your textbox like this:
=Forms!frmSurveys!ClientName.Column(1)

My questions are:
- What do I need to do to my database to resolve the issue I've
described? I've tried using combo boxes on forms instead of table-based
lookups, but the unique ID is still returned instead of the real
ClientName.

- If I do use combo boxes on forms instead of table-based lookups, what
effect does this have on cascade update and delete?
If a record from tblSurveys is deleted in my database, all the related
records from tblSurveyItems need to be deleted as well.
Cascade delete should do this.

I feel that I've only heard half the story of how to design a database
without using table-based lookups. If anyone would care to fill me in
on the full procedure, I'd be very grateful.

(I know the description of the database is rather lengthy - if anyone
would prefer to look at the real version instead of reading about it,
I'll gladly email over the file)

Thanks for your help!