I had another tread recently than user rainlover's been helping me with. I'm still stumped though (not rainlove's fault I assure you). One point was made about lookup tables and lookup fields in tables. I've researched this with the help of a couple VIP's here and am still stumped.

My typical procedure for building follows as:

Build table structure on paper. Define fields and create proper tables with like data. For the most part when there is a relationship one of the tables becomes a lookup table for the main details table. Generally, I set the property of the FK field in the main table to a combobox and define the fields I want to use a a lookup using the wizard. This even creates the relationship. Now I have the main table that has FK's that are actually set to comboboxes from the related table. I'm going wrong somewhere with this. From what I gather, but not yet understand is that I'm not supposed to set the FK fields as lookups from the related table, but rather create the lookup somehow different (I can manually set up the relationships fine). Following this procedure yields crosstab tables automaticaly for the lookup tables, which is kind of nice.

I surmise that I should only add lookups on the form level like a lot of the other formatting should be done. If this is true, I don't know how to change a txt box on the form level to a combo. Still confused....

Thanks for any more insight on this!!!

Hi Nathan,

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm

in the design view of a form, right-click on a text box, choose ChangeTo --> combo

set:
RowSource
Columncount
ColumnWidths
ListWidth
ListRows (I like to change the default of 8 to a higher number, like 24)

it would really help you to read this:

Access Basics
http://www.utteraccess.com/forums/showflat...;Number=1220772
30-page Word document on Access Basics (for Programming) -- it doesn't cover VBA, but prepares you for it because it covers essentials in Access

Thank you. I'm gonna play with this tonight!!!!

you're welcome, Nathan (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

the reason why people recommend you not to use lookup fields in tables is that it hides whats really going on - you are not easily able to see the true value being stored in the table, but rather the looked up value, sometimes you really want to see the true value, and its infuraiating when its hard to get at.

as it only takes a few seconds to do it "normally", ie creating a query to do the lookup function, the extra effort is well worth it.

This is an added feature MS put in for us, but its really a bit anti-relational and better not used

Hi Nathan,

To add to what gemmathehusky (what is your name?) said, you should use Forms to input and display data anyway -- the only people who should open tables directly are administrators and programmers -- and when they do, they need to see the real data. For this reason, you should not set the Caption property for fields in the table design either as that masks the real fieldname when you look at the datasheet view.

But seeing the real data is not the only reason to avoid lookups in the table design, it can also cause other problems as described in the link I gave you

Boo yah. I think I got it. My tables should house no meaning to the user. I should create queries and keep the data struture more like a matrix. At first this is how I used the relationhips on the table level, except I learned to circumvent this and added the lookups to ease table views. If it's not an accepted reason to use the lookup on table level I won't. I've made some changes back on my DB's.

Will this have an effect on cascading updates? Or global changes if that's the wrong term.


Thanks again.

Nathan

Glad to see you are getting there.

As I said in the previous post, changing the lookups to normal Text Boxes will have no affect. What is stored in this field is the looked up value. So if the Field you are looking up is Autonumber you will be storing an Integer. If for the sake of the argument you went back to making this a combo box you would still be storing the Integer but the difference is that you will see the associated value wich is not really there.

Your relationships stay the same, so there is no problem there.

Allow me to carry on just a little more.

say your Lookup Table stored the following Data.

Autonumber for the primary Key and Text for the next field. You could then store the following

1 Cat
2 Dog
3 Bird

If you used a Combo Box in the Table then your query/table would show the following

Cat
Dog
Bird

Where in fact the true values are 1,2 and 3. So if you filtered by Cat it would not work as the true value is 1 not Cat.

You still use a separate Table as a look up and to store the Value. But do not use Combo boxes at the table level. Use them at the form level.

Have a look at the attached. One would normally show only the first two controls on the form. I added the other three just for demonstration purposes only.

Des,

Thanks. I'm won't forgot this one. I'm gonna make a copy of this database so when you're a famous (more famous) developer I'll have it. I'll post the db again in a couple dayz and hopefully it'll be perfect. I still have to use the option explicit statments. I'm gonna try to figure them out, but you know I'll be back if I can't.

thx,

Nathan

Nathan

Perhaps you could make a post under Modules asking for somone to explain the use of Option Explicit. I know enough to satisy myself but not enough to explain here in the forum. I could make a little mistake in my explaination, so it is best to leave this to someone else.

--- Option Explicit ---

It is always a good idea to DIMension your variables before you use them

Dim mStr As String

to force variable delaration (which I HIGHLY recommend), put this at the top of your module:

Option Explicit

you can do Tools, Options... and check "require variable declaration" so that any new modules created will have the Option Explicit statement automatically at the top. Modules that have already been created will not be changed.

Another handy thing in declaring variables is that, if Access recognizes the variable name you have typed as a variable that has been dimensioned, it will change the case for you after you move off the line.

mstr --> will be corrected to --> mStr
as soon as you move off the line. Nice because you have an indicator that you typed the name correctly! And if you didn't, the compiler will catch it since all variables must be declared when using Option Explicit


***

Option Compare Database

this means that "ABC" = "abc"
so case is ignore when string comparisons are made, a handy thing to do

************************

this also has some good reference stuff:

Code Behind Form (CBF)
http://www.utteraccess.com/forum/enter-liv...at#entry1438041

I'll check this out tonight!!!