Lookup List, Form, Table, Combo box

[emilyebba]

Hi,

I have a field that looks up Street Suffix (Road, Avenue, Street, Drive, etc..). My question is this: Is the best place for this lookup in the table, or is it in a combo box on the form. I can either have the lookup wizard store the lookup in the table OR store the field as text and then create a combo box on the form and have the data source be that field in the table. Which is better? Does it make a difference?

Along this line is in my combo box, I am still able to type things in. I have set the property in the form to be "Limit to List" but I find that I can still type things in. Help!

Thanks!

 

[John Big Booty]

Store the values in a table and use a combo to retrieve them from there.

Read this article as to why using Table Level Lookups will lead you into a world of hurt.

 

[John Big Booty]

...

Along this line is in my combo box, I am still able to type things in. I have set the property in the form to be "Limit to List" but I find that I can still type things in. Help!

Thanks!

That sound rather odd are you sure the value is getting stored back to the underlying table

 

[emilyebba]

Hi, THANK YOU!!

So create a separate and new table (one text field called SuffixLookup? for example) and store the values there? Dont type them in the "wizard" section at all? Tell Access you want it to lookup the values in a table?

Regarding the "<imit to List"...when I upload data to my form/table from an excel sheet and there is data in the fields that is different from what I have assigned to "Limit to List" it still allows it (for example Avune vs Avenue). The reason I am asking is we have been storing our data in excel and now are migrating to an Access DB. I tested it out and I can still upload data that if you were typing it in using the form would not be allowed - my misspelled example. Is this right??? DO I need to so something when I upload it? Thanks!!!

 

[John Big Booty]

In your table you would have two columns the first and autonumber called, say. ItemID and the second with a Text field called, say Item. The ItemID would be what you store in the table containing the records.

Have a look at the attached sample for a very simple example.

 

[emilyebba]

Thank. That was helpful. Question from a database managment perspective. Why do you store the Option ID and not the actual Option (Option 1 for example). Do you need to have an ID for every table? For my road suffix would it store the actual suffix (Rd. or St.) or would it store its "ID"?

Also about uploading table data...what if the data entered on the excel file does not meet the parameters of what you have in your combo boxes?

Thanks!

 

[John Big Booty]

The ID is stored as it is an simple integer and takes up less space that storing a String.

Additionally if at some point, rather than Rd. you wanted to Display Road it would be a simple matter of changing one field rather than hundreds.

 

[emilyebba]

Thank you for your help!!