Auto fill stops working

[Charles Hobbs]

I have a combo box (from a list of category items) on a form that when I start typing the first few letters should auto fill with the data that is related to that field. For instance, I start typing FO and normally the box would auto-fill with "Food" and I would hit enter and go to the next item on the form. Access has stopped doing this for some reason and so far I have not been capable of figuring out what is going on to cause it to stop working correctly.

I have gone so far as to create another field on the table that used the list from the category table and then update the form with the correct field. This worked fine for a while and then it stops. When it stops working I get an error message that says that "fo" (for food) is not on the list. If I use the drop-down list of the combo box and click on "Food" it populates the field just fine. Without the auto fill feature, populating the field is very time consuming. Especially when I know that it should work like it is supposed to.

I discovered that when it "breaks" that it does so also at the table level.

Can someone point me in the right direction to figure out what is causing this?

Help is much appreciated.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[Mickjav]

Think I found years ago that it worked better if you order the list and have you checked the Auto Expand setting?

[Charles Hobbs]

Thanks for all your help. I found an old thread that said to uncheck the SQL Server Compatable Syntax (ANSI 92) boxes. So I did. Everything works great!