Showing the results of an SQL query in a combo box to add records to a table.

[mrbaggins]

I've done this before, so I know it's possible, but can't for the life of me remember how I've done it.

I have 3 tables:
Student details
Activities (contains name/date/location of activities)


Registration (Which pairs ActivityID's with StudentID's)

What I've done is make an Enter Activity Details form, which has details about an activity and the students doing it. This form has has the student details in a subform (Datasheet) with Student Name/Lastname/Gender.

This lets me add students to activities by storing the relevant ID's in Registration.

The nifty part was that each cell in the datasheet was a combo box, that when clicked pulled up every student sorted by whichever column you picked. So you could choose which way to sort the data when adding, depending on if you wanted to jump to lastnames/firstnames or whatever.

It showed as a mini-datasheet, with ALL the columns in the popup though.

Now I'm trying to do this again, pretty much identically, but with Awards.

So I have the tables:
Awards
Student details
AwardRegistration

But no matter what I do, I only get one field turning up in the combobox popup. I've got the record source for each field being the SQL query identical to the Activities form, and I've been mucking around for ages, but can't get it to work the same way as Activities Entry does.

What stupid little gimmick am I missing?

[mrbaggins]

No one knows what I'm talking about? Would a screen shot help?