Fields from 2 tables displayed in Form without using Subforms

[EastCoast]

I have a "Courses" table and a "Classes" (every occurence of a course)table. I want to display a few fields from the "Courses" Table on the "Classes" Form. I don't want a subform because that would show one course and then every occurence of that course on that screen. What I need is for each record (each occurrence of a class in the class form) to show individually and to give me a combo box that lets me choose what course it was that ocurred on that day.

Example: If my Classes form shows:

Date: 5/14/2006
Instructor: Bo Diddly
Then, there should be a box somewhere that lets me choose what course it was (such as: Making Widgets 101, Excel Intro, Communication Skills, etc.). This box would then store the data somewhere (I guess the table, not store for later use?) so that I can run a report on every Widgets 101 class in the month of May.
Make sense? Any help you can offer is much appreciated.

 

[snyperx3]

if you have a courses table im guessing it has
CourseNumber (Key)
CourseName

Why not add a field to your classes table that contains the coursenumber. Then on your classes form you could just have a combo box that contains the coursenumber and coursename with column 1 bound and the widths set to 0";1". Then you could just store the value of the combo box in the coursenumber field of the classes table for that record.

-Pete

 

[EastCoast]

Yes, I do have "CourseID" and "CourseName" as my fields in the "Courses" Table. If I do what you suggest, how do I grab the "CourseID" from the "Classes Table" and the "CourseName" from the "Courses" Table and put them into one combo? Will this be created using a SQL statement?

Finally, will this allow me to choose the same CourseName on another record to reflect that this class (Example: Communication Skills) was also offered on another day?

 

[EastCoast]

I tried this:
SELECT DISTINCTROW [Classes].[CourseID] FROM [Classes] AND [Courses].[CourseName] FROM [Courses] AS [Course]

But it returns an Error in the FROM clause

 

[snyperx3]

Im sorry i guess i read your initial question wrong. I thought you wanted the form to contain the date from the class, the name of the instructor, and then a combo box which allows you to choose a class.

Is this not what you want?

Did you only want the courses that have values stored in the classes table?

heh, im sorry, but ive gotten very confused now.

-Pete

 

[EastCoast]

Okay, I'll try describing it differently. I have a "Courses" form and a "Classes" form. I want to put the "CourseName" field from the "Courses" table into the "Classes" form. How do I do that?

I don't want to make "Classes" form a subform of the "Courses" form (I know that would work). Rather, I want the classes form to be such that anytime a new class is offered, I can type in the date, time, location (etc.) in the fields that are on the classes form and ALSO choose the name of the class (CourseName) from a drop down list for that particular class. Does this help? I wish I could paste a screen shot for you.

 

[snyperx3]

of course you can do that.

the best way in my opinion is for you to have a field in your classes table for courseid. then on your classes table make a combo box that has the recordsource of the Courses table. or if it has more than just those two fields you could do "SELECT CourseID,CourseName FROM Courses;"

Then you set the bound column to 1, and set the control source to CourseID OF THE CLASSES TABLE.

now when you open the form, the combo box will contain all the courses, and if you select one its courseid will be stored in the classes table.

-Pete

 

[EastCoast]

Okay, I think we're tracking. I tried that and got "Name#" in the field. Here is what I put in the properties of the combo box:

Control Source: [CourseID] FROM Classes
Row Source: SELECT [CourseID], [CourseName] FROM Courses;

What am I doing wrong?

 

[snyperx3]

the problem is with your control source

do you have the form linked to your classes table?

i was thinking thats what you had. Anyway...if so you only need to have "CourseID" as the control source.

-Pete

 

[EastCoast]

Okay, I am now able to see the numbers "1-4" as choices in the combo box (these are the 4 CourseID's I entered as dummy data). Yes, I did have the tables linked. How now, do I see the Course Name, not the course ID in that combo box since I want the data entry person to be able to choose the name of the course (they won't know what the ID represents).

 

[snyperx3]

ok

set the number of columns to 2
and set the column widths to 0";1"


-Pete

 

[EastCoast]

You rock! Thanks.