Table Lookup field: Need help!

[Charter Newsgroups]

I have three tables in my new database: Courses, Instructors, & Courses and
Instructors.

Courses has Course Number (AutoNumber, key) and Course Name (text, indexed /
no duplicates) fields.

Similarly, Instructors has Instructor Number and Instructor Name fields
(same properties).

Courses and Instructors has Course Name and Instructor Name fields, which I
have set up 1:1 relationships with their counterparts.

My trouble lies in the fact that I am trying to make it so that I can have a
Combo Box with the Course Name or Instructor Name, as appropriate, listed. I
have been able to make the Course number and Instructor Numbers show up in
the combo boxes, but not the Name!!! This is exceedingly frustrating. For
both fields, I have (in the Lookup tab of the bottom area, in design view)
the Display Control as Combo Box. I have Row Source Type as Table/Query,
with Row Source pointing to the Courses or Instructors table, as necessary.

I had thought that having the data type be text would help Access to figure
out that I need to use the field that is also defined as text in the other
table; no such luck. It apparently, I guess, simply displays the autonumber
key of the other field as text.

I have tried to change the Bound Column to 0, 1, or 2, but it appears to
have absolutely no effect on which column actually appears.

Column heads simply shows the field names, while Column Widths lets me show
1 or both columns.

Can somebody help me out here? I am totally frustrated -- this is really
ridiculous!

BTW, does anybody really know what "Bound Column" refers to? It seems, by
its description, that it should be what I'm looking for, but it isn't. This
is so totally illogical!

Thanks,
Eric

 

[AnandaSim]

Have a look at this article:

http://www.techonthenet.com/access/comboboxes/bind_index.php

There are also other combo box articles there.

HTH
Ananda

 

[Pete D.]

Bound column is the one that the control will return as your selection.
Column count is how many columns you want to display. Column width is
entered like 1", 2", .5" making each column so you can see the info. Insead
of working with the full table on source line click the button with the dots
on it at the end of source and make a query to fill the control with what
you want. Then set your sizes and such for the columns.

 

[Eric IsWhoIAm]

Ananda,

That helped immensely! Thank you so very much for bringing that article
to my attention. Of course, now Access is, strangely, claiming that I need
something more from my Courses table, but this certainly helps with my
initial difficulty!

Thank you,
Eric

 

[Eric IsWhoIAm]

Pete,

Thank you for replying. I very much appreciate your advice! I understand
what is needed much better now, and I have the data displaying correctly.

Thank you,
Eric

 

[tina]

comments inline.

I have three tables in my new database: Courses, Instructors, & Courses and
Instructors.

Courses has Course Number (AutoNumber, key) and Course Name (text, indexed /
no duplicates) fields.
okay.


Similarly, Instructors has Instructor Number and Instructor Name fields
(same properties).
okay.


Courses and Instructors has Course Name and Instructor Name fields, which I
have set up 1:1 relationships with their counterparts.

wrong. one course may be taught by many instructors, and one instructor may
teach many courses, correct? that is a many-to-many relationship. so the
CoursesAndInstructors table is a "junction" or "linking" table between the
Courses table and the Instructors table. as such, it must contain the
*primary key* fields of those two tables, as foreign keys, as

CoursesAndInstructors
CourInsID (primary key, autonumber)
CourseNumber (foreign key from Courses table)
InstructorNumber (foreign key from Instructors table)

My trouble lies in the fact that I am trying to make it so that I can have a
Combo Box with the Course Name or Instructor Name, as appropriate, listed. I
have been able to make the Course number and Instructor Numbers show up in
the combo boxes, but not the Name!!! This is exceedingly frustrating. For
both fields, I have (in the Lookup tab of the bottom area, in design view)
the Display Control as Combo Box. I have Row Source Type as Table/Query,
with Row Source pointing to the Courses or Instructors table, as

necessary.

bad, bad idea. see http://www.mvps.org/access/lookupfields.htm. get rid of
the Lookup fields in the table; you can use combo box controls to choose
course and instructor names *in the form*, which is where the user should
interact with the data anyway - never at the table level.

I had thought that having the data type be text would help Access to figure
out that I need to use the field that is also defined as text in the other
table; no such luck.

wrong; see above. don't confuse data display and user interaction, which
should be handled via forms and reports, with data storage, which is handled
by tables. to use Access effectively, you need to learn the basic principles
of relational design, and build a sound structure of tables/relationships
based on those principles, to store your data; *then and only then* build
the queries, forms, and reports you need to display and interact with the
data. for more information, see
http://home.att.net/~california.db/tips.html#aTip1. also suggest you read
tips 2 thru 6, as well.

It apparently, I guess, simply displays the autonumber
key of the other field as text.

I have tried to change the Bound Column to 0, 1, or 2, but it appears to
have absolutely no effect on which column actually appears.

Column heads simply shows the field names, while Column Widths lets me show
1 or both columns.

once you've created and related your tables correctly, create a form to
enter data in the CoursesAndInstructors table. use combobox controls, as i
mentioned above. you can read up on combobox controls and their properties
(specifically: RowSourceType, RowSource, ColumnCount, ColumnHeads,
ColumnWidths, BoundColumn, ListRows, ListWidth, LimitToList, AutoExpand) in
Access Help, so you'll understand how to set the properties to get the
result you want.

hth

 

[Eric IsWhoIAm]

My comments in line too!

comments inline.



wrong. one course may be taught by many instructors, and one instructor
may
teach many courses, correct? that is a many-to-many relationship. so the
CoursesAndInstructors table is a "junction" or "linking" table between the
Courses table and the Instructors table. as such, it must contain the
*primary key* fields of those two tables, as foreign keys, as

CoursesAndInstructors
CourInsID (primary key, autonumber)
CourseNumber (foreign key from Courses table)
InstructorNumber (foreign key from Instructors table)

I have modified this so that Instructors has a 1:Many relationship with
Courses and Instructors. One instructor can teach many courses. However,
since the course section is part of the course name (for the purposes of my
project), I have a 1:1 relationship between Courses:Course Name and Courses
and Instructors:Course Name.

Still, I was thinking that perhaps I ought to include CourseID, or
CourseInsID, to my table... I will investigate this path.

necessary.

bad, bad idea. see http://www.mvps.org/access/lookupfields.htm. get rid of
the Lookup fields in the table; you can use combo box controls to choose
course and instructor names *in the form*, which is where the user should
interact with the data anyway - never at the table level.

Theoretically, I understand your point, and I will do so in the future. At
this moment, I am not interested in creating forms, as I just need some
sample data for the time being. Other than the fact that you should add data
in the form rather than in the table, is there any other good reason you
could explain to me that it isn't a good idea to use combo box lookups
within the table? I had thought it would be nice to help save time, since
this is simply a sample database. I am going to use what I learn from this
to help me design a true, useful database for work, which I started working
on two days ago.

wrong; see above. don't confuse data display and user interaction, which
should be handled via forms and reports, with data storage, which is
handled
by tables. to use Access effectively, you need to learn the basic
principles
of relational design, and build a sound structure of tables/relationships
based on those principles, to store your data; *then and only then* build
the queries, forms, and reports you need to display and interact with the
data. for more information, see
http://home.att.net/~california.db/tips.html#aTip1. also suggest you read
tips 2 thru 6, as well.

Okay, I will check that out. Thank you!

 

[tina]

Other than the fact that you should add data

in the form rather than in the table, is there any other good reason you
could explain to me that it isn't a good idea to use combo box lookups
within the table?

since the course section is part of the course name (for the purposes of my
project), I have a 1:1 relationship between Courses:Course Name and Courses
and Instructors:Course Name.

Still, I was thinking that perhaps I ought to include CourseID, or
CourseInsID, to my table... I will investigate this path.

well, you pretty well lost me there, so i've no comment except to suggest
that you re-examine your tables structure; sounds like *perhaps* you need
tblSections as your linking table between tblCourses and tblInstructors,
since one instructor teaches each section. again, i urge you to read up on
relational design principles before proceeding.

hth

 

[Eric IsWhoIAm]

Actually, you gave me exactly what I needed in order to resolve the problem,
Tina. Thank you very much!

Maybe you, or somebody else, can help me with one other thing:

I have went ahead and created a form for the Courses and Instructors table.
It has drop-down boxes that show the Course Names and Instructor Names, and
I have the Course ID and Instructor ID fields within the table (they are the
foreign keys).

Now, I wish to make it so that when I select a Class Name from the combo
box, it automatically changes the Class ID to match the appropriate Class
Name. Would I need to use VBA for that, or can it be done through some other
method? How would I get started?

I've used regular VB (6.0 and .NET) before, but I've seldom used VBA, so I'm
not certain how to create an appropriate function....

-- Eric

 

[tina]

from a later post in another thread, it appears you've resolved the form's
combobox issue; if not, post back.

 

[Eric IsWhoIAm]

Tina,

I have indeed resolved the issue. Thank you so very much for all your help!


Gratefully,
Eric

 

[tina]

you're very welcome

Tina,

I have indeed resolved the issue. Thank you so very much for all your help!


Gratefully,
Eric