Automating combo boxes (Access 2K, SP3)

Topic

New Reply

I know this has probably been asked a hundred times or more, so here’s one more. I’ve seen combo boxes where one could start typing in text and the drop-down list would open at the beginning of the first letter the user typed in or the like. For example, if I have a combo box with a list of clients and I type in “T” or “Tr” for Travelers, the dropdown opens at the “T’s” thus saving all the scrolling. Even some help on what to search for would help. I’ve found I’ll be retired or dead if I start going through the list of messages using the term “Combo box”.

Thanks for any help,

Reply | Quote

Replies

Not sure if this is what you’re after. You can make the list drop down when the user activates the combo box. Let’s say that it is named cboClients.
– Open the form in design view.
– Select cboClients.
– Activate the Event tab of the Properties window.
– Click in the On Enter event.
– Select [Event Procedure] in the dropdown list.
– Click the … to the right of the dropdown arrow.
– Make the code look like this:

Private Sub cboClients_Enter()
Me.cboClients.Dropdown
End Sub

– Switch back to Access.
– Close and save the form.
– Test the form.

If that is not what you intended, post back.

Reply | Quote

Thanks, Hans. That is not exactly what I was looking for although I’ll add that to my “Code snippets” folder for future reference. What I’m looking for is sort of a look up function/action. As it is now, the user has to scroll through something like 1,300 alphabetically listed names to get down to the names that start with an “S” (or any other letter). When dragging the scroll bar down toward the bottom of the list, it pops back up about the middle of the list rather than staying down at the bottom like one would expect. At least on my Access form it does. I read somewhere this is due to the combo box having to scroll through a lot of records and can’t keep up with the scroll bar being moved by the mouse.

So, what I’d like is for the user to just type, say, “S” (or any letter of the alphabet) and then or when the dropdown arrow is clicked the first entry showing is the first “S” entry. My aim is to speed up using the combo box and minimize the scrolling using the scroll bar. It is sort of like the Help section in MS apps, when you type in a key word in the dialogue box and the window below it immediately goes to the first match.

Am I making myself clear?

Thanks.

Reply | Quote

The feature you want is the default behavior of combo boxes. Typing a few characters into a combo box should automatically select the first item that begins with those characters.

A combo box with 1,300 entries is not very efficient or user-friendly. It might be better two use two linked combo boxes. The first one would display categories, for example the first letter. When the user selects an entry in the first combo box, code restricts the second combo box to display only items corresponding to the selected entry. See ACC2000: How to Synchronize Two Combo Boxes on a Form.

Reply | Quote

I’ve looked at the sample in the last post and have browsed the web looking for other examples. Found an example on the Access MVP site that seemed like it might do what I wanted. However, I seem to be having trouble modifying the SQL string to get it to do what I want. From your earlier suggestion, I decided the best approach would have the first combo box just select a letter of the alphabet. From there, add the “*” character to the chosen letter to filter the listings in the second combo box. Works like a champ when it is hard coded (Like “T*”), but trying to change that to a selected letter and adding the “*” is giving me problems. I’m open to suggestions, but I’ll keep working with it and poking around to see if I can stumble on the answer.

Thanks for your guidance,

Reply | Quote

There are basically two different ways you can synchronize combo boxes.

One method is to create a query of the form

SELECT LastName FROM tblSomething WHERE LastName Like Forms!frmTest!cboFirstLetter & “*”

with the appropriate names substituted, of course; cboFirstLetter is the combo box displaying the first letters. Set the Row Source property of the second combo box to the name of the query. You must requery the second combo box in the After Update event of the first one:

Private Sub cboFirstLetter_AfterUpdate()
Me.cboLastNames.Requery
End Sub

The other method is to modify the Row Source of the second combo box in code:

Private Sub cboFirstLetter_AfterUpdate()
Me.cboLastNames.RowSource = _
“SELECT LastName FROM tblSomething WHERE LastName Like ” & _
Chr(34) & Me.cboFirstLetter & “*” & Chr(34)
End Sub

Of course, these are only rough indications; the row source of your combo box may be more complicated than that.

Reply | Quote

Thanks for your hel p, Hans. In the past, the combo boxes I set up in applications always did what I wanted them to do so I’ve never had to dig that deep into making them do something other than what the wizard sets up. In this instance, I inherited an application and was asked to add some stuff…like adding contact names to companies. That’s where the combo work came in. I didn’t realize it but I was copying the same lookup routine in this application that I used in another, personal application. In both, I essentially look for the Contact who is linked to the company rather than looking for the company. The combo boxes are actually looking at the ContactID. I also think that is why the Autoexpand is not doing what it is supposed to. I took the Customer ID field out of the SQL statement in the RowSource and the combo box did just what it is supposed to do. Perhaps I’ll have to take another look at the whole setup and make some changes in how I’m looking up stuff. You’ve given me a good start and I’ve found some good stuff here on the lounge (after I found a better search term). With a little more work, I think I’ll get it worked out. Thanks again.

Reply | Quote

Good luck, and don’t hesitate to come back if you have more questions.

Reply | Quote

Good luck, and don’t hesitate to come back if you have more questions.

Reply | Quote

Thanks for your hel p, Hans. In the past, the combo boxes I set up in applications always did what I wanted them to do so I’ve never had to dig that deep into making them do something other than what the wizard sets up. In this instance, I inherited an application and was asked to add some stuff…like adding contact names to companies. That’s where the combo work came in. I didn’t realize it but I was copying the same lookup routine in this application that I used in another, personal application. In both, I essentially look for the Contact who is linked to the company rather than looking for the company. The combo boxes are actually looking at the ContactID. I also think that is why the Autoexpand is not doing what it is supposed to. I took the Customer ID field out of the SQL statement in the RowSource and the combo box did just what it is supposed to do. Perhaps I’ll have to take another look at the whole setup and make some changes in how I’m looking up stuff. You’ve given me a good start and I’ve found some good stuff here on the lounge (after I found a better search term). With a little more work, I think I’ll get it worked out. Thanks again.

Reply | Quote

There are basically two different ways you can synchronize combo boxes.

One method is to create a query of the form

SELECT LastName FROM tblSomething WHERE LastName Like Forms!frmTest!cboFirstLetter & “*”

with the appropriate names substituted, of course; cboFirstLetter is the combo box displaying the first letters. Set the Row Source property of the second combo box to the name of the query. You must requery the second combo box in the After Update event of the first one:

Private Sub cboFirstLetter_AfterUpdate()
Me.cboLastNames.Requery
End Sub

The other method is to modify the Row Source of the second combo box in code:

Private Sub cboFirstLetter_AfterUpdate()
Me.cboLastNames.RowSource = _
“SELECT LastName FROM tblSomething WHERE LastName Like ” & _
Chr(34) & Me.cboFirstLetter & “*” & Chr(34)
End Sub

Of course, these are only rough indications; the row source of your combo box may be more complicated than that.

Reply | Quote

I’ve looked at the sample in the last post and have browsed the web looking for other examples. Found an example on the Access MVP site that seemed like it might do what I wanted. However, I seem to be having trouble modifying the SQL string to get it to do what I want. From your earlier suggestion, I decided the best approach would have the first combo box just select a letter of the alphabet. From there, add the “*” character to the chosen letter to filter the listings in the second combo box. Works like a champ when it is hard coded (Like “T*”), but trying to change that to a selected letter and adding the “*” is giving me problems. I’m open to suggestions, but I’ll keep working with it and poking around to see if I can stumble on the answer.

Thanks for your guidance,

Reply | Quote

The feature you want is the default behavior of combo boxes. Typing a few characters into a combo box should automatically select the first item that begins with those characters.

A combo box with 1,300 entries is not very efficient or user-friendly. It might be better two use two linked combo boxes. The first one would display categories, for example the first letter. When the user selects an entry in the first combo box, code restricts the second combo box to display only items corresponding to the selected entry. See ACC2000: How to Synchronize Two Combo Boxes on a Form.

Reply | Quote

Thanks, Hans. That is not exactly what I was looking for although I’ll add that to my “Code snippets” folder for future reference. What I’m looking for is sort of a look up function/action. As it is now, the user has to scroll through something like 1,300 alphabetically listed names to get down to the names that start with an “S” (or any other letter). When dragging the scroll bar down toward the bottom of the list, it pops back up about the middle of the list rather than staying down at the bottom like one would expect. At least on my Access form it does. I read somewhere this is due to the combo box having to scroll through a lot of records and can’t keep up with the scroll bar being moved by the mouse.

So, what I’d like is for the user to just type, say, “S” (or any letter of the alphabet) and then or when the dropdown arrow is clicked the first entry showing is the first “S” entry. My aim is to speed up using the combo box and minimize the scrolling using the scroll bar. It is sort of like the Help section in MS apps, when you type in a key word in the dialogue box and the window below it immediately goes to the first match.

Am I making myself clear?

Thanks.

Reply | Quote

Not sure if this is what you’re after. You can make the list drop down when the user activates the combo box. Let’s say that it is named cboClients.
– Open the form in design view.
– Select cboClients.
– Activate the Event tab of the Properties window.
– Click in the On Enter event.
– Select [Event Procedure] in the dropdown list.
– Click the … to the right of the dropdown arrow.
– Make the code look like this:

Private Sub cboClients_Enter()
Me.cboClients.Dropdown
End Sub

– Switch back to Access.
– Close and save the form.
– Test the form.

If that is not what you intended, post back.

Reply | Quote

Doesn’t the Autoexpand property do what you want?

Quote from Help:- “You can use the AutoExpand property to specify whether Microsoft Access automatically fills the text box portion of a combo box with a value from the combo box list that matches the characters you enter as you type in the combo box.”

Make sure that Autoexpand is set to Yes.

Just a thought.

Reply | Quote

I have Autoexpand set to yes and it is not doing what it is supposed to. Any ideas why that might not be working as it is supposed to?

Thanks for the help

Reply | Quote

There is an MSKB article for Access 2002/2003 about this, I don’t know if it applies to your situation: The AutoExpand property may not work when ANSI-92 syntax is enabled and the Combo Box RowSource does not use DISTINCT values.

Reply | Quote

Thank you so much! This has been driving me crazy, and this is exactly the reason. Works like a charm now. Thanks again.

Reply | Quote

There is an MSKB article for Access 2002/2003 about this, I don’t know if it applies to your situation: The AutoExpand property may not work when ANSI-92 syntax is enabled and the Combo Box RowSource does not use DISTINCT values.

Reply | Quote

I have Autoexpand set to yes and it is not doing what it is supposed to. Any ideas why that might not be working as it is supposed to?

Thanks for the help

Reply | Quote

Steve, thanks for the input. After some more checking, I found out why the combo box was not jumping to the company like I expected and like it is supposed to do. Without repeating myself, see my response to Hans, post 415120. Anyhow, with a bit more work and re-evaluation, I think I’m back on track.

Thanks again,

Reply | Quote

Steve, thanks for the input. After some more checking, I found out why the combo box was not jumping to the company like I expected and like it is supposed to do. Without repeating myself, see my response to Hans, post 415120. Anyhow, with a bit more work and re-evaluation, I think I’m back on track.

Thanks again,

Reply | Quote

Doesn’t the Autoexpand property do what you want?

Quote from Help:- “You can use the AutoExpand property to specify whether Microsoft Access automatically fills the text box portion of a combo box with a value from the combo box list that matches the characters you enter as you type in the combo box.”

Make sure that Autoexpand is set to Yes.

Just a thought.

Reply | Quote