Combo box search function

[Carouser]

It's getting late and my brain is fried. On a db that I created a few years ago, I manage to create a search/look up combo box that you can par type a search of a last name, click on the drop down tab, it would give results starting from the letters you typed in alphabetical order. I cannot replicate this. What is coming up when par typing a search is the first result high lighted, but half way down a long list of last names rather than the first result of the drop down. I've replicated everything I could from the original, but something is amiss.



The Combo is based on a query with sort set to last last name and first name in ascending order plus ID. The main table has the last name and first name indexed for no duplicates plus the primary key. I have replicated exact settings in properties from original. On the original, there are no macros apart from the after update and no vb code related to the combo box. The original was created with the wizard in Access 2003, I'm using 2010, and with my limited experience, used the wizard. I looked aroun trying to find the answer, but I may not be able articulate the exact problem in a meaningful search

Any most obvious remedy would be greatly appreciated.

[John_G]

Check the Row Source property of the combo box - does it have just the name of the query, or does it have a SQL statement with a WHERE clause in it?

I have found that it is sometimes better to explicitly include the WHERE in the combo box Row Source, to force it to put the rows in the order you want; the Row Source SQL might be something like:

Select column_list from query1 order by lastname, firstname

replacing the italics with your actual names.

HTH

John

[Carouser]

I've found the issue. I had my 'limit to list' set at 16. From what I can figure out, Access will need to have as many names in the list as specified by the limit, and as it is a new db with a few sample names, the criteria of 16 names had to be met. Limiting my list to 5 has given the first available records starting from my par typing selection. If I chose the last name starting with 'W', the list will need to have a list of 5 names and the 'W" names would be on the bottom of the list. If I used a bigger name sample for testing I would not have noticed and avoided a partial melt down by focusing on this small issue.

[John_G]

No, that is not what Limit to List means. It should not even have allowed you to put 16 in as a value - It is Yes or No. Limit to List = Yes means you cannot add new values to the list by typing them in. It forces you to select one of the rows, or type in a value that matches one of the rows, which in your case is the setting you need.

John

[Carouser]

My bad, I meant to say "List Rows" that yo nominate the number of results displayed. Thank you for clarifying the finer points of the combo box properties. I rarely have to think about combo properties when using wizards as it does what I need, but now am much wiser to the array of properties and all their functions.

Thank you for your input John. And it is good to know I will never have to travel alone in my project where my expectations are exceeding my experience. But with help from board members so far, I am well on the way.

Cheers

[John_G]

We're always glad to help.

The List Rows property determines the maximum number of rows a combo box will display before it needs a scroll bar. If there are fewer than that, no problem, that's all it will show. But the List Rows value does not put any requirement on how many rows in the source table there have to be, i.e. there is no minimum.

[Carouser]

Thanks John_G, that explanation has helped clarify further.

The search combo is working like it should, but when I do a search and select the record I want, in the subform data sheet I can see records 'flashing' as it goes to the corresponding record. It seems like it scrolls to the record rather than just going directly to it. If I add a record number in the main form record selector at the bottom of the form, it goes directly to the record and no flashing. The main form Spk_ID is linked to the subform ID. I have totally redesigned the structure of my subform, so I am not sure if the problem is my main form or the subform.

[John_G]

Hi -

I have never seen that; if I had to guess, I think it might be related to the action of the combo box. If you scroll through it, then the sub form will do that. Does the subform do the flashing only after you make a selection in the combo box?

What you could try is this: unbind the combo box from any underlying table field, so that it is only a search box. Add a text box to the main form, bound to the same table field that was used for the combo.

Then, use a line or two of VBA in the combo After Update event, to move the main form to the record you want. Something like this:

'
' Go to the country number field, then find that record
'
DoCmd.GoToControl "country Number"
DoCmd.FindRecord Me![country_name].Column(0), acEntire, , acSearchAll

(taken verbatim out out of one of my own databases - I know it works)

Here, country_name is the name of my combo box, and Country Number is the field (control) that is linked to the subform. The effect is that the main form goes straight to the record identified by the combo box, and the subform is refreshed without flashing.

John

[Carouser]

It worked!!! That is brilliant John_G. No flashing in the subform.

Now the only problem is after searching and selecting a record, the focus goes to the ID text box. I have set the tab stop of the ID text box to 'No', but still goes there. I want the focus to remain on the search combo so it is ready to search all the time without having to click in the field, highlight the text already in there and then type the next search name. I tried to disable the ID text box, but I get an error that it needs to change the focus to this text box after selection in search combo. I do not require any tab stops on the main form. I have already looked around to resolve this, but nothing is working, or I don't understand the finer points of "tab'bing.

[John_G]

Hi -

After the DoCmd.FindRecord command, put something like this:

Me!subformcontrol.form.fieldname.setfocus

where fieldname is the name of the control on the subform.

Note that you use the name of the main form control containing the subform, not the name of the subform itself.

John

[Carouser]

Thanks again John. All is working without flashing. The main components are working fine, now onwards to the automation tasks, which I have many, but limited VB coding experience.

Cheers