CODEWITHSUNDEEP
ms-accessjoincomboboxinner-joinautofill
pinhat
pinhat

Reputation: 21

MS Access Identical Comboboxes for Autofill

tableStudent (student_ID, last_name, first_name) is a list of students given an ID number by the table.

tableProject (project_ID, project_title, student1_ID, student2_ID) 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.

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:

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);

Upvotes: 2

Views: 76

Answers (1)

June7
June7

Reputation: 21379

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: this is least efficient method as domain aggregate functions can slow performance of queries and forms.

For all options, set textboxes with TabStop No.

Upvotes: 0

Related Questions