Combo box: values are correct, but when selected becomes blank

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

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

" wrote:

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

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

" wrote:

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

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

" wrote:

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

" wrote:

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

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

Sprinks wrote:
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

" wrote:

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

" wrote:

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

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

" wrote:

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

Sprinks wrote:
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

" wrote:

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

" wrote:

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

Oh man oh man!! I am really dumb...
lol!!
You are so so right.
Ok, let me tell you what I missed:
RowSource SELECT Majors.ID, Majors.Major FROM Majors ORDER BY
Majors.Major;
Well, actually, I missed: Majors.ID... I just added Majors.Major, and I
was happy with it...
Well, that was after I removed the Students And Majors table from that
rowsource...

Ok, sorry about all the trouble!! I am very glad you were here to help
me!! Thank you so much!!

Celine


Sprinks wrote:
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

" wrote:

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

Sprinks wrote:
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

" wrote:

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

" wrote:

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

Celine,

My pleasure.

One note: although it's a nuance, when you have a "lookup" table such as
Majors, it is general practice to store its primary key rather than the text
associated with it in the secondary table, in your case, Students an Majors.
There are several reasons for doing so: first integer fields take up less
memory and storage, and are faster to access.

Also, you limit downstream changes to one record. For example, let's say
the college changes the name of the History major to Historical Studies. If
you store the primary key in Students and Majors (known as a "foreign key"),
you simply make the change in the Majors table. If you are storing the text,
you will need to change every student record that has that major.

But the most compelling reason to do this is so that Access can enforce
referential integrity through a Relationship.

Note that the field types have to match. If Majors has a numeric key, the
foreign key must also be numeric. Also note that you will still *display*
the more meaningful text in the combo box by setting the foreign key's
ColumnWidth to 0". And you can print the text on a report by linking the two
tables in a query by the primary key/foreign key, and selecting the text. So
you lose no convenience, just gain efficiency.

This issue is part of the more general subject of Database Normalization.
You can find more information at the following links:

ACC: Database Normalization Basics
http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genacc...abasenorm.html

Support WebCast: Database Normalization Basics
http://support.microsoft.com/default...lurb060600.asp

Database Normalization:
http://burks.bton.ac.uk/burks/foldoc/35/28.htm

5 Rules of Database Normalization:
http://www.datamodel.org/NormalizationRules.html

"Understanding Relational Database Design" Document Available in Download
Center:
http://support.microsoft.com/?id=283698
http://support.microsoft.com/?id=164172

ACC2000: "Understanding Relational Database Design"
http://support.microsoft.com/?id=234208

Fundamentals of Relational Database Design:
http://support.microsoft.com/?id=129519

Sprinks