asked on

Storing Combo Box Selection in Table

I have two combo boxes on a form cascaded, One for directors and one for films.
Select Directors Box, the values in Films Combo Box have been filtered with query.
Two tables, "Directors" and "Films"
Combo boxes work right but I need to store value back in "Directors" table in field named "Rating"
How can I do that? Would something like AfterUpdate work???

Scott McDaniel (EE MVE )

Bind the Combos to the Table Columns where you want to store the values. To do that, set the ControlSource of the Combo to the appropriate field.

Bill Pearson

ASKER

There are no option selections under "Control Source" is to access "Expression Builder"

Row Source Type is Table/Query
Row Source is SELECT Films.ID, Films.FilmName, Films.DirectorID FROM Films WHERE (((Films.DirectorID)=Forms!TestForm!cboDirectors)) ORDER BY Films.FilmName;
I have no option like text box to set Control Source to Specific Row. I probably need some type of code.
(I'm a newbie and am running Access 2003 if that helps)
Thanks

PatHartman

If the form is bound, the comboboxes can be bound to columns of the Form's RecordSource. With a bound form, you will see a list of fields in the ControlSource for each control.

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott McDaniel (EE MVE )

Or are you perhaps saying that you need to store a value in a table that is NOT IN your Form's Recordsource?

If so, then often that points to improper data structure. You'd have to tell us more about your application and table structure before we could determine if that's a problem, of course.

Bill Pearson

ASKER

Thanks. Let me look at the listed article and re-evaluate. I'll respond back later with more structure
information.

Bill Pearson

ASKER

You were right. I had a relationship issue and had no fields available to store data. I now have it properly bound and it stores to the main table. It stores my first column which is the numeric ID. Is there a way to store both the numeric ID and the text field which is the second column of the the RowSource is query/table/SQ so I get something like ID, film (62 and Jaws)?

Bill Pearson

ASKER

Is it possible to let the bound filed alone and use the AFTERUPDATE event to write the second column to the table?

Bill Pearson

ASKER

Problem Solved; Works perfectly. Thanks.

PatHartman

Storing the lookup field in addition to the ID is technically wrong since it violates rules of normalization - look up first, second, and third normal form.

All you need to store is the ID. Then when you need to show the text value, you use a query that joins to the lookup table or simply use a combo box on your form or report.

Keep in mind that if you store the text value, it will NOT be updated if it is changed in the lookup table. For example, if you store the FilmID and the FilmName, if the FilmName is changed in the films table, it would not be changed in this table Granted, it isn't likely that FilmName would be modified after it was entered but perhaps it was entered incorrectly and the spelling needs to be fixed. Using proper normalization techniques avoids the data anomaly entirely since you always look up the FilmName when you need to display it. Experienced developers have learned the hard way to not violate normal forms. I would only store the text value, if I were creating a data warehouse and wanted to avoid requiring the user to do lookups. Data warehouses are NEVER updated interactively. They are always recreated on a schedule from the transactional data.

Bill Pearson

ASKER

Thanks. I never thought of updates becoming a problem but I see where that can happen.

PatHartman

fixing the design now will be easier and less work than fixing it later.