Trying to display a related field in an unbound control

[daniel]

I have encountered a problem with a form in a database I have recently
created. I have been able to recreate the problem in a brand new db
file, so I am pretty sure it is all my fault.

Platform is Windows 2000 with Office 2000 installed.

I am quite a fluent developer in FileMaker but this is my first foray
into Access; I've been working with the product for about a week.


The issue involves displaying related data on a data entry form,
frmtClasses (based on the tClasses table), from the table tCourses. In
FileMaker I would just place an instance of the related field on the
form (layout), but I have discovered that such is not possible in
Access unless I base my form on a query - and if I base it on a query I
am not sure if I can create a new record in tClasses.

On frmtClasses, I want to choose a course upon which the class is
based. So I have set up a combo box on my [tClasses].[CourseID] field
with a lookup query which shows me the [CourseID], [CourseCode] and
[CourseName] fields from the tCourses table. This part is working fine;
I have the combo box set to display the [CourseCode] value and have set
up a text box field to verify that the [CourseID] value is being
properly stored.

Displaying the related [CourseName] value in an unbound text box
populated by an expression, however, does _not_ work and I cannot
figure out why. If I have the control source property set to:
=Me.cboCourseID.Column(2)
I receive the wonderfully helpful "#Name?" error.

With the property set to:
=DLookUp("[CourseName]","tCourses","[CourseID]='" & [CourseID] & "'")
I am shown the equally informative "#Error" text.

I found both of these examples at
http://accesstips.datamanagementsolutions.biz/lookup.htm after
searching through this newsgroup for similar situations.

Does anyone have a clue as to why the code is not working?

Thanks in advance,
Daniel.

 

[Allen Browne]

Hi Daniel

The Me keyword is for code only.
Try setting the Control Source to:
=[cboCourseID].[Column](2)
Note that this refers the 3rd column. (The first one is zero.)

If Course ID is a Number field (not a Text field), drop the extra quotes:
=DLookUp("[CourseName]","tCourses","[CourseID]=" & [CourseID])
That should work, except at a new record where the CourseID is null.
To avoid that error also, use Nz() like this:
=DLookUp("[CourseName]","tCourses","[CourseID]=" & Nz([CourseID],0))

For more details, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

Happy Accessing!

 

[unhinged]

Thanks, Allen!

I could have sworn that I tried that some days ago - but hey, it's
working now!

Thank you also for the information regarding the Me object, it's just
the sort of thing I need to know when trying out a new environment.

Regards,
Daniel.