Combo boxes, Queries and Forms

[Joy]

Hi -


[This message was previously posted to microsoft.public.access.forms, but no
one has replied yet, so I posting in the queries NG.]


To explain, briefly, I am dealing with 4 tables:
Instructor, Course, Instructor Course Expenses,
and Instructor Course Expense Details.

The Instructor Course Expense record has the total expense for
the instructor teaching 1 course on 1 course start date.

The Instructor Course Expense Details record has expenses for each day.

Here are the keys to the tables:
------------------
Instructor -- Instructor ID

Course -- Course ID
Course Number
Course Start Date
(There is1 record for each instance of a course
- e.g., course 208 could be offered on 5 different dates)

Instructor Course Expense -- Instructor Course Expense ID
Instructor ID (FK)
Course ID (FK)
Position ID (FK)
(There is 1 record each time
an instructor is assigned to
teach a course on a start
date)

InstructorCourseExpenseDetails -- Instructor CourseExpense ID (FK)
InstructorCourseExpenseDetailsID
(1 record for each day
of the course)

One instructor can teach many courses.
One course/course start date can be taught by many instructors.

Synopsis:
-----------

The first thing that happens in the program is you use combo boxes to assign
an instructor to a course (with unique course start date) and also select
the position from a combo box. This creates a new InstructorCourseExpense
record. OK.

Then I want to find the same record, by asking the user to enter an
InstructorID and CourseID on another form.
It would be nice to display the instructors name and the course name on this
form, after the user selects from the combo boxes.

The way it works now, is, after you pick an instructor, only the
course/course start dates that he has been assigned to teach show up in
cboSelectCourse. So I hope that the search will always be successful.

Then I guess press a button or (use cboSelectCourse_AfterUpdate) to open
another form. This will be the requested InstructorCourseExpense record,
and on this form there will be a subform
where Expense Details can be filled in.

So if someone could give me directions - I would be so happy! I am sort of
part way there...but it's hard to get my head around everything Access is
capable of.

Thanks!

Joy

 

[Joy]

Hi -

To be more specific, this is the progress I have made so far.

There are 2 forms - Expense1 and Expense2. Expense1 asks for an instructor
and a course instance via combo boxes. Expense2 displays the expense sheet
for this instructor/course instance, with the daily expenses. (By 'course
instance', I mean a course number - course start date).

Expense 1 displays its records from a query. The tables used in this query
are Course Name, Course Details, Position (the instructor's position),
InstructorExpense, Instructor and Department (the instructor's department).
So this form is really based on the assignment of an instructor to a course
instance. The Expense1 form is finding the key to a unique record in
tblInstructorExpense, by selecting an Instructor and a Course instance.

When the Expense1 form opens, there are 4 records, because there are
currently 4 records in the Instructor Expense table. OK. Right now, only 1
of these records has InstructorCourseDetails records associated with it.

I select an Instructor from cboSelectInstructor. The form displays the
First Name, Last Name of the instructor I picked in cboSelect Instructor.
OK.

Then I select a course instance from cboSelectCourse. Only the course
instances which are assigned to the instructor picked in cboSelectInstructor
are can be choices in cboSelectCourse. For example, if I pick Instructor A,
then his course instances are March 2, 3, 4 whereas if I pick instructor B,
his course instances are March 8 and 9. OK.

The result of picking from cboSelectCourse is the key to the
InstuctorExpense table - that is, by picking an instructor, and then a
course instance, I get the key to a record in tblInstructorExpense. OK.

Expense 2 displays its records from a query. The tables used in this query
are Course Name, Course Details, Position, InstructorExpense,
InstructorExpenseDetails, Instructor and Department. So it is based on the
assignment of an instructor to a course instance, plus displaying the daily
expenses for this instructor/course instance, in a subform.

I have two problems at the moment.

Problem 1: I wanted it to show Course Name, Course Location, after I picked
from cboSelectCourse, but it won't let me. I suppose it is because the
result of cboSelectCourse is the key to the InstructorExpense table. So I
removed the fields Course Name, Course Location from the form. It does
display Instructor First Name, Last Name, after picking from
cboSelectInstructor, which helps.

Problem 2: when I go to the Expense2 form, 2 things can happen. If I am
asking for an Instructor Expense record that already has
InstructorExpenseDetails, then all the fields are displayed. If I am asking
for an InstructorExpense record with no InstructorExpenseDetails records,
then all the fields on the form are blank.

Do you have any idea why Expense2 form is blank? The query for the Expense2
form has criteria for the InstructorExpense key =
Forms!frmExpense1!cboSelectCourse. I really would like to be able open
Expense2 with data in it, whether or not there are InstructorExpenseDetail
records. It is possible?

It is hard to describe all this accurately in words, but if you need more
information, because I have been unclear, please ask me. I really hope to
make some progress this weekend, and I honestly don't know where else to
turn!

Thank you (so much!) for reading this. I truly do appreciate your time and
interest.

Joy