Combo box record source

[KneeDown2Up]

Right, I have a two tables, 'TblDays' and 'TblStudents'. They have a one to
many relationship (from days to students). I have a form with a combo box
with various text boxes bound to the students table, and one combo box bound
to the days table. I need the user to be able to drop down a selection of
days and make a selection and this data related to that student thereon. What
is happening is when I select the day for any given student, it changes the
days table - I only want the relationship to remain, not the data within the
days table to change.

What am I doing wrong please?

 

[Wayne-I-M]

If you open the properties box for the combo you will see the name of the
field in the Control Source row. - delete this.

Leave the row type and row source as they are.

You will then be able to select and not chage the table data

 

[KneeDown2Up]

Yes, thanks Wayne, I sort of found that whilst playing around with it, the
only problem is that I have no correlation of the day selected and the
student; how are they linked, how is it retrieved that any given day was
selected for the student? Is the relationship correct or is it something to
do with the form expression?

 

[Wayne-I-M]

Sorry I don't know. You need to give more information about the way your
application is set up.

As a general topic you should have the student ID listed in the combo and
then set the AfterUpdate of this combo to set the row source for the next.

A very simple method of doing this it to base both combos on queries and use
the criteria of the 2nd to be the ID the is selected in the 1st.

 

[KneeDown2Up]

O, thanks Wayne, I appreciate it's difficult to advise on this without seeing
the whole thing. I guess my lack of knowledge is limiting me from
understanding exactly what you mean.

The form I have is based on a query;-

SELECT tblStudents.FirstName, tblStudents.LastName,
tblStudents.ParentsNames, tblStudents.PostalCode, tblStudents.PhoneNumber,
tblStudents.Notes, tblStudents.[Date Added], tblStudents.DoB,
tblStudents.Age, tblDays.Day
FROM tblStudents INNER JOIN tblDays ON tblStudents.StudentID =
tblDays.[Student ID];

I guess it's something to do with there being no referrence to the student
id and the days ID, not quite sure how I invoke this without the control
source as 'Days' - but then of course it changes the tbldays. Driving me mad
now!!

 

[KneeDown2Up]

oh, and the combobox query (on the same form) is;-

SELECT tblDays.Day
FROM tblDays;

 

[Ken Sheridan]

If the relationship is really one-to-many from Days to Students then you
should have a field in the students table referencing the primary key of the
days table, e.g. DayID, and the combo box should be bound to this field.

However, the relationship may in fact be many-to-many in which case you'll
need a third table to model the relationship by resolving it into two
one-to-many relationships. It would help us advise you on this if you could
provide some details of what the database is intended to model in terms of
the real world entities and the relationships between them.

Ken Sheridan
Stafford, England