Have forms with auto-complete/combo box option for cells

[jbarresi]

Full disclosure...I'm very tech savy but Access ignorant.

Scenario: I am building a database to store student assessment information. I have a table with student information, a table with teacher information, and tables which will store the student's scores on a certain assessments.



Q1: I have a field in the assessment table for Teacher ID and Student ID which are linked to their respective tables. What I want to do is create a form for inputting the assessment data but the fields for teacher ID and Student ID to auto-complete or be combo boxes, I don't care which. That is, when a person is entering a student's scores, and go to enter their ID, they won't have to type the whole number out and hope they dont make any mistakes, they will essentially have a list of choices to choose from. Or as they type, the corresponding number will filter down. I already have refferential integrity on so that they can't enter an invalid ID in either field but I want them to have the choice.

Q2: In an assessments table, there is a 1-1 correspondence btw student ID's because only one student can have one score on a particular assessment. However, clearly one teacher can be tagged to several student's assessment scores. Can anyone think of a better way to tag the teacher to multiple student's scores without having fields in the assessment table for teacher ID which is repeated multiple times.

Thanks for any insight that can be offered.

[NTC]

Q1 should be inherently occurring if you set up the field in the table to be a look up field. When this look up field type is put onto a form it will automatically be a combobox style field.

Q2 you must repeat the teacher id in each record; perhaps you wish to set up a form that will auto enter the teacher ID as you enter the score - just for productivity's sake - but in terms of logic you need that value in the record.

[jbarresi]

Q1 - You're Awesome! Stupidly, I had not created the fields as Look up fields. Since changing it, I'm set.

Q2 - I thought that for logic sake the records would have to be there but how do I create a form that auto-enters data?

[NTC]

Overall it is an issue of creativity. But the technical underpinning is:
* put the value you want auto entering into the header of the form - the db has to know this value.
* when in the record of the form and entering something else - in the AfterUpdate Event (or some appropriate event) then call that value down from the header i.e. Me.TeacherID = Me.headerTeacherID The TeacherID in the record doesn't have to be visible.

There is of course other ways to implement this. You could have a form/subform arrangement. It depends on your creativity and how you want to display info for the user experience.

Hope this helps.