auto populate field based on combination in different table/form

[tjones]

[FONT=&quot]I have two forms - Main and StudentAdvisor (filtered by student ID#)

The main form originally had a combo box field to display advisor, the problem is that some students had multiple advisors during the course of their program. This problem was fixed by the creation of a StudentAdvisor (table/form) for recording ALL advisors. I set the form to display by newest term so the current advisor (or initial if no change) is the displayed record.

On the main form I then added "add" to open the StudentAdvisor form for entering information, and an unbound txtfield to display their current advisor (or initial advisor if no change).[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]The problem is I can not get the txtfield to display the information. I have managed to get the error ( #Name?)

Advisor Type 1=Initial, 2=Current, 3=Previous
Changed 1=Yes, 2=N/A

so a record would record as:[/FONT]
[FONT=&quot]Initial and N/A
Current and Yes
Previous and Yes[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I tried writing DLookup but could not get that to look up the combinations (above) and display either initial or current.

also tried pulling form SA = Advisor to form Main = txtAdvisor

For report purposes I need to know not only current advisor but how many were assigned during the program and also how many students each advisor was assigned during certain time frames.[/FONT]

 

[spikepl]

Show a screenshot from your Relations window with all the tables expanded fully so all fields are visible.