Solved

Access 2010 basic question - table update from form

Posted on 2014-11-02
5
918 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

732 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