Solved

Access 2010 basic question - table update from form

Posted on 2014-11-02
5
848 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

You have seen this as an option on your internet browser before or it may be completely new to you.  But what does this mean and why would I use this?
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 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