Type mis match in expression on a form

[Sheila]

I have read much of the resource that has been suggested to me in this forum,
but am still having problems.

Amongst other tables I have the following:-

Class table
Student table

I need to be able to view a class so I can see which students are enrolled
on it, and I need to be able to access student records to see which classes
they are enrolled on.

My StudentClass table consists of x3 fields: StudentClassID, ClassID,
StudentID. There is no data in the table at the moment because I need a
form/subform for data entry.

On my table, the ClassID field has a relationship with the ClassID field in
the Class table, the StudentID field has a relationship with the StudentID in
the Student table.
I have tried to set up a form/subform to enter the required information but
keep getting a "type mis match in expression error" and the subform is always
blank so I can't enter any details.

I have tried using the Student Table as the source for the main form but the
subform doesn't display the Class details and vice versa.

NB I need to be able to see all the fields in both form and subform and not
view them by ID. I have 200 students attending many classes and the classes
themsevles are made up of teacher/day/venue/activity/level so I need to make
sure I select the correct class by student (37 different classes).

It seems so simple, so why doesn't it work...???

 

[Tom Lake]

I have read much of the resource that has been suggested to me in this
forum,
but am still having problems.

Amongst other tables I have the following:-

Class table
Student table

I need to be able to view a class so I can see which students are enrolled
on it, and I need to be able to access student records to see which
classes
they are enrolled on.

You need three tables. A student can be enrolled in many classes while
a class can have many students. This is called a many-to-many relationship.
The intermediate table (called a junction table) has two keys, ClassID and
StudentID. You relate the ClassID in the Class table to the ClassID in the
junction table as a one to many and relate the StudentID in the Student
table to the StudentID in the junction table the same way. Here's a link to
get you started:

http://www.ehow.com/how_13626_define-many-many.html

Tom Lake

 

[Sheila]

Hi Tom,

Thanks for your reply and link. I have read both and am still stuck. The
junction table that you describe is (sort of) what I had set up (I called it
StudentClass table). Only I had x3 fields in it (I had StudentClassID which
was the primary key, instead of your idea of having x2 fields both as primary
keys). The trouble with using the junction table for the form was that when I
created the form, the only fields I was requested to complete was Student ID
(so I need a hardcopy printout of all 200 students with their respective ID
numbers) and ClassID (again I would need a printout of classes with ID
numbers)....very prone to data entry mistakes.

I have tried expanding my junction table to include the other fields that
make up the class details (eg day, teacher, venue) and the student details
(eg Firstname, Surname), but it still doesn't work. All I can see on my form
is a whole load of ID numbers which are meaningless. That is why I moved
towards the form/subform idea.

I have tried using Combo boxes so that when I click on class ID, it display


Please can you help. I'm getting desperate now. It seems like it should be
easy. I thought I had grasped this Access thing until I started putting it
into practise.

Thanks so much,

Sheila