Can't access subdatasheets... keep getting "expression is typed incorrectly..." error

[nightwolf84]

Every time I try to click the '+' button in my parent table to view the corresponding subdatasheet(s) for each record I get this error:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

After reading several threads, I thought maybe I was making the same general mistake in that the fields of my primary and foreign keys were not the same. This, however, is not true, yet I continue to get this error message.

Here's what I have:

Student Table:
StudentID (autonumber) (PK)
Last Name (text)
First Name (text)

ClassResult Table:
ClassResultsID (autonumber) (PK)


StudentID (number) (FK)
Class (text look-up)
Class Date (date/time)
Result (text look-up)

Each student (StudentID) can have more than one class record (ClassResultsID). My PK is autonumber, and the FK is number... so why do I continue to get this error???
I've even closed out of the program and restarted several times to no avail. ANY help would be greatly appreciated!

[June7]

Are you sure the link in the Relationship builder is set to the correct fields?

Want to provide db for analysis? Follow instructions at bottom of my post.

[nightwolf84]

I'm pretty sure. I have StudentID from the Student Table (PK) tied to StudentID in the ClassResult Table (FK). I checked marked the reference integrity box prior to creating the link and it shows a 1 to Many relationship. I'll attach my db in the morning when I'm back at work.

[nightwolf84]

Are you sure the link in the Relationship builder is set to the correct fields?

Want to provide db for analysis? Follow instructions at bottom of my post.

Here's my db. Thanks for the assist.

[June7]

Setting relationships with Classes and Result (rename as Results) error because the datatype of the PK/FK fields mismatch. If you want to save the number IDs then the FK fields must be number type to agree with the PK autonumber type. Personally, these attributes are so short, I would just save the text values as described below. I avoid Lookups as much as possible. http://access.mvps.org/access/lookupfields.htm

The Lookups would be unnecessary if the text values are saved. Change properties:
RowSource: SELECT [Result] FROM Result ORDER BY [Result];
ColumnCount: 1
ColumnWidths: 1"

Change the PK in Result to the Result field.

Do same for the Classes table.

So either change the FK fields to number or make above changes and replace the number values with the text values.

The Students table appears to be corrupted. I created a new table and reset Relationship and it works.

StudentEntryForm has controls with invalid ControlSource.

[nightwolf84]

Thanks!!!