Lookup Fields in Tables (1 Viewer)

[mkaeser]

Hello all,

Figured since I'm on a role of question all that is my knowledge of access...

I have read several articles/blogs/threads/whatevers that recommend against using the lookup wizard at the table level, and that you should instead use this in forms. It seems to cause a lot of issues when working with an SQL server, but what if your database will be on a network, should you still not use this type of field at table level? Thanks!

 

[pbaldy]

You probably saw this one?

http://access.mvps.org/access/lookupfields.htm

I personally don't/wouldn't use them. I hate not seeing the actual contents of a table, and the other quirks noted in that article as well. If you understand the quirks and still want to use them, it's your call.

 

[CJ_London]

It seems to cause a lot of issues when working with an SQL server,

lookups at the table level are only available in ms access.

SQL server, MySQL, Oracle etc do not use them - so it is not a problem for them, only you if you've used table lookups and decide to upsize in the future.

but what if your database will be on a network, should you still not use this type of field at table level?

No - even more reason not to do so. One of the problems with lookups in tables is it slows performance, and networks can be slow anyway so you are only compounding the problem.

 

[mkaeser]

I would like for anyone with some knowledge in Access design to be able to step into this database and see what is happening in a glance, INSTEAD of having to trace things back to their original source. Or encountering fields that are called "Type" and have a value of 1 or 2, but NO description of what the 1 or 2 MEANS!!!! I didn't even think you could name a field TYPE, I thought it was a reserved word :banghead:.
Any who, I like the idea of getting rid of these look up fields. Might as well since I seem to be starting at the beginning with everything else right? Thanks for the tips!

 

[Solo712]

You probably saw this one?

http://access.mvps.org/access/lookupfields.htm

I personally don't/wouldn't use them. I hate not seeing the actual contents of a table, and the other quirks noted in that article as well. If you understand the quirks and still want to use them, it's your call.

I hear you but the problem is : what is the better solution ? If you are referencing a customer, you want the user to see a name, but the FKs are numbers. I have built a pop-up search form that allows the user to search for a reference by name and the selection is a number (with the name rendered in an associated textbox next to it). I am happy with it, some of my users aren't. They say it's confusing. Ok, so what else is there ?

Best,
Jiri

 

[mkaeser]

Is there a reason why you wouldn't use a select query and dlookup to get the customer name associated with the customer ID, that way your users don't have to do a secondary search to get the right customer ID?

 

[spikepl]

There is no problem. The important thing is simply to keep data storage separate from data presentation at all times, because these are two entirely separate issues in Access.

Lookup fields in tables yield absolutely nothing presentation-wise that cannot be created using standard tools. But they just supply a lot of confusion for no good purpose.

 

[spikepl]

#6 Yes there is. Because that is not how such things are done. In a form you'd use a combo bound to an id but displaying the correpsonding name and getting data from a lookup table. To display a whole bunch of names you'd use a query which would select data from both tables related by, say, the UserID. Open a query designer and play with it. Or else do a query toutorial.

 

[pbaldy]

I hear you but the problem is : what is the better solution ? If you are referencing a customer, you want the user to see a name, but the FKs are numbers. I have built a pop-up search form that allows the user to search for a reference by name and the selection is a number (with the name rendered in an associated textbox next to it). I am happy with it, some of my users aren't. They say it's confusing. Ok, so what else is there ?

Best,
Jiri

At the simplest, a combo bound to the ID field but only displaying the name. The users can start typing a name and the list will jump to what they start typing.

 

[Solo712]

At the simplest, a combo bound to the ID field but only displaying the name. The users can start typing a name and the list will jump to what they start typing.

IOW, "search as you type". I haven't tried it recently but it did not work for me last time I did. The requerying business after every keystroke can be really a messy affair with slower machines and fast typists. BTW, this technique masks the ID field as well, which you said you dislike. So, basically you are stuck with synchronizing the ID field on the form with the combo box.

Best,
Jiri

 

[pbaldy]

Not "search as you type", just using the auto expand property of the combo. If the user types a "C", it will jump to the C's in the list, and it's instantaneous. I said I didn't like looking in a table and seeing text when what's really being stored is a number.

 

[Solo712]

Not "search as you type", just using the auto expand property of the combo. If the user types a "C", it will jump to the C's in the list, and it's instantaneous. I said I didn't like looking in a table and seeing text when what's really being stored is a number.

Ok, I get it. Funny, I never actually used the autoexpand though most of my combos have the feature turned on. I guess it came to be neglected after I opted for the FNLN notation in the text box. Thanks for reminding me though.

Best,
Jiri