Public,
use DLookup, see vba help on DLookup.

Me.TheTextBoxName  = DLookup("[CourseName]", "qryCourse", "[CourseID] = " &
Me![CourseID]")

Assuming that you have a query called qryCourse with the CourseID and the
CourseName in it. If not, create the query.
Do not put any criteria in the criteria row of the query.

Jeanette Cunningham -- Melbourne Victoria Australia

You can't have more than one recordsource for bound fields on a form.

There are a couple of ways that you could get what you want, however, since
it seems that CourseID is in the current recordsource (if it's not, there is
no way you can show the related coursename).

One: add the table (or query) which contains the CourseID and CourseName
fields to your existing query which is the form's recordsource; join on the
CourseID field, set the join properties to "show all records from (existing
table in query) and only matching records from (new Courses table/query in
query)" -this will allow existing records to be shown if CourseID or
CourseName is missing.  Then bind your textbox to the CourseName field which
is now available in the form's recordsource query.

Two: use a dLookup expression in the textbox, including a criteria clause to
return the CourseName for the CourseID field of the current record; it will
be an expression similar to:
   =dLookup("CourseName","Courses","CourseID = " & [CourseID])
If CourseID is a text field, rather than a numeric field, you will need
delimiters:
   =dLookup("CourseName","Courses","CourseID = '" & [CourseID] & "'")
Expanded for clarity, that's:
   =dLookup("CourseName","Courses","CourseID = ' " & [CourseID] & " ' ")

HTH,

Rob