Storing and calculating data using a combo box

[Guest]

I am having the most difficult time calculating values obtained from a combo
box. The field that I am trying to calculate is based on a column in a combo
box selection. I can update/calculate the information on a form or report
but the values that are calculated will not store in the underlying table.
Here is an example:

The combo box uses the following Row Source:
SELECT tblCourseData.[Course ID], tblCourseData.[Course Listing],
tblCourseData.[Course Hours] FROM tblCourseData ORDER BY
tblCourseData.[Course Listing];

The text box used to lookup the Course Hours uses the following Control
Source:
=[Course Name].column(2)

I can make calculations in the form based on the Course Hours text box but
cannot save this data in the 'tblCourseData' table or use the data in a
report to calculate a Sum of several records. Can anyone help?

 

[Guest]

I assume this is the same problem as that in your other shorter post to which
I replied.

The first question here is whether you should store the Course Hours value
looked up from the tblCourseData table or not. This depends on whether it
can change over time. If the Course Hours value will remain the same for the
Course ID then you should not store Course Hours in another table which
references the tblCourseData table as it can always be obtained in the way
you are already doing. As I said in my other reply join the tables and
return the Course Hours column in the query so you can Sum it in a report.

If on the other hand the Course Hours values in tblCouseData can change over
time, but in another table which references tblCourseData you want to keep
the value in use the time the row in the other table was created then you
should store it in that table. You'll find an analogous situation with
UnitPrice values in the sample Northwind database where these are stored in
the Order Details table as well as in the Products table.

In the latter scenario you should have a control in your form bound to the
Course Hours column in the referencing table. In the AfterUpdate event
procedure of the Course Name combo box assign a value to the Course Hours
control with:

Me.[Course Hours] = Me.[Course Name].Column(2)

Ken Sheridan
Stafford, England