I suppose this is a particularly complex problem. I essentially want to restrict values for an entry form based upon a subset of items from a more or less hardcoded query. I was not quite able to get the sense of how to do this in the help documentation, and it appears what I want to do is far too complex for the dependent dropdown boxes.
Here's how this situation is set up:
There are three tables of interest for this form.
[Student], with its primary key ID;
[Sessions], that contains information that changes periodically (by schoolyear), including the student's grade and school...it takes the foreign key of SID (which is Student.ID);
and [TutoringSessions] that allows tutors to enter information about the student's current session. It will also take the foreign key of Student.ID (as SID).
The restrictive query would only allow students from the current school year to have information added. This query would combine information from the Student and Sessions tables to create a recordset. This recordset is where the student information dropdowns on the Tutoring Sessions form are supposed to go.
Now here's the thing: I would like the form to not only restrict entry to this recordset, but also that when a value (that is, a student name) is picked, the ID of that choice automatically gets placed into the SID column in the form. Every time I try to do this in the dropdowns, I just get separate entries of last name and first name. It is picking up the individual items from each field, but they're not connected at all.
If this is confusing the way I've explained it, please let me know and I will do my best to clarify.
Thank you very much in advance, and I am really enjoying working with this program; it's very powerful!
-Seth