Solved

Access 2010 basic question - table update from form

Posted on 2014-11-02
5
860 Views
Last Modified: 2014-11-03
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
0
Comment
Question by:willcox
  • 2
  • 2
5 Comments
 
LVL 9

Accepted Solution

by:
macarrillo1 earned 167 total points
ID: 40418800
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.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 333 total points
ID: 40419202
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:

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

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.
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 40419440
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.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 333 total points
ID: 40419477
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 ...
0
 

Author Comment

by:willcox
ID: 40420824
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
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now