Combo box autocomplete problem when unbound to lookup field

edmiller

N00b Extraordinaire!
Local time
Today, 11:34
Joined
Nov 10, 2006
Messages
17
Combo box auto expand problem when unbound to lookup field

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." :confused:

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 :p

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:
The width of the bound column is zero? And the column count is 2?
 
Indeed, you are exactly correct (bound column width=0", column count=2). As a first stab at a solution, I tried making the PersonName field the first field returned by the query (and changed the column widths and bound column properties appropriately), alas that did not work.

Ed
 
Can you post a sample, because that should work. The column width should look like:

0";1.5"

I've got similar combos and the autocomplete works fine.
 
Paul, I'll have to "sanitize" the data before publishing the database, but I am more than willing to do so. My next reply may be a bit later in the day. I'm grateful for your help and as I suspected when I searched and found nothing, something atypical is going on here.
 
Maybe I'm missing something here, but why does it matter if the field is bound or not? The autocomplete (autofill) action could be based on a simple VBA construct like below ...

Me.LastName = Me.CboBaseData.Column(1)
Me.FirstName = Me.CboBaseData.Column(2)
Me.BdAge = Me.CboBaseData.Column(3)

and so on ...
 
I think you're missing something. :D
 
I think you're missing something. :D

I don't see why?

I use the same method to autofill a form using a dropdown box. Only one field is bound - yet I can autofill any or all the fields in the record based on the query that calls the combo by referencing the correct column.

I would really appreciate your thoughts as we all learn by inches. :)
 
I don't believe the OP is trying to have other controls filled out based on the selection made, as your code would do:

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."

I believe he means the auto expand feature, not autocomplete, which perhaps sent you down the wrong road. Or perhaps I'm all screwed up, which would not be the first time. Not even the first time today. :p
 
I don't believe the OP is trying to have other controls filled out based on the selection made, as your code would do:

I believe he means the auto expand feature, not autocomplete, which perhaps sent you down the wrong road. Or perhaps I'm all screwed up, which would not be the first time. Not even the first time today. :p

Ahhh, that makes a lot more sense. I was certainly going down the wrong road if that is what he meant. Maybe he'll log back on and clarify.
 
I'm back from lunch. :) Okay, as I "learn by inches" also it seems that I have misused a term, "autocomplete." My previous experience would take this word to mean "the behavior of presenting the user with various endings to entries they start, based on previous completed entries". But in Access parlance, apparently that is called "autoexpansion" of an entry.

Which makes me wonder if I had searched the forums on auto expand instead of autocomplete would I have seen relevant results?

Let me quickly finish sanitizing the database and I'll post it here.

Ed
 
I'll go to the Help right now, but to ensure that there is no "hidden" sensitive data that I think I deleted but is somehow embedded in the database, are there any steps I should take? I've finished editing the database.

Ed
 
You could import just the table and form into a blank db and post that. Then you just have to make sure there's no sensitive data in the table.

By the way, where are you in CA? I was raised in Sacramento and now live in Reno.
 
Yep, no sensitive data in the tables, that I'm sure of. I went under Tools...Options to the General tab and there's a "Remove personal information from this file on save" option, I used that.

I'm working in Livermore, CA but I live up closer to Walnut Creek in a little town called Clayton. :)

Here's the db. I do hope it is instructive of my problem. Look at four fields on the form I autolaunch: Staff Assigned, Review Status, Primary Contact and Secondary Contact. None autocomple....er, autoexpand, and if you attempt to type in a valid value that you see in the dropdown, it will tell you it's an invalid value. I'm super-confused about this. Seems it should work...
 
Last edited:
Ehh, seems to work fine for me. If I type an "A" into the status combo, it shows "Active", and I can hit tab or enter at that point and "Active" fills in the combo, as expected (and I've only typed an "A"). The others worked in a similar way. Am I doing something different?

I'm familiar with the area. I went to St. Mary's near Walnut Creek. Played golf down there a few times too.
 
Nice, St. Mary's is a beautiful area. Clayton's got it's own nice links, too.

Uh, Paul, I just tried again unsuccessfully. I went to status combo, cleared the contents, and typed Act...nothing, no auto expansion. Also, grrr...when I type Active it tells me "that ain't valid, son." Or something like that. :P

So it sounds like it's either an invididual Access configuration issue, or an application corruption issue.

I'm running Access 2003 on XP. You?

Ed
 
I've got 2000 here (OS=XP Pro). I can test on 2003 later, but I'd guess it's more of a corruption problem or something. I can't think of anything in the Access setup or configuration that would cause this.

Here's a test db I just threw together that works for me. See if it works for you.
 

Attachments

I don't know whether to be excited or distraught that your example works for me. :)

However, it does inspire me to copy this to a colleague's machine and see if the behavior is different with the same version of Access. I will report back tomorrow on my findings.

Right now, I have to head out to join a friend --- we're BARTing into SF to hear a talk on .NET 3.0's new Windows Communication Foundation (WCF).

Thanks for looking at this, Paul. More info tomorrow.
 
I don't know whether to be excited or distraught that your example works for me. :)

However, it does inspire me to copy this to a colleague's machine and see if the behavior is different with the same version of Access. I will report back tomorrow on my findings.

Right now, I have to head out to join a friend --- we're BARTing into SF to hear a talk on .NET 3.0's new Windows Communication Foundation (WCF).

Thanks for looking at this, Paul. More info tomorrow.

I had the same problem with the db. However, I have no problem with Paul's example.
 
Yeah, I'm still trying to sort out the odd behavior. I am almost done with a small sample db to mimic the box. I feel it's important to duplicate the basic underlying queries without carrying over the VBA and other baggage from the original. If that works, then I know my database has internal issues, and it's not my MS Access installation.

I should be able to report in the next 20 minutes or so.

Ed
 

Users who are viewing this thread

Back
Top Bottom