Table1 - has unique number and other personal information
         like name and address, etc.
CaseFile
  CaseNumber(unique)
       .
       .
       .
Table2 - Doctor information
DoctorTable
  DoctorID
     .
     .
     .
Table3 - this table allows me to store an unlimited
         number doctors for a given patient.
ContactTable
  CaseNumberID (From Table 1 - CaseNumber )
  ContactID  (From Table 2 - DoctorID )

The following is excerpts of the code I use:

    ' Set the data control's DatabaseName and RecordSource properties
    datDoctorInfo.DatabaseName = "CaseFile.MDB"
    datDoctorInfo.RecordSource = "DoctorTable"

    ' Set the data control's DatabaseName and RecordSource properties
    datContactTableInfo.DatabaseName = "Contact.MDB"
    datContactTableInfo.RecordSource = "ContactTable"

At this point I already know which case number I am working on.

This is the SQL statement I use to list all of the Doctors associated
with this particular patient. ( The SQL statement show here is broken
up for easier viewing.  It is one complete line as required by Visual
Basic)

Case_Number = #

datDoctorInfo.RecordSource = "Select * from [Doctor] WHERE

   " & datContactTableInfo.Recordset!CaseNumberID & " =
   " & Case_Number & "   AND  

   " & datDoctorInfo.Recordset!DoctorID & " =
   " & datContactTableInfo.Recordset!ContactID & "

     ORDER BY [LastName]"

Suppose we have CaseNumber = 1 and he has 3 Doctors (1, 2, 3)
                CaseNumber = 2 and he has 3 Doctors (4, 5, 6)

My results are:  When Case_Number = 1 I get all the entries in Doctor
Table.  When Case_Number = 2 I don't get any.  I can't figure out why.  
Any ideas would be greatly appreciated.  Thanks in advance!

    I think you are close, but not quite.  If I read your statement
correct, it will evaluate the *value* of
datDoctorInfo.Recordset!DoctorID, (something like 52 or 100 or some other
ID number) and not look at the *field name*.  What you want to do is more
like this:

datDoctorInfo.RecordSource = "Select * from [Doctor] WHERE "

    & " CaseNumberID = "                <=== change this line
    & Case_Number &
"   AND  DoctorID =" &                  <=== change this line
   & datContactTableInfo.Recordset!ContactID &
"   ORDER BY [LastName]"

So that your final SQL reads something like

"Select * from [Doctor] Where CaseNumberID = 53 and DoctorID = 1"

   This says to look at the values of the fields named CaseNumberID and
DoctorID, and compare them to the values you want.  Your example (I
believe) said compare the *value* of datDoctorInfo...DoctorID, and
compare it to the *value* that you want.  The resulting SQL of your
original (if I am interpreting it correct) would have read:

"Select * from [Doctor] where 53 = 53 and 1 = 1"

  Which makes no sense.  Hope this helps.  When in doubt, do the
following:

- go into debug mode, and stop at the statement where you assign the SQL
value.
- copy the SQL - go to the debug window and do a "?
datDoctorInfo.Recordsource"
- Open up MS Access against your database, and paste the SQL that you
copied into a new query.  This will show what your SQL statement looks
like and what it will return,

--
Jim Gilbert                    Sub Disclaimer()

Monsanto Co.                      myOpinionsReflectMyEmployers = False
St. Louis, MO                  End Sub