Celine,
OK, it sounds like Students and Majors table is the many side of a
one-to-many relationship with Students, but I can't understand why you'd be
trying to limit the Rows of your combo box based on what's already been
entered, rather than allowing the user to add *any* new major.
Viewing only the majors assigned to *this* student is the job of the
LinkMasterFields and LinkChildFields of the subform. It doesn't have
anything to do with the RowSource of the combo box. If my understanding of
your your application is correct, the following solution is more
straightforward:
Tables:
Students
------------------
StudentID PK
FName
LName
...etc.
Majors
------------------
MajorID PK
Major Text
Students and Majors
----------------------------
StudentMajorID AutoNumber PK
StudentID Integer (Foreign Key to Students)
MajorID Integer (Foreign Key to Majors)
Main Form
----------------
RecordSource Students
Fields All
Subform
----------------
RecordSource Students and Majors
Controls Combo Box for MajorID only
LinkMasterFields StudentID (Students table)
LinkChildFields StudentID (Students and Majors table)
Combo Box Properties
---------------------------
RowSource SELECT Majors.ID, Majors.Major FROM Majors
ORDER BY Majors.Major;
ControlSource MajorID (In the Students and Majors table). This field's type
must match the type of Majors.MajorID
BoundColumn 1
ColumnWidths 0";x", where x is wide enough to display the widest major
If I'm way off base, please post the properties I requested in my last post,
and I'll try again.
Sprinks
Sprinks,
Thanks for your reply. Sorry for the confusion.
Actually, that combox on the subform shows as a datasheet on the
subform.
It shows the data from the Students and Majors table, and also, a user
is able to add another major for the same student from the datasheet
(you can choose from the combobox on the datasheet).
Also, just so you know, a student can have many majors, that's why I
have that Students And Majors table.
So, if not all of the majors are assigned into the Students And Majors
table, then my combo box will only show the ones that have been
assigned.
I tried to do a join query where I would get all the Majors and only
those records from students and majors where the fields are equal. That
gives me all my majors, but when I go in my datasheet and try to select
one that hasn't been assigned for any student, then the box stays empty
(in the Students And majors table, it adds the studentID, but nothing
for the major ID)
I hope this is more clear!
Thank you,
Celine
Celine,
I'm confused.
My understanding was that the purpose of the subform was to allow a user to
add a new major for a given student. If so, I would think you'd want the
entire list of majors to pick from; I don't understand the purpose of this
Students and Majors table.
Please post the following properties:
- the RecordSource of the main form and subform
- the LinkMasterFields and LinkChildFields of the subform control
- the ControlSource and BoundColumn of the combo box
Sprinks
:
Hi Sprinks,
Thank you for your reply.
Here is the RowSource for my combobox:
SELECT DISTINCT [Students And Majors].MajorID, Majors.MajorName FROM
Majors INNER JOIN [Students And Majors] ON Majors.MajorID = [Students
And Majors].MajorID;
That combo box is situated on a subform which is on the main student
form.
So I need to get the list of major(s) for the current student. This is
why I need to refer the student table in my rowsource. If I don't, then
I effectively get all of the available majors, but it doesn't show the
major(s) for that specific student
(The table Students And Majors stores the studentID and majorID for
each student having a major).
Any ideas?
Thank you very much,
Celine
Sprinks wrote:
Hi, Celine. Please post the RowSource of your combo box. It sounds to me
like you have an unnecessary condition in the query statement. It should be
something like:
SELECT Majors.ID, Majors.Name FROM Majors ORDER BY Majors.Name;
Also post the RecordSource for the form. If this is a query, please post
the SQL.
Sprinks
:
Hi All!
I have a subform based on an empty table that contains 2 fields:
StudentID and MajorID. In the future, the user will be able to add
major(s) for a given student via this form.
On this form, I have a combo box based on MajorID. It looks up the
MajorName via a query that includes all records from the Majors table,
and only those records from the table students and majors where the
joined fields are equal (otherwise, I don't get anything in my table).
In can see all my majors in my combobox.
However, when I want to select a major from the combobox, it stays
blank, and I can see in Students and Majors table that the studentID
has been added, but the majorID is empty.
However, if I enter manually some studentID and majorID in the table,
then my combobox works perfectly.
So if I start from an empty table, it won't work, but as long as at
least one of each majorID is entered into the Students And Majors
table, then it works. (if there is only one majorID found in that
table, the combobox will still show all majors, but only the one that
is in the table will work).
I hope I was not too confusing...
Any suggestions? Anyone?
Thank you
Celine
