Hi Peter,

by foreign key, I mean this:

studentID is the primary key in the Students table
studentID is a foreign key in the StudentPrefs table because it links a
related student preference record to a student record

read the section on mainform/subform in Access Basics

make a main form based on Students -- this will be your main form

make sure that StudentID is on this form

make another form based on StudentPrefs -- this will be used as a
subform.  Meke the default view --> continuous form

on this form:

textbox control for PrefNum allowing 1-4 to be entered

combobox control for SubjectID
RowSource -->
SELECT SubjectID, SubjectName
FROM Subjects
ORDER BY SubjectName

Columncount --> 2
ColumnWidths --> 0;2
ListWidth --> 2.2

textbox control for StudentID
Visible --> No

textbox control for StudentPrefID
TabStop --> No

on the BeforeUpdate event of PrefNum

'~~~~~~~~~~~~~~~~~~
  if isnull(me.prefnum) then
    msgbox "You must specify a preferance number" _
      "Missing Prefereance Number"
    Cancel = true
    exit sub
  end if

  if me.prefnum < 1 or me.PrefNum > 4 then
    msgbox "You must enter 1-4 for the preference" _
      ,,"Data not valid"
    Cancel = true
    exit sub
  end if

  'check to make sure prefnum has not been used
  'in another record for this student
  if nz(dLookup("StudentID" _
      ,"StudentPrefs" _
      ,"StudentID=" & me.studentid  _
      & " AND Prefnum = " & me.prefnum _
      & " AND StudentPrefID <> " & me.StudentPrefID _
      ),0) <> 0 then
    msgbox "You must enter 1-4 for the preference" _
      "Data not valid"
    Cancel = true
    exit sub
  end if

'~~~~~~~~~~~~~~~~~~

on the BeforeUpdate event of SubjectID

'~~~~~~~~~~~~~~~~~~

  'check to make sure prefnum has not been used
  'in another record for this student
  if nz(dLookup("StudentID" _
      ,"StudentPrefs" _
      ,"StudentID=" & me.studentid  _
      & " AND SubjectID= " & me.SubjectID _
      & " AND StudentPrefID <> " & me.StudentPrefID _
      ),0) <> 0 then
    msgbox "You may not use the same subject twice" _
      "Data not valid"
    Cancel = true
    exit sub
  end if

'~~~~~~~~~~~~~~~~~~

on the form BeforeUpdate event, make sure prefnum and subjectID are
filled out

'~~~~~~~~~~~~~~~~~~

  if isnull(me.prefnum) then
    msgbox "You must specify a preferance number" _
      "Missing Preferance Number"
    Cancel = true
    exit sub
  end if

  if isnull(me.subjectID) then
    msgbox "You must specify a Subject" _
      "Missing subject"
    Cancel = true
    exit sub
  end if
'~~~~~~~~~~~~~~~~~~

now, add the subform to the mainform
use a subform control and set these properties:

SourceObject --> the name of your subform

LinkMasterFields --> StudentID
LinkChildFields --> StudentID

there are still things you will need to do ... but this is a start <smile>

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

 *
   (: have an awesome day :)
 *