Lookup Tables Don't Appear to Work 2

[wreded]

i've run into this before but can't remember the answer. Using Access 2003 a field has a value referenced in a lookup table however the text isn't showing, just the reference number. It should show a combobox with a dropdown arrow to allow me to select text and store the associated number in the main table. Right now, even when i display all columns in the table cell it just shows the reference number.
i think i'm all up to date on Service Packs and updates, but i'm still having this problem. Any ideas?
Thanks,
Dave

 

[dhookom]

Many of us veterans consider lookup fields evil

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

I also don't allow users to access tables directly. Consider always using forms for all user interaction. You can use combo boxes on forms rather than lookup fields in tables.

Duane
Hook'D on Access
MS Access MVP

 

[wreded]

All that said, i'm still looking for an answer. i use lookup fields to constrain user input to certain values and use forms. i'm the only one accessing the tables directly and can't seem to get the lookups to work correctly. i'm rewriting a small application incorporating changes i've learned from this forum and to try out new things. Access 2003 is being difficult and i absolutely HATE the way Access 07 looks and behaves; by the time i have a table or query open in design half my screen is taken up by property sheets and objects, necessitating more clicks here and there until i get the screen the way i want it.
But that's beside the point. Why is Access 2003 acting like this?

Thanks,
Dave

 

[dhookom]

Go in to the table design and find the lookup field. View the lookup properties and set them like you would a combo box on a form. You may need to set the column widths to something like 0",1".



Duane
Hook'D on Access
MS Access MVP

 

[MajP]

use lookup fields to constrain user input to certain values and use forms.

Wrong! You do this on forms only and not in the table. You should never have look ups in tables, because no one should enter data directly in a table. If you want to go behind the scenes then build a query with a lookup table.

Please reread Dhookums original post. Trying to save you some pain.

 

[wreded]

Duane,
Did that, even set it to 1",1" so i could see both columns, still see just one and no line demarking the second one either. i think it's an update issue, but MS shows me as being complete up to date.
i know You guys are trying to help, but the "Wrong!" sorta gets my dander up and immediately puts me on the defensive. It's kinda like going to the dentist and he starts working before the novocaine kicks in; Ya hit a nerve and i jump.
Thanks anyway.
Dave

 

[MajP]

Sorry for getting your Dander up, but when an expert like Dhookum tells you not to use lookups in a table and provides a very compelling article, and you reply

All that said, i'm still looking for an answer. i use lookup fields to constrain user input to certain values and use forms. i'm the only one accessing the tables directly and can't seem to get the lookups to work correctly.

It seems a little arrogant because it sounds like you are disregarding the advice because you know better, and you still want someone to put a band-aid on a bad design. Good luck with the look up tables, but they will probably come back to bite you.

 

[dhookom]

What are all of the lookup properties of the field in your table design. We can't see them from our vantage.

Duane
Hook'D on Access
MS Access MVP

 

[wreded]

i read the referenced article, and anything else i could find on lookups, and didn't understand a thing. i checked a couple of other places and all i got was the "lookups are evil" but no recommendations for replacing them or alternate solutions.
Any good links for or tips for replacing the lookups? (i guess i should say; "Any i can understand?")

 

[dhookom]

There are developers (even very good ones) who use lookup fields. If your's are working as expected, we need to know the properties of your lookup fields. That is why

What are all of the lookup properties of the field in your table design

I don't allow users to interact directly with table or query datasheet views. Users should interact through forms. You can use combo boxes on the forms which provide the same type of interface as lookup fields in your table designs.

Duane
Hook'D on Access
MS Access MVP

 

[wreded]

i'm at work right now and those are at home. i'll try to get to them tonight.

 

[wreded]

Ok,
i have a table named "tblRank". It consists of 2 fields. First field is Autonumber, primary key, it has numbers 1-18 in it, and it is named "RankNum_ID". Second field holds the text values associated with different ranks.
Primary table is named "tblUsers". It has lots of fields, but the only one i can't get right is "RankNum". It is set as a Lookup

Display Control = Combo Box
Row Source Type = Table/Query
Row Source = tblRank
Bound Column = 1
Column Count = 2
Column Heads = No
Column Widths = 0"; 1"
List Rows = 16
List Width = Auto
Limit to List = Yes

i rebuilt the table and lookup link thinking i might have done something wrong, but get the same results every time. This happened at work with an MS Access update push and then went away with another one, but my system here at home says i'm up to date.
i'm stumped here.
Thanks,
Dave
(Any hints or recommendations to improve layout would be greatly appreciated.)

 

[dhookom]

I would try change the Row Source to a SQL statement:

SELECT RankNum_ID, OtherRankField
FROM tblRank
ORDER BY OtherRankField;

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

I'd define tblUsers.RankNum As Numeric,Long and foreign key to tblRank.RankNum_ID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wreded]

Complete uninstall of MS Office 2003, registry cleanup, and reinstall up to SP 2 seems to have fixed the problem. Lookups are working as expected now. i'm still open to suggestions and will try both dhookom's and PHV's.
Thanks for the suggestions.
Dave

 

[LarrySteele]

Dave,

Glad you got things worked out. With regards to using lookups in tables... It's your choice whether to use them or not. I will tell you my story and why I will not use them.

I took a job to fix a database that was no longer functioning for the business. Since the programmer was gone and there were no technical resources around, one of my first jobs was reverse-engineering the application. The programmer used a number of lookups in his tables, which really hindered my ability to analyze because what I saw in the fields was not what was stored in the fields. It made querying more difficult. Take for example a field named Status. This field would most likely contain a status code "A", but the field shows "Active". When I queried where Status = "Active", I got no rows, even though "Active" is what appeared in the table. I ran into this on quite a few fields. Suffice to say, I was NOT happy. I had other problems directly attributed to the use of lookups in tables, but I'll stop there.

Again, it's up to you if you wish to use lookups in tables. I just wanted to give you a real world example of how they can cause problems in terms I hope you can understand.

Good luck with your project.

Larry