Update table based on combo box selection

[Guest]

I currently have a form where the user selects a course from a combo box.
After selecting the course, the course hours are stored in the underlying
table. I would like for additional information to be stored in the table
also but whenever I enter information into the form , the additional
information is not populating. This is the code that I am using:

Private Sub Course_Name_AfterUpdate()
Me.[Course Hours] = Me.[Course Name].Column(2)
Me.[New Hire Course] = Me.[Course Name].Column(3)
Me.[Cust Satisfaction Course] = Me.[Course Name].Column(4)
Me.[Technical/Skill Building Course] = Me.[Course Name].Column(5)
End Sub

All if the above information is coming from a Select query:

SELECT tblCourseData.[Course ID], tblCourseData.[Course Listing],
tblCourseData.[Course Hours], tblCourseData.[New Hire Course],
tblCourseData.[Cust Satisfaction Course], tblCourseData.[Technical/Skill
Building Course] FROM tblCourseData ORDER BY tblCourseData.[Course Listing];

Can anyone help me?

 

[Al Camp]

Your combo should be bound to CourseID, with
ColumnCount of 6
ColumnWidths set to 0";1";1";1";1";1" (ex. widths)

That will "store" CourseID in the bound combo field, but "display" the CourseListing
in the combo box.

Otherwise, it looks OK... if you can see the data in the columns, and each row has a
unique Course ID, it should present those values to the form on AfterUpdate.

But...
There's no need to save the "ancillary values" to a table field, like... CourseHours
or NewHireCourse etc...
All you really need to capture is the Course ID in your combo, and the other values
can be "calculated" from that.

ex. field CourseHours with ControlSource of...
=Me.[Course Name].Column(2)
will always "display" the correct value... no need to save it. All the ancillary info can
be "re-derived",
on the fly, in any subsequent query form or report.

Never save a value that can be derived from data you already have.

 

[Guest]

Thanks. I did not have my columncount set correctly. Also, thanks for the
suggestion concerning storing values. The reason for this is that the values
change depending on participant information. I appreciate it!!

Your combo should be bound to CourseID, with
ColumnCount of 6
ColumnWidths set to 0";1";1";1";1";1" (ex. widths)

That will "store" CourseID in the bound combo field, but "display" the CourseListing
in the combo box.

Otherwise, it looks OK... if you can see the data in the columns, and each row has a
unique Course ID, it should present those values to the form on AfterUpdate.

But...
There's no need to save the "ancillary values" to a table field, like... CourseHours
or NewHireCourse etc...
All you really need to capture is the Course ID in your combo, and the other values
can be "calculated" from that.

ex. field CourseHours with ControlSource of...
=Me.[Course Name].Column(2)
will always "display" the correct value... no need to save it. All the ancillary info can
be "re-derived",
on the fly, in any subsequent query form or report.

Never save a value that can be derived from data you already have.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I currently have a form where the user selects a course from a combo box.
After selecting the course, the course hours are stored in the underlying
table. I would like for additional information to be stored in the table
also but whenever I enter information into the form , the additional
information is not populating. This is the code that I am using:

Private Sub Course_Name_AfterUpdate()
Me.[Course Hours] = Me.[Course Name].Column(2)
Me.[New Hire Course] = Me.[Course Name].Column(3)
Me.[Cust Satisfaction Course] = Me.[Course Name].Column(4)
Me.[Technical/Skill Building Course] = Me.[Course Name].Column(5)
End Sub

All if the above information is coming from a Select query:

SELECT tblCourseData.[Course ID], tblCourseData.[Course Listing],
tblCourseData.[Course Hours], tblCourseData.[New Hire Course],
tblCourseData.[Cust Satisfaction Course], tblCourseData.[Technical/Skill
Building Course] FROM tblCourseData ORDER BY tblCourseData.[Course Listing];

Can anyone help me?