Entering data into a form based on a lookup table

[noelgarry]

Hi
I am fairly new to this so please help.

I have 2 tables such as

MEMBER TABLE
Member Number
Name
Address
Member Type (Char 1)

MEMBER TYPE TABLE
Member Type (Char 1)
Type Description

In the data entry form for the Member Table, I would like to provide a drop down of the possible member types based on a lookup to the Member Type Table. Depending on the selection chosen by the Data Entry person, I would then like to insert the one character code into the Member Table.

I hope that is clear.
Regards
Noel

 

[Dennisk]

what you require is a combo box, either create one from scratch or use the combo box wizard.

 

[noelgarry]

Hi
Thanks for the feedback on using a combo box.
However, how would I use a combo box that looks up another table to find the valid values?
Regards
Noel

 

[hyperpau]

Hi
Thanks for the feedback on using a combo box.
However, how would I use a combo box that looks up another table to find the valid values?
Regards
Noel

it's easy, make sure that on the table itself, it has that field which is a look up to that other table. in the design view of that table, go to the field where you want it to be a lookup, then select the type as look up wizard. Then there would be a pop up that would just ask you which table to look up to and which field in that other table would be the items showing on the selection.

if you have configured the table this way, then as soon as you add that field to your form, it would automatically be a combo box.

 

[Bob Miller]

it's easy, make sure that on the table itself, it has that field which is a look up to that other table. in the design view of that table, go to the field where you want it to be a lookup, then select the type as look up wizard. Then there would be a pop up that would just ask you which table to look up to and which field in that other table would be the items showing on the selection.

if you have configured the table this way, then as soon as you add that field to your form, it would automatically be a combo box.

Actually, you shouldn't place the combobox in the table.
Do as noelgarry says and use the combobx wizard on the form. It will step you through the process.

 

[hyperpau]

Actually, you shouldn't place the combobox in the table.
Do as noelgarry says and use the combobx wizard on the form. It will step you through the process.

That is always a good alternative. But if you do that, you need to do the
wizard everytime you add that field to a form. But if you do it on the table itself, then every form referenced to that table will automatically have that field as a combobox. Ok?

 

[boblarson]

That is always a good alternative. But if you do that, you need to do the
wizard everytime you add that field to a form. But if you do it on the table itself, then every form referenced to that table will automatically have that field as a combobox. Ok?

NOT OKAY!!!!!!

Did I make that clear? DO NOT, I REPEAT, DO NOT (no matter how "easy" it seems at the time) use lookups in tables. This is a very, very BAD idea as it WILL cause problems for you sometime down the line. Notice I did not say "maybe" I said it will.

Read here for more information:

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

 

[hyperpau]

NOT OKAY!!!!!!

Did I make that clear? DO NOT, I REPEAT, DO NOT (no matter how "easy" it seems at the time) use lookups in tables. This is a very, very BAD idea as it WILL cause problems for you sometime down the line. Notice I did not say "maybe" I said it will.

Read here for more information:

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

I have done tons of access database with vba codes on it. And this is what I always do. never did my clients encounter any problems with my design.
Well i guess were entitled to our own opinions. seems like this forum is not really into that. instead, they put big fonts in bold format as if they are shouting. goodbye then.

 

[boblarson]

I have done tons of access database with vba codes on it. And this is what I always do. never did my clients encounter any problems with my design.
Well i guess were entitled to our own opinions. seems like this forum is not really into that. instead, they put big fonts in bold format as if they are shouting. goodbye then.

Well, I'm surprised that you've never run into this problem before because almost all of the Microsoft MVP's will tell you the same thing I just did. If that offends you then sorry, but I'm only telling it like it is. If you want to disagree, there's nothing wrong with that. But, in my 10 years of Access use, I've seen so many times that the use of lookups has screwed up people's use of forms and reports, so it is important to let people know.

 

[Dennisk]

I've been using Lookups on tables for 15 years and never had a single problem, however I did read the article and now I only use combo boxes on forms along with ensuring required fields and validations as it is much more flexible.

 

[noelgarry]

Thanks

Looks like I have generated a bit of heat on this one.
Thanks everybody for your help.
Noel