Combo box auto expand problem when unbound to lookup field
Let's say I have a table called Persons that consists of two columns:
Furthermore, let us assume that PersonID is used as a foreign key in other tables. Doubtless this is familiar to all here.
Now I have a form that I want the user to enter a Person and be bound to those defined in the Persons table. I use a combo box, populate it with a nice query that looks like this:
Now I set my combo box to be "bound" to PersonID, but I only show PersonName in the dropdown, and I set the combo box to be limited to the list values. Again, nothing earthshaking here, we all do this all the time.
The problem is this: I want the field to autocomplete on PersonName. But because it is bound to PersonID (the number), not only will it NOT autocomplete, but if I try to enter "Mary" in the field I get the error that "The text you entered is not an item in the list."
1. Is it possible to autocomplete on the unbound "lookup" field?
2. If not, should I be pursuing a VBA solution on an unbound field, keyed off of events? I roughly know how I would go about it, but obviously I'm hoping that it doesn't require that level of work. <--- Lazy
Please believe me when I tell you that I searched the forums a great deal in trying to find this answer. I tried searching on autocomplete, combo box, and lookup in various combinations with no success. Yet the problem seems as if it would be so common, I can't believe that an answer has not been previously discussed...many times over.
Thanks, all.
Ed
Let's say I have a table called Persons that consists of two columns:
Code:
PersonID PersonName
-------- ------------
1 John
2 Mary
...
399 Ray
400 Sally
Furthermore, let us assume that PersonID is used as a foreign key in other tables. Doubtless this is familiar to all here.
Now I have a form that I want the user to enter a Person and be bound to those defined in the Persons table. I use a combo box, populate it with a nice query that looks like this:
Code:
SELECT PersonID, PersonName
FROM Persons
ORDER BY PersonName;
Now I set my combo box to be "bound" to PersonID, but I only show PersonName in the dropdown, and I set the combo box to be limited to the list values. Again, nothing earthshaking here, we all do this all the time.
The problem is this: I want the field to autocomplete on PersonName. But because it is bound to PersonID (the number), not only will it NOT autocomplete, but if I try to enter "Mary" in the field I get the error that "The text you entered is not an item in the list."

1. Is it possible to autocomplete on the unbound "lookup" field?
2. If not, should I be pursuing a VBA solution on an unbound field, keyed off of events? I roughly know how I would go about it, but obviously I'm hoping that it doesn't require that level of work. <--- Lazy

Please believe me when I tell you that I searched the forums a great deal in trying to find this answer. I tried searching on autocomplete, combo box, and lookup in various combinations with no success. Yet the problem seems as if it would be so common, I can't believe that an answer has not been previously discussed...many times over.

Thanks, all.
Ed
Last edited: