Discussion: Look Up usage.

[Mihail]

In this thread the experts advice is to avoid the LOOK UP fields in tables as much as possible.

Ok. I believe they.
But, until now, I have done something like that:

tblParents:
    ID_Parent (usually an autonumber)
    ParentName
    Other fields

tblChilds:
    ID_Child (usually an autonumber)
    ID_Parent (Look Up field on tblParents
    ChildName
    Other fields

After that, in Relationship window, I can see the relation between my tables.
If I need, I can to Edit Relationship... in order to Enforce Referential Integrity and Join Type... .

From now, using Form Wizard, the forms will be well created in seconds.
I notice that in field ID_Parent (tblChilds) Access will store a long integer (an ID from tblParents)

Without using the look up fields:
- Manually establish the relationship between tables.
- Create forms
If, in order to create frmChild form, is used the Form Wizard, is necessary to change the created text box into a combo box
- Create RowSource query for combo box (tblChilds) in order to reflect the ID_Parent field (tblParents).
- Set the Column Width property to zero in order to hide ID field (I think no one wish to remember thousands IDs)

So, for me, the resulting form is the same as if I use the look up feature (with a lot of work around).
I must admit that in the tblChilds table the field ID_Parent has no more a SQL string stored in.

More than, I have used extensively the look up fields and never I encountered a problem.

Can you, the experts, to show me some examples where the look up field generate a problem ?
Or even only to develop the theory ?
Or to share your's approach ?

Thank you in advance !

[NeoPa]

The link that zmbd posted in the linked thread details the problems with LookUp fields.

Ultimately, you can choose to use them if you so desire. Just be aware that they come with these issues attached. This situation doesn't change just because you don't like the answer. You still have a choice, but our recommendations won't change no matter how many times you ask the question, and every point that's relevant has already been raised, one way or another. You have all the information available, now it's down to you to make your decision. You may choose to ignore the advice, but that won't change the advice. It just leaves you deciding for yourself how you want to proceed (which is how things should be).

[Stewart Ross]

I agree entirely with NeoPa, Mihail. The thread you linked in post #1 gives you all the information needed to make an informed decision, and those who advised against doing so have very sound reasons for giving that advice.

In my view, the use of embedded lookups within Access tables is one of the least-useful features ever added to Access. I found on the one occasion that I used it that it caused me endless problems maintaining queries and associated forms, because the lookups were masking what was going on with the underlying fields and I could not tell whether it was the key field or the lookup value I was working with at the time. I have never used such embedded lookups since.

Anyhow, only you can make the choice - but I know what mine would be.

-Stewart

[Mihail]

Ok. As I already have said, I believe you, because you're certainly a lot more skilled in Access and in databases area than me.
Now I am looking for a replacement in my mind for my approach and can't find a good one.
So: how you handle you in that situation without using the lookup fields ?

Thank you very much for replies !
Ok. I'll try, from now, to avoid to use lookup fields. But how to do that ?

[Stewart Ross]

You already outlined the general approach in your first post, Mihail.

Without using the look up fields:
- Manually establish the relationship between tables.
- Create forms
If, in order to create frmChild form, is used the Form Wizard, is necessary to change the created text box into a combo box
- Create RowSource query for combo box (tblChilds) in order to reflect the ID_Parent field (tblParents).
- Set the Column Width property to zero in order to hide ID field (I think no one wish to remember thousands IDs)

Yes it involves a little more setup to use combo boxes the way you describe, but the development effort concerned is minimal. It is also explicitly clear to you and any future maintainer what table does what - you are not relying on embedded lookups which may be very, very confusing to maintain.

-Stewart

[Mihail]

Now is clear, Stewart. Thank you very much !
I have not know that I hit the good way.
It is why I have asked you, the experts, about.

Thank you again !