Combobox - Retrieving only the required records

[Shaldaman]

I have 2 forms - 'Existing Instructor' and 'Existing Course' which are
linked. The first form has the following fields - InstructorID,
Instructor First Name, Instructor Lase Name and Department, all of
which get populated when the form loads - the information for all the
fields is retrieved from an 'Instructor' table. The 'Existing Course'
form has 4 fields - InstructorID, CourseNumber(a Combobox), Course Name
and Course Description. If a user on the 'Existing Instructor' form
clicks a button 'View Existing Courses'(named Command11), it opens the
'Existing Course' form, displaying the corresponding InstructorID but
the other fields should remain blank. When the user enters clicks on
the CourseNumber combobox to view the drop down list, only CourseNumber
related to that specific InstructorID should be retrieved from a
'Course' table. When a CourseID is selected, the corresponding Course
Name and Course Description should populate the remaining text fields.
The 'Instructor' table has a one-to-many relationship with the 'Course'
table (InstructorID is the primary key in the first table and the
foreign key in the second).

Here's my problem:
1)My combobox pulls up all the CourseNumber records in the Course table
and not just the ones corresponding to the InstructorID
2)When I select a value from the combobox, how do i get the remaining 2
fields Course Name and Course Description to get populated accordingly?


Any help would be appreciated. Thanks in advance.

 

[Brian]

1. RowSource of the combo box having the list of courses: in the criteria for
InstructorID, put [Forms]![yourFormName]![InstructorID]. Just make sure that
InstructorID is the bound column for the InstructorID, even if the
instructor's name is in the visible column. This will filter to only those
courses having that InstructorID.

2. Assuming that the second form is there to edit one course at a time:

a) Set the form's RecordSource to all courses having the selected instructor
(using basically the same query/criteria from the RowSource above),

b) Put your existing course-picking combo box in the form's header (I call
it CourseSelector in the example below), but make it UNBOUND. In its
AfterUpdate event, put something like this:

If IsNull([CourseSelector]) Then Exit Sub
Me.RecordsetClone.FindFirst "[CourseID] = " & Me![CourseSelector]
Me.Bookmark = Me.RecordsetClone.Bookmark

c) In the detail section of the form, make sure there are text boxes bound
to the other fields in the Courses table. They will automatically populate
based on the course selected in CourseSelector.

 

[Shaldaman]

Hi Brian
Thanks a bunch for your prompt reply. I'm a wee bit confused about the
first part. I got the second part to work, i.e. filling up the Course
Name and Course Description fields based on what is selected in the
CourseID combo box but I'm not sure about the first part. Right now,
the RowSource for the combo box is a query. When I open the Query
Builder to modify the query, it shows the following:
Field : CourseID
Table: Course
Sort :
Show:
Criteria:

Is this is where I've to specify the criteria you mentioned? But when I
enter that, it doesn't bring up any records. Do i need to do something
in VBA? by the way, InstructorID is the bound column for the
InstructorID.

 

[Brian]

I think I took too much for granted concerning your table structure in my
first post. If each course has only one Instructor, then you will need an
InstructorID field in the Courses table. Then, you add this field to the
query in the RowSource we are discussing, and put the criteria in the
InstructorID criteria section. You will also need to add a hidden text field
to the subform; call it InstructorID and make its default value this:
=[Forms]![mainFormName]![InstructorID].

That way, the current instructor gets tied to the course automatically.

However, if multiple instructors teach the same course, then read on...

With only Courses and Instructors tables, you have no place to store which
Instructors teach which Courses. In order to tie multiple Instructors to
multiple Courses, you will need at least three tables (and this will be
over-simplified by way of example.

Table: Courses
Field: CourseID
Field: CourseDescription

Table: Instructors
Field: InstructorID
Field: InstructorName

Table: CourseInstructors
Field: CourseInstructorID (Autonumber, primary key)
Field: InstructorID
Field: CourseID

Create relationships between Courses.CourseID & CourseInstructors.CourseID
and between Instructors.InstructorID and CourseInstructors.InstructorID.

The form under discussion, now, will draw from the third table.

 

[Shaldaman]

Never mind, Brian. I got it to work. I added the InstructorID field to
the query in the second column and specified the criteria there. Works
well, except the initial values of the Course
Name and Course Description fields are from the first record. i think i
know how to fix that. Thanks a lot...that was a huge help...

 

[Shaldaman]

Thanks again for your reply...certainly learnt a lot. Actually, your
assumptions were all correct - Each course has only one Instructor, I
have an InstructorID field(primary key in Instructor table) in the
Courses table. I added this field to the query in the RowSource as you
suggested and put the criteria in the InstructorID criteria section.
I'm also able to get the InstructorID from the Existing Instructor form
to show up in the InstructorID field on the Existing Courses form. With
the query in place, when i use the combo box drop down, only the
courses related to that specific instructor show up and corresponding
values for Course Name and Course Description are filling up.

Here's the thing though - should those 2 fields(Course Name and Course
Description) also be unbound because right now, any changes to those
fields will make changes in the table as well. i initially wanted it
only for lookup.

 

[Brian]

Try this: leave them bound, but disable and/or lock the boxes so that the
user cannot edit them.

 

[Shaldaman]

Yes, that's a great idea. Thanks for your patience and for being so
helpful. One quick question before we wind up - the initial values of
the Course Name and Course Description fields are from the first record
in the Course table, not the first record for that particular
instructor. How do i open the form so that those fields are initially
blank? When i tried to assign blank values to the fields in the form's
OnCurrent property, it messes with the tables by replacing values with
blank fields...

 

[Brian]

In the form's RecordSource query, include the InstructorID and insert the
exact same criteria as you did for the combo box. This will limit the courses

 

[Shaldaman]

Hi Brian
I'm still not able to prevent the first record in the Course table from
displaying in the Course Name and Course Description fields. My form's
doing some strange things. After I included the InstructorID and
inserted the criteria for the combo box, every time I use the drop down
to view the courses, it shows all the corresponding courses for that
instructor but also the first record which was initially in the
fields(that seems to be getting inserted into that instructor's record
or any other instructor's record for that matter because every time i
open the second form, the correct InstructorID and list of courses are
shown in the combo box, but each time, since the first record in the
Course table is displayed in the Course Name and Course Description
fields, that gets inserted into each instructor's record too)

For instance, let's suppose a CSC instructor is the first entry in the
Instructor table and a COM course is the first entry in the Course
table. When I log in as the CSC instructor and go to this 'Existing
Courses' form, it displays all the courses he/she teaches but because
this COM course is the first entry in the Course table, for some
reason, the COM course's information is displayed in the Course
Description and Course Name fields. So whenever I close the form and
look at the Instructor table, it shows that the COM course is now part
of the CSC instructor's list of courses and has moved from the COM
instructor's list of courses.

How do I prevent my form from inserting values into the table and
instead only display values? Right now, when the form loads, the
correct InstructorID shows up, the combo box looks blank until it's
expanded, the initial values of the Course Name and Description point
to the first record...Each time, I select a course in the CourseID
combo box, the respective values of the Course Name and Description
show up but by now, the drop down includes the COM course as well.

Sorry to be a nuisance. I'm struggling with this bit...

 

[Brian]

OK. First a question: if this form is just to SHOW the courses for the
instructor, where do you ADD the courses to the instructor? This can be done
with a form almost identical to the Courses form.

Just a little review to help me understand.

The RecordSource of the Courses FORM should have this in the criteria for
InstructorID:

=[Forms]![CurrentInstructor]![InstructorID] .

It might be more efficient to just take this to e-mail so you can just zip
up your app & have me review it:

brainy
at
pacifier
dot
com

 

[Shaldaman]

Yes, Brian. This form is only for showing the courses. There is another
form for entering new courses. This is so that an instructor can view
the courses he/she is already teaching and then add assignments, etc. I
specified the criteria that you mentioned below in my query, which is
the RecordSource of the Courses form, and it works in the sense that
the correct courses are getting listed for every instructor when the
Courses form loads. Only hitch is, when the form opens and before I
click on the combo box to drop down and see the course list, there are
already values in the Course Name and Course Description text fields
from the very first record in the Course table. This record is however
not related to that instructor and when i close the form, that course
ends up getting added to this instructor's list of courses.

Essentially, I just want my form to be used for lookup and no data
entry should be possible. When the form opens the correct InstructorID
should show up(which it does), the correct list of courses should show
up in the combo box(which works too), the other 2 text fields should
remain blank until a value from the combo box has been selected. Let me
know if you still want me to send my file to you. Thanks.

OK. First a question: if this form is just to SHOW the courses for the
instructor, where do you ADD the courses to the instructor? This can be done
with a form almost identical to the Courses form.

Just a little review to help me understand.

The RecordSource of the Courses FORM should have this in the criteria for
InstructorID:

=[Forms]![CurrentInstructor]![InstructorID] .

It might be more efficient to just take this to e-mail so you can just zip
up your app & have me review it:

brainy
at
pacifier
dot
com

Hi Brian
I'm still not able to prevent the first record in the Course table from
displaying in the Course Name and Course Description fields. My form's
doing some strange things. After I included the InstructorID and
inserted the criteria for the combo box, every time I use the drop down
to view the courses, it shows all the corresponding courses for that
instructor but also the first record which was initially in the
fields(that seems to be getting inserted into that instructor's record
or any other instructor's record for that matter because every time i
open the second form, the correct InstructorID and list of courses are
shown in the combo box, but each time, since the first record in the
Course table is displayed in the Course Name and Course Description
fields, that gets inserted into each instructor's record too)

For instance, let's suppose a CSC instructor is the first entry in the
Instructor table and a COM course is the first entry in the Course
table. When I log in as the CSC instructor and go to this 'Existing
Courses' form, it displays all the courses he/she teaches but because
this COM course is the first entry in the Course table, for some
reason, the COM course's information is displayed in the Course
Description and Course Name fields. So whenever I close the form and
look at the Instructor table, it shows that the COM course is now part
of the CSC instructor's list of courses and has moved from the COM
instructor's list of courses.

How do I prevent my form from inserting values into the table and
instead only display values? Right now, when the form loads, the
correct InstructorID shows up, the combo box looks blank until it's
expanded, the initial values of the Course Name and Description point
to the first record...Each time, I select a course in the CourseID
combo box, the respective values of the Course Name and Description
show up but by now, the drop down includes the COM course as well.

Sorry to be a nuisance. I'm struggling with this bit...

 

[Brian]

Zip it & send it. Design issues are so inter-related that it is hard to get a
complete picture without seeing the code.

Yes, Brian. This form is only for showing the courses. There is another
form for entering new courses. This is so that an instructor can view
the courses he/she is already teaching and then add assignments, etc. I
specified the criteria that you mentioned below in my query, which is
the RecordSource of the Courses form, and it works in the sense that
the correct courses are getting listed for every instructor when the
Courses form loads. Only hitch is, when the form opens and before I
click on the combo box to drop down and see the course list, there are
already values in the Course Name and Course Description text fields
from the very first record in the Course table. This record is however
not related to that instructor and when i close the form, that course
ends up getting added to this instructor's list of courses.

Essentially, I just want my form to be used for lookup and no data
entry should be possible. When the form opens the correct InstructorID
should show up(which it does), the correct list of courses should show
up in the combo box(which works too), the other 2 text fields should
remain blank until a value from the combo box has been selected. Let me
know if you still want me to send my file to you. Thanks.

OK. First a question: if this form is just to SHOW the courses for the
instructor, where do you ADD the courses to the instructor? This can be done
with a form almost identical to the Courses form.

Just a little review to help me understand.

The RecordSource of the Courses FORM should have this in the criteria for
InstructorID:

=[Forms]![CurrentInstructor]![InstructorID] .

It might be more efficient to just take this to e-mail so you can just zip
up your app & have me review it:

brainy
at
pacifier
dot
com

Hi Brian
I'm still not able to prevent the first record in the Course table from
displaying in the Course Name and Course Description fields. My form's
doing some strange things. After I included the InstructorID and
inserted the criteria for the combo box, every time I use the drop down
to view the courses, it shows all the corresponding courses for that
instructor but also the first record which was initially in the
fields(that seems to be getting inserted into that instructor's record
or any other instructor's record for that matter because every time i
open the second form, the correct InstructorID and list of courses are
shown in the combo box, but each time, since the first record in the
Course table is displayed in the Course Name and Course Description
fields, that gets inserted into each instructor's record too)

For instance, let's suppose a CSC instructor is the first entry in the
Instructor table and a COM course is the first entry in the Course
table. When I log in as the CSC instructor and go to this 'Existing
Courses' form, it displays all the courses he/she teaches but because
this COM course is the first entry in the Course table, for some
reason, the COM course's information is displayed in the Course
Description and Course Name fields. So whenever I close the form and
look at the Instructor table, it shows that the COM course is now part
of the CSC instructor's list of courses and has moved from the COM
instructor's list of courses.

How do I prevent my form from inserting values into the table and
instead only display values? Right now, when the form loads, the
correct InstructorID shows up, the combo box looks blank until it's
expanded, the initial values of the Course Name and Description point
to the first record...Each time, I select a course in the CourseID
combo box, the respective values of the Course Name and Description
show up but by now, the drop down includes the COM course as well.

Sorry to be a nuisance. I'm struggling with this bit...

 

[Shaldaman]

Ok, I'll send it. Thanks for taking the time and trouble to help me
with this. I really appreciate it.

 

[Shaldaman]

i sent it...let me know if you got it...

 

[Shaldaman]

i did this: "the RecordSource of the Courses FORM should have this in
the criteria for
InstructorID:

=[Forms]![CurrentInstructor]![InstructorID] . "

It seems to work..