Need help updating record (or combobox)

[Eric IsWhoIAm]

I have three tables: Courses (Key is Autonumber Course Number, also have
Text Course Name); Instructors (similar fields); and Courses and Instructors
(have a combination of the four fields).

Currently, if I manually select the course from the drop-down box, the
Course Number, and the same with the Instructor fields, then I can save the
record. If the Course Number doesn't match the Course Name, though, I'm out
of luck.

What I really want is, when I choose the Course Name in my Courses and
Instructors combo box on the from I made for it, for Access to do the
following:

--> Look in the Courses table.
--> Find the Course Name in the Courses table that corresponds to the Course
Name I chose in the Courses and Instructors form's combo box.
--> Find the matching Course Number that corresponds to the Course Name in
that
table.
--> Fill in the Course Number (in the Courses and Instructors form or table)
with the Course Number it found in the previous step.

So, I know how to break down the procedure I want it to do, but I don't know
how to tell Access to do this.

Please, anybody: Can you help me to translate these instructions into a
procedure that Access will understand?

Thanks,
Eric

 

[Bob Quintal]

I have three tables: Courses (Key is Autonumber Course Number,
also have Text Course Name); Instructors (similar fields); and
Courses and Instructors (have a combination of the four fields).

Currently, if I manually select the course from the drop-down box,
the Course Number, and the same with the Instructor fields, then I
can save the record. If the Course Number doesn't match the Course
Name, though, I'm out of luck.

What I really want is, when I choose the Course Name in my Courses
and Instructors combo box on the from I made for it, for Access to
do the following:

--> Look in the Courses table.
--> Find the Course Name in the Courses table that corresponds to
the Course Name I chose in the Courses and Instructors form's
combo box. --> Find the matching Course Number that corresponds to
the Course Name in that
table.
--> Fill in the Course Number (in the Courses and Instructors form
or table) with the Course Number it found in the previous step.

So, I know how to break down the procedure I want it to do, but I
don't know how to tell Access to do this.

Please, anybody: Can you help me to translate these instructions
into a procedure that Access will understand?

Thanks,
Eric

Eric, the problem is that your Course-Instructor table should have
only the two foreign keys, not the names as well, to view course,
coursename, instructor and instructorname, you do not vieww the
table, but a query that looks up the course name on the course
number, and the instructor's name on the instructor's number.

That's what comboboxes do. They display the instructor name, but put
the instructor number in the table.

Just create a form with two comboboxes, in the first combobox, the
combobox has the two fields from the course, showing the name, but
linking hte number. the second combobox has the instructor number
and name.Set the bound column of each combobox to the number, the
control source to the number field in your courses-instructors
table, and the width of the number columns in each combo box to 0,
so that you see the names.

 

[Larry Linson]

Currently, if I manually select the course from the
drop-down box, the Course Number, and the same
with the Instructor fields, then I can save the record.
If the Course Number doesn't match the Course Name,
though, I'm out of luck.

Hunh? Sounds to me as if you have a bit of a normalization problem... your
Course table should be the only "authoritative source" for linking course
number and name. You shouldn't be storing the name, separately, elsewhere.
Store the Course Number, and then join that with Course Number in the Course
table to obtain the Course Name.

You have encountered one of the primary reasons for adhering to relational
database guidelines and normalizing your data. It's not just that redundant
data costs storage; it is that you encounter "anomalies" of this kind
(usually your own fault, BTW) that cost you time, effort, and stress.

The Combo Box should display the Course Name, but store the Course Number.
Then if "Basketweaving for Beginners," course BW101 is renamed as
"Introduction to Willow Withing,", all the places you use it will reflect
the name change. The "trade name" for the error you have encountered is
"committing spreadsheet".

"Designing Effective Relational Database Applications," by Rebecca Riordan;
and a similar title by Mike Hernandez are good references. "Microsoft
Access <yourversion#> Step by Step" from Microsoft Press is a good
self-study for the novice. "Microsoft Access 2003 Inside Out" by J. Viescas
and "Microsoft Access 2007 Inside Out" by J. Viescas and J. Conrad, both
from Microsoft Press (or maybe "Microsoft Learning") are good books that
start at the beginning and go deeper than the Step-by-Step series. In
previous versions, I've liked Roger Jennings' "Special Edition Using
Microsoft Access" books by Que, too, but haven't reviewed the 2002, 2003,
and 2007 editions.

Larry Linson
Microsoft Access MVP

Larry Linson
Microsoft Access MVP

 

[Eric IsWhoIAm]

Thanks... combined with the experimentation I've been doing for the past
several hours, that got me exactly what I wanted -- a table that consisted
solely of the class and the instructor! I really appreciate your help.

Thank you,
Eric Johnson