Expert: Scottgem - 10/23/2005

Question
Hi,

Great advice again, thanks.

But say I am filling out a form and under course there are only four acceptable answers (Engl101, Engl201, Engl301, and Engl401). You're saying this should not be a lookup, no combo-boxes in tables? Then what is the correct way to do that? Is it better to type the options into Row Source than to lookup in another table?

Thanks for your "Above And Beyond The Call Of Duty" help,
Tom

-------------------------
Followup To
Question -
Can you explain that more please? I'm confused by the idea of using Forms instead of tables. I played with that and tried the unbound text box (I'm not sure exactly what it is) but I didn't get far.

I think I used the Wizard to create a Lookup for the field Homeroom in table tbl_HRTeacher.
- Display Control: Combobox
- Rowsource: SELECT [tbl_HomeRoom].[HomeRoomID], [tbl_HomeRoom].[HomeRoom] FROM tbl_HomeRoom;

Does that still seem like a problem?

Tom

-------------------------
Followup To
Question -
Thank you for all your great answers.

I'm continuing work on the same database for teachers.

I have a table tbl_Students and I have a table tbl_HRTeacher. In tbl_HRTeacher each HRTeacher is assigned to one Class (e.g. teacher Sally Smith is the HRTeacher for Grade7-Class1). In the table tbl_Students each student is assigned to one Homeroom (e.g. Allan Apple is in Grade7-Class1). Therefore I know that Allan Apple's Homeroom teacher is Sally Smith. But where and how will I see next to the name Allan Apple the homeroom teacher Sally Smith. Will I do this in a table, or a query, or a form that displays the info from a query? Please explain how I can do this.

Is it possible to set it up so that when I am using tbl_Students and I use the lookup to Select Grade7-Class1 that the fields HRTeacherFname and HRTeacherLname are automatically filled in? Is there a better way?

I'm interested in Forms but I haven't quite gotten there yet so I work in tables a lot.

Thank you,
Tom

Answer -
You would not do it in a table. But you could do it on a form or report or in a query. If you have a table of Homerooms, then you don't need the tbl_HRTeacher. Just add HRTeacherID as a foreign key in the Homerooms table.

I'm a little concered by this statement: "when I am using tbl_Students and I use the lookup to Select Grade7-Class1". You should NEVER be viewing or editing data in the tables directly. This should only be done using forms. Also it is NOT recommended that you use lookup fields on the table level. Lookups should be done with combo or listboxes on forms. It sounds like you are using lookup fields and editing within a table.

What you SHOULD be doing is including a Combobox (called cboHomeroom) to select Homeroom on the Student form. The RowSource should look like this:

SELECT HomeroomID, Homeroom, HRTeacherLName & ", " & HRTeacherFName AS HRteacherName FROM tblHomerooms, tblTeachers INNER JOIN tblHomeroom.HRTeacherID = tblTeachers.TeacherID;

You would then add an unbound text box on your form with a Controlsource of =[cboHomeroom].Column(2)

HTH
Scott<>
Answer -
A form is an object used to interact with a table. You have much greater flexibility and control over the data input and display when using a form. Users should NEVER be allowed to interact directly with tables.

Lookup fields on the table level (created in table design mode) are not recommended. This following article explains why: http://www.mvps.org/access/lookupfields.htm

You should go back into table design mode and change any lookup fields you have back to text boxes.

You apparently have 2 tables: tblHomerooms and tblHRTeacher. If there is a one to one relation between the two then you don't need both. HRTeacherID should be an FK in tblHomerooms and there is no need for the tblHRTeacher. By having both you are creating a circular logic situation and redundant data. If a=b and b=c then a=c. So you can just have a=c and don't need b=c.

HTH
Scott<>

Answer
No, What I'm saying is that you use combo or list boxes (or even option groups) on FORMS. You don't use lookups fields on TABLES. To clarify this further, when you are in design mode of a table, there are 2 tabs under the field list; General and Lookup. Under the Lookup tab the default for Display Control is Text Box. NEVER change that. That is where you create lookup fields on the TABLE level and that's where you shouldn't do it. Instead, where you need to use a list of options, you use combo/list boxes or option groups on a FORM.

Generally I use a query as the Rowsource for my lists. The only time I use a value list is when I have a short list that is unlikely to change.

HTH
Scott<>