Junction table used, but still doesn't work

[Sheila]

I have posted this query again, because I still can't get this to work.....

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 (PK = Class ID) This table has data
Student table (PK = Student ID) This table has data
StudentClass table (Junction table for above many to many. PKs = Class ID
and Student ID) This data has no data yet because I seem to be unable to make
a form for data entry and this is where my problems start.

Class table related via ID field to Junction table (StudentClass Table)
Student table related via ID field to Junction table (StudentClass Table)

I need a form so I can record in my database, which students attend which
classes and, I need to be able to view the information by a class so I can
see which students are enrolled on it, but first, I need to get the data in
the database.....!

I have tried x2 ways to set up my form for data entry using the Junction
table and having read and received various bits of information from postings,
I believe that the way to proceed is with a simple form produced from the
Junction table.

......but the trouble with using the junction table for the form was that
when I created the form, I needed to know the IDs for each field. So I
imagined myself sitting at my laptop for x3 days with a hardcopy printout of
all the students' names with their respective ID numbers and another hardcopy
print out of the ClassIDs. All I could then visualise was a whole list of
meaningless numbers...I quickly realised that there had to be a better way....

So, I thought that Combo boxes would be the answer. I could click the drop
down box next to the Class ID field, it would display the full list of
classes with all their details, I would select the one I wanted, and all the
remaining fields relating to that class would be completed for me. I would
then click the drop down box next to the Student ID field and the same thing
would happen.... but I can't seem to get the set-up correct. Are the combo
boxes bound or unbound..?? Do I want the combo box to look up the values in a
table or query....if so, which table...etc etc. Are the combo boxes not
helping because the data required to auto-populate is contained in other
tables NOT the junction table (junction table has no data yet)..??

In an ideal world, I would like a form where I enter the ClassID field and
it auto-populates the remaining class fields (teacher,venue,time etc), and
then I just enter a list of student names (from a combo box so I don't make
spelling mistakes). This would all link back to the other tables and hey
presto.....all singing all dancing. If I can do this bit, I know that I can
crack all the other bits I need to do to complete my database.

I think I understand the 1-many relationships and realised that I had
many-many. I am absolutely positive that my relationships are all set up
correctly.

It seems so simple, so why doesn't it work...??? I am at a total loss as to
how to proceed and I've become desperate.....Excel is beckoning......

I'm sorry this is so long but I thought that if I make it to brief you would
not fully understand what I'm trying to do, and what I've done so far.

Please please help,

Thanks,

 

[Sheila]

Hi Bob,

Thank you so much for your quick reply. I didn't realise I had to set up a
query. I assumed I had to set up a form based on the Junction table, but what
you says makes sense even before I give it a go. It 12:30am UK time here so
I'm off to bed, but I will come back to it in the morning and let you know
how it all goes...I may be able to get off the merry-go-round that I've been
for days now...

Thank you so much,

 

[John W. Vinson]

I need a form so I can record in my database, which students attend which
classes and, I need to be able to view the information by a class so I can
see which students are enrolled on it, but first, I need to get the data in
the database.....!

Bob's suggestion is one way to go, but there's a somewhat more complex (and
IMO somewhat more user-friendly) solution: Subforms.

If you want to bring up a class and enroll students in it, create a Form based
on your Classes table. You may want to use the combo box wizard to create an
unbound combo with the "use this combo to find a record" option, just so you
don't have to scroll through all the records to find a class.

On this Form put a Subform, based on the junction table. Use the ClassID as
the master/child link field; this will put the main form's current class ID
into new records on the subform, and will cause only records for that class to
be displayed.

On the subform put a Combo Box bound to the StudentID field. The combo should
be based on a query on the Students table, such as

SELECT StudentID, [LastName] & ", " & [FirstName] FROM Students ORDER BY
LastName, FirstName;

to store the studentID while displaying "Jones, Jimmy" for example.

 

[Sheila]

Hi John,

The idea of a sub form made sense to me. But...I still can't get it to work.
My Junction table has x2 fields, ClassID and StudentID both related back to
their own tables. I have followed your instructions so many times today that
I really can't think any more. All I get is a blank subform box with a
message that tells me basically that the code I am using is way too
complicated then I get a mismatch expression which is where I started when I
posted this query originally in a different post.

I am sure this should be really simple and I am sure I must be doing
something really stupid but I can't see what. The trouble is that I am now
running out of time and if I don't get this sorted soon I will have to
abandon the whole idea and give up on it which would be a real shame.

Perhaps it would be better for me to e-mail what I have over to someone. At
the moment it is quite small because I have only enetered a few "token"
students' names and no personal details.

Please help........!

Thanks


--
Thanks, Sheila

I need a form so I can record in my database, which students attend which
classes and, I need to be able to view the information by a class so I can
see which students are enrolled on it, but first, I need to get the data in
the database.....!

Bob's suggestion is one way to go, but there's a somewhat more complex (and
IMO somewhat more user-friendly) solution: Subforms.

If you want to bring up a class and enroll students in it, create a Form based
on your Classes table. You may want to use the combo box wizard to create an
unbound combo with the "use this combo to find a record" option, just so you
don't have to scroll through all the records to find a class.

On this Form put a Subform, based on the junction table. Use the ClassID as
the master/child link field; this will put the main form's current class ID
into new records on the subform, and will cause only records for that class to
be displayed.

On the subform put a Combo Box bound to the StudentID field. The combo should
be based on a query on the Students table, such as

SELECT StudentID, [LastName] & ", " & [FirstName] FROM Students ORDER BY
LastName, FirstName;

to store the studentID while displaying "Jones, Jimmy" for example.

 

[John W. Vinson]

The idea of a sub form made sense to me. But...I still can't get it to work.
My Junction table has x2 fields, ClassID and StudentID both related back to
their own tables. I have followed your instructions so many times today that
I really can't think any more. All I get is a blank subform box with a
message that tells me basically that the code I am using is way too
complicated then I get a mismatch expression which is where I started when I
posted this query originally in a different post.

Please post (or repost) the structure of your tables in the form

Students
StudentID <Autonumber, Primary Key>
FirstName
LastName

etc. and indicate how the tables are related; and post the Recordsource
property of your form and subform. If that recordsource is a Query open the
query in SQL view and post it here. Also post the actual text of the error
message.

My guess is that you're trying too hard and doing a lot of work that Access
will do for you! The subform's recordsource should be JUST your junction table
- nothing else; you don't need to base it on a multitable query. Is that what
you're doing?