Combo box contents

[ashows]

I am creating a database for use in an educational institution. One form (frmCourseInfo) contains fields for the user to enter the following: course description, course abbreviation, course number, and if the course is still being offered. The last field is a check box called "Deleted." Another form (frmCoursesAndFaculty) allows the user to assign instructors to the courses. On this form a combo box displays the courses entered using frmCourseInfo. This is working fine. However, the user wants the drop down list of the combo box to display only the courses that are NOT marked "deleted" on frmCourseInfo. I set the combo box criteria to display the courses where the delete field = 0. This works for the drop down list of the combo box, but it displays a "blank" combo box on the form for any courses that were taught in the past and have since been deleted. Is there a way to display the course information (both current and past) in the combo box itself but limit it's drop down list to only the current courses?

 

[gusbrunston]

[tt]
Hi:

There are probably several ways. I usually prefer to use a query for the data source of a combo box, because I learned early on to enter criteria to filter records in a query. In the same way, you can use an SQL statement in the Row Source property of the combo box.

You can have multiple combo boxes, synchronized so that you first select "Current", "Deleted", "Cancelled", "Previous Semester", etc; then the next box shows only those courses that fit the description in the first cbo.

That is, if I understood your question.

Cheers,[/tt]

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7
Webmaster: www.rentdex.com[/tt]

 

[ashows]

I want the form to display course info and instructors from past semesters as well as the current semester. Only courses that are currently being offered should appear in the combo box's drop down list. When the user is assigning instructors to classes for upcoming semesters, they may select a course from the combo box list to assign a teacher to the selected course. For classes that are no longer taught, I still want the course name to appear in the combo box on the form (to see when the course was offered and who taught it) but the course should not appear in the drop down list for the combo box when the user is entering new data. Currently the Row Source for the combo box is as follows:

SELECT tblCourseInfo.ID, tblCourseInfo.CourseAbbr, tblCourseInfo.CourseNo, tblCourseInfo!CourseAbbr & " " & tblCourseInfo!CourseNo AS Course, tblCourseInfo.Delete FROM tblCourseInfo WHERE (((tblCourseInfo.Delete)=0)) ORDER BY tblCourseInfo.CourseAbbr, tblCourseInfo.CourseNo;

The combo box displays the course abbreviation and the course number. The WHERE Delete=0 part of the statement removes the discontinued courses from the combo box's drop down list of courses. The problem is that when a course is marked as deleted, the combo box on the form appears to be blank for those records. The data is still there, it's just not displayed since the row source is set to display only the courses that have not been marked as deleted.

Am I trying to get a single combo box to do too many things?

 

[gusbrunston]

[tt]
I don't understand what you're trying to do. I'm used to simpler constructs, such as categories of products that can be sorted by manufacturers, then model numbers. Your's is sort of a unique application. I guess you have to be there.[/tt]

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7
Webmaster: www.rentdex.com[/tt]