Question Text box with drop down list suggestions as you type

[DanJames]

Hi, I have a database with one table full of client names, another with staff names ect.. I have made a text box, and would like to be able to type in a client or cleaner name and have a drop down list appear with the names available. Then I would like to be able to click on one of the names available on the drop down list and get it to take me to another form showing the information of the selected person. (It could take the information from the other fields next to the person on the table.) Really its like the facebook search where you can search friends. Please help as this would really improve my database. Thanks in advance, Dan.

 

[Scooterbug]

What you are looking for is a Combo Box, not a text box.

 

[DanJames]

Yeah, I get that. But when I use a combo-box the drop down doesnt appear with the list of selections, it just auto-completes your text for you. Also you cannot select a name and it take you to a new form with there details. Thanks, Dan.

 

[Scooterbug]

If the combo box is setup right, then indeed you can use the down arrow to display data from the row source of the combo box. You can, as well, use the selection from the combo box to look up data from another table.

If you want, post your database (minus any sensitive data if you wish). Make sure if you are using Access 2007 that you save it as an MDB, as you will get more responses since some people dont have 2007.

 

[DanJames]

I'm just wondering if you could make a simple access program, with a drop down list and make up a few names and publish it on here. I would publish mine but I'm on another computer. You are a great help thanks, Dan.

 

[Scooterbug]

Here ya go. Simple form with a combo box that display the person's full name. The query behind the form uses a criteria for the PersonID, which is the bound column of the combo box. Once the selection is choosen, the form is requeried so that the correct information is displayed.

 

[DanJames]

Yeah, thats great. All I'd like now is that when you type the list will automatically appear without having to click on the arrow. This is because I would like to be able to see every client as they have same names. Is there a way when you enter a value or even when you click on the box, the drop down list can appear. Thanks alot, Dan.

 

[Scooterbug]

You can put this in the On Focus event for the Combo box:

Me.Combo3.Dropdown

where Combo3 is the name of the control. That will cause the list to show up.

 

[DanJames]

Now, is it possible to have a list of names from not just one table but also another table (2 tables). EG: ClientDetails and StaffDetails names. I think this can be done using a query? Can someone use the file above but change it so its 2 tables. Thanks alot in advance, this is all part of a massive database that I need to make for a company, and I may also need help in the future. Heres the link to the combobox already:http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=28724&d=1252003871 Thanks, Dan.

 

[Scooterbug]

Are the two tables related? (is there a relationship between them - PrimaryKey/ForeignKey). I'm guessing not, but hey, you never know.

If not, you have several options. One is to make a Union Query. Second is to use cascading combo boxes. I'm gonna wager that the cascading would suit you better. Search on the forums here for it, there are plenty of posts. But, in a nutshell...

You will have one combo box with two choices - Staff or Client. Once that choice is picked, then you can tell the combo box with the names what the row source is going to be.

 

[DanJames]

If you could send me another form with the combobox that would work with client and staff names and send it back to me as it has gone quite wrong? I would be very thankfull if this is possible? Name the Combobox "Combo3" and add it to Form1. The tables are "staffdetails" and "clientdetails." Could you also add the text boxes with the persons info to come up when a name is selected. I know this is alot to ask but i really need help? Thanks Dan.

 

[Scooterbug]

You didn't answer my first question. It's an important question...because if you need to store the ID for something, then a Union query isn't the best solution.

I dont mind helping...but it's just that...help. I get the feeling that you just want the answers without even trying to understand how it works. You are the one who ppl will go to when they have questions or want a modification. If you cant understand what should be a fundamental concept (at least in my eyes it is) then how are you going to fix any issues that come up?

I can give you an example of a cascading combo box...if that is what you truely need. But a little more info would insure that the example is correct.

 

[DanJames]

Well, I need an example of the cascading combobox but also the details coming up as The first file you sent me doesnt seem to be working. Also, I do want to know how to do the things myself but I manage these sort of things better if I can have an example and look at how the codes and work is done and changed to my (failed) trys. Thanks alot Scooterbug, if you could send this example it would be great. Dan.

 

[Scooterbug]

Well, since you never answered my questions...I had to guess on a few things. Here is an example of cascading combo boxes. If you look at the code, the row source for the second combo box is determined by the selection of "Staff" or "Client" in the first combo box. The record source for the form (Which displays all the data) is also dependant on the first combo box.

And yes, when you first open the form the text boxes display #Name. That's because the controls are bound to fields that dont exsist until either client or staff is choosen. Once you select a particular individual, then the data associated with that person will be displayed. You could set the default record source to either one of the queries if you wanted to make them go away.

Hope it helps.