MS Access Identical Comboboxes for Autofill

user_18041593 0
Viewed 40

I have two tables: 'tableStudent' - a list of students given an ID number by the table, with following columns:

  • student_ID
  • last_name
  • first_name

and 'tableProject' which gives each project an ID and will be used to store information about the students involved in the project. Students will work in pairs on the project. This table includes the following columns:

  • project_ID
  • project_title
  • student1_ID
  • student2_ID

The columns student1_ID and student2_ID are combo-boxes that link student_ID to the student names.

I want to create a form that can be used to record students involved in a project. I want to be able to select the student IDs and have the student names autofill on a form.

I can make a form that autofills, but only with one student with this SQL:

SELECT tableProject.project_ID, tableProject.project_title, tableProject.student1_ID, tableStudent.last_name, tableStudent.first_name
FROM tableStudent INNER JOIN tableProject ON (tableStudent.student_ID = tableProject.student2_ID) AND (tableStudent.student_ID = tableProject.student1_ID);

When I add Student 2, I get a duplicate error on the student names. How do I indicate that the two names belong to different students? This is the code I'm generating:

SELECT tableProject.project_ID, tableProject.project_title, tableProject.student1_ID, tableStudent.last_name, tableStudent.first_name, tableProject.student2_ID, tableStudent.last_name, tableStudent.first_name
FROM tableStudent INNER JOIN tableProject ON (tableStudent.student_ID = tableProject.student2_ID) AND (tableStudent.student_ID = tableProject.student1_ID);

Any help appreciated!

ms-access autofill combobox inner-join join
1 Answers

Options for displaying related info from lookup table:

  1. Use Access query designer to build form RecordSource. Pull tableStudent into query designer. In your case pull in twice - second instance will get named like tableStudent_1. Join each to one of the student fields. Bind textboxes to both sets of lookup table fields and set them as Locked Yes to prevent edit.

  2. Don't include lookup table in form RecordSource at all. Include all student info in combobox RowSource. Expressions in textboxes refer to combobox columns by index, index begins with 0: =[comboboxName].Column(1)

  3. DLookup() domain aggregate function, however, since your tables have a relationship, this is an inefficient method.

For all options, set textboxes with TabStop No.

user_76071900
Related