Access 2010 basic question - table update from form

Noobe to Access, so this is very basic, so be gentle!  I think it should be simple.  

Database with multiple tables. One we'll call master and has multiple fields and thousands of records.  Other tables have from a few dozen to a few hundred records in a single field to be used as selection lists for data input.  Form set up to use combo boxes to the small tables to create  the selection lists during data entry.  I want the data to all be saved to the appropriate fields in the master table.   I can create a new record OK and save it, and all the manually input fields work, but the fields retrieved using the selection lists from the other tables don't update the master table.  Gotta be something basic I'm missing, so any help appreciated.  thx
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael CarrilloInformation Systems ManagerCommented:
Creating Lookup field in Access table
What you need to do is change your Master Table to make use of all the lookup tables (Tables with single fields).
You can do that by opening the master table in design view. Then select one of the fields that you are having problems with (contains no data in that field). Click the 'lookup' tab in the properties and you will see 'display control'. Click in the property and you will see a drop down selection 'text box, list box, combo box'. Select Combo box. The properties will change; click in the 'row source'.  This is where you tell it to use your lookup table.  You can either select the lookup table directly or create a query that uses it.  The reason for using a query is if you want to sort or restrict your selection within the dropdown.

See illustration above.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Form set up to use combo boxes to the small tables to create  the selection lists during data entry.
This is the correct way to do things, so please don't set lookups at the table level. It won't fix your problem, and the ONLY benefit to doing so is that Access will automatically add a combobox to the form when you drag/drop that field onto a form. See this somewhat humorous take on the matter:

If your form is not saving data to the database, then most likely it's because you haven't set the ControlSource of the combobox correctly. Open the form in Design view, select the combo, and then select the Data tab in the Properties dialog. Make sure the ControlSource value is set to the field in the underlying table where you want to store that data.
Michael CarrilloInformation Systems ManagerCommented:
Other tables have from a few dozen ... in a single field to be used as selection lists for data input.

The link provided by Scott McDaniel assumes that your lookup tables have an ID and the lookup field (two columns).  Your statement is that you have tables with a single column (the lookup field). If this is the case you can use these tables as lookup tables in your master table. As Scott McDaniel stated one of the advantages is that this field will already be configured when you drop it into this form and future forms. In addition since you have a single column table the value from the table (lookup field) will be stored; not the ID of the lookup value. If you chose to update the table then I would recommend deleting the old corresponding fields and adding them back in so that they pick up what you changed in the table.  If you chose to update the form you will need to remember to configure your fields to lookup values in future forms.

There is more than one way to solve a problem; each with advantages and disadvantages.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
one of the advantages is that this field will already be configured when you drop it into this form
That is the ONLY advantage ... and there are many disadvantages to setting lookups at the table level (obfuscation of data being the prominent one, followed closely by the troubles with filters/lookups in queries).

I agree there are many ways to resolve an issue, but I'm not sure how setting a lookup at the table level would solve the problem of data not being stored in the table. This would seem to point to a form-level issue (i.e. no control source, perhaps), instead of a lookup table issue.

But I could be wrong ...
willcoxAuthor Commented:
Wow.  Or perhaps, Duh!.  That's just what I needed.

Access is new to me (obviously), but I think I'm gonna dig in some more.  I'm an old-timer - if I still had the compiler and a DOS system  I could have done this project in dBase with a Clipper front end in just a few hours, but I've been  struggling with Access for couple of weeks now.  Clearly I'm gonna have to jump into the VB side of things to really get creative, but this solves my immediate problem and gets this project going.

Many thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.