Using a search for records in a form based on a lookup field.

[Jigreel409]

I'm using Access 2013. I have two tables - "UniversityNames" table and a "StudentNames" table. A third table "Student_University" table is a junction table to record all the universities that a given student applies to. I have a FORM for the "Student_University" table and am using a Lookup for the student names and a lookup for the university names. I have additional fields in this junction table to record pertinent information about each record, such as scholarships granted, etc. The drop-down list for the student's name field in the form and the university name field in the form are quite large, requiring a lot of scrolling each time I wish to add a bit of information to the table via the form. I am in need of a search feature in the form to find a specific student's name or a specific university name. I have tried what is suggested, to create a search by using a combo box's third choice, "Find a record on my form based on a value I selected in my combo box." This will not work for me, I guess because the value is coming from a lookup field on the form. It works fine when I set it up on a non-lookup field. Any suggestions what I might do to make it work? Thank you.

[davegri]

mtm with subform-Large Lookups-davegri-v02.zip

Have a look at the attached.
The student table is large. When the combobox is selected, you can start typing and the combobox will auto search to your keystrokes - no need to scroll down.
The junction table for university/student includes fields for data pertaining to that particular student at that particular university.

Replaced v01 attachment with v02 which includes a combobox for the university lookup.

[Micron]

...am using a Lookup for the student names and a lookup for the university names...
...It works fine when I set it up on a non-lookup field.

Sounds like you are saying the table is where the lookup field is. If so, then unless you plan to work directly in tables (which isn't always a great idea) table lookup fields should generally be avoided. The main issue is that the value seen is of course a value from the data source, but what Access stores is the index number for that value. Possibly that value can be used when the lookup is based on another table, but when it's a multi value field, the index is hidden and you cannot get at it.

Sticking with the assumption you have a table lookup field, you may have to use the .Value property to retrieve the actual value that you see in such a table field. However, I've never tried to access that in a form control record or row source - only in a query. Sorry if I'm way off base, but "doesn't work" doesn't reveal anything of value. In this case you could mean you get the idex value (a number) or nothing at all, or maybe even an error.