Combo Box to Select Row from other Table and to make it Autocomplete

[Marco Soares]

Hello

Thank you very much for willing to help. I'm having a problem with many Combo Boxes. It's something like this:

Imagine i have a table called "TblBook" and that one of its fields ia called AUTHORS and it's a foreign key to a table called "TblAuthor". The primary key of "TblAuthor" is an autonumerated field called Author_ID. The other field of this table is called Author_Name.

Now imagine i create a Query to Join these 2 tables by matching Author_ID with AUTHOR. Then i create a Form based on this Query, so that i can use it to add new books..

Now i create a Combo Box in this form to get a row from TblAuthor. The Combo Box's record source is the field AUTHOR and its row source is something like:
SELECT [Author].[Author_ID], [Author].[Author_Name] FROM Author.

When i select a row from the drop box of the combo box, the Combo Box control shows the Author_ID value from the row i selected. The Author_Name Control is automatically filled.

This works right, but i need to make it faster to select rows from other tables with Combo boxes. What i need to know is:

1) How can i create a combo box that stores not the Author_ID, but the Author_Name instead? An Author_ID Control of the Form should be filled automatically after i select the row.

2) How can i make this Combo Box autocomplete for each keystroke i give?

Thank you very very much for the atention and for willing to help!

Best Regards,
Marco

 

[sneuberg]

The Author_ID is the key and that should be stored; not the name. You can do that and still just see the Authors name instead of the ID. In the Format tab of the combo's properties make the Column Count equal to 2 and then in the Column Widths put something like

0";2"

that will hide the autonumber and show two inches of the authors name.

It will kind of autocomplete. If you type "a" in and then click the down arrow the list will be just the authors with names that start with "a". I wish there was a way to make it display the shrinking list as you type but I haven't found it.

 

[Marco Soares]

Thank you very much! In another forum, another guy was giving me the same solution. It worked perfectly. I will close this topic. Thank you.

Best Regards,
Marco

 

[sneuberg]

Please read http://www.excelguru.ca/content.php?184

 

[Marco Soares]

I'm really sorry. The text of your link is very clear and well written. Even suggests when (and how) it's really apropriate to cross-post. Made me feel like a stupid idiot...

I just would like to say that i haven't read this text before. Also, i could never even imagine that these people helping were volunteers. I imagined at least the most active ones were payed somehow. Also, i honestly mentioned the fact that other guy had solved the problem exactly because i felt very bad for taking your time. I sincerely apologize. My behaviour was abusive. I will behave appropriately from now on.

Thank you very much and Kind Regards,
Marco

 

[sneuberg]

Made me feel like a stupid idiot...

Please don't. The post was only to make you aware that it's best to let us know when you have posting the same question elsewhere so that we don't duplicate someone else's efforts.

 

[Marco Soares]

Yes, i understand. I will do that from now on. Thanks for worrying about me.

Best Regards,
Marco