Combo box value option

[asunpraise]

I want to have a combo box that populates the Course_no column in Course table. The user has an option to choose one of the Course_no or to get all courses.

In Row Source under Properties, I can only set "SELECT [Course].[Course_No] FROM Course;". The combo box can only get one Course_no into its control field. I don't know where to put "Show all the Courses" as an option. Any suggestions how to define a combo box like this?

 

[SBendBuckeye]

Create a query that selects what you need to display from your course table and then copy the SQL from the SQL view. Let's say it was something like the following where CourseID is numeric, CourseName is text and CourseTime is a date/time:

SELECT tblCourse.CourseID, tblCourse.CourseName,
tblCourse.CourseTime
FROM tblCourse
ORDER BY tblCourse.CourseName, tblCourseTime;

Create a one record table named tblALL and put a value like " [All Courses]" in a field named AllDescription. Then convert your query created above into a Union query as follows:

SELECT tblCourse.CourseID, tblCourse.CourseName,
tblCourse.CourseTime
FROM tblCourse
UNION
SELECT -99 AS ID, tblAll.Description, now() AS Time
FROM tblALL
ORDER BY 2, 3;

You cannot view a Union query in design view, only in SQL view. The column names from the 2 tables do not have to correspond in name but they do have to have the same data types.

In this case, you really don't care about either column 1 or column 3 for the all side of things so we just dummy in some easy data.

If the column names do not correspond then you select them based on relative column count, beginning with 1. Since you defined your all field with a leading space and used that column as the high order sort, it should be the first row in your combo box.

Good Luck!