Combo box does not expand and search as you type (1 Viewer)

[Jboymeng]

MS ACCESS 2010
AutoExpand Property Set to Yes

I have a bound combo box that has a query as a row source, The query has 3 fields called from tblMasterItems, The ItemID , ItemDesc and ItemSupplier(related to the PK of tblSupplier).

Column Count = 3
Column Width = 0";1";0"

If I do not put a criteria under ItemSupplier, the combo box behaves just fine. The problem happens when I set the criteria under ItemSupplier, the criteria being the supplier ID, the combo box no longer expands and searches as you type, but the items in the combo box have been filtered and are there.

Summary:
When the row source qry ItemSupplier Field does not have a criteria, cbo works just fine.
When the row source qry ItemSupplier has a criteria, cbo no longer expands and searches as you type, but has the filtered data showing if you hit the expand button (that arrow pointing down in the cbo)

(I am at my wits end)

Thanks for any and all help.
Jboymeng

 

[MarkK]

How do you apply this criteria? Show the SQL without criteria, the SQL with criteria, and the code that makes the change. Otherwise we are just guessing.

 

[Jboymeng]

Here are pics of what I do with the qry.

Also the row source SQL with criteria is
SELECT tblMasterItems.[Master Item ID], tblMasterItems.Description, tblMasterItems.Supplier FROM tblMasterItems WHERE (((tblMasterItems.Supplier)=45));

Row source SQL no criteria is
SELECT tblMasterItems.[Master Item ID], tblMasterItems.Description, tblMasterItems.Supplier FROM tblMasterItems;

 

[MarkK]

So you don't have code that applies the criteria based on user input? I thought the problem was related to how you were changing your RowSource, but you are just changing it in design view, right?

If so, you might have to post your DB, since I can't replicate this problem here. Maybe it has to do with the data type of Supplier, but you are saying the list is displaying correctly when you apply the criteria, so that doesn't make sense either.

I don't know, maybe someone else sees something, or post your DB if you want.

 

[Jboymeng]

The DB is still incomplete and need a lot of work and I have only learned access three weeks ago, so please forgive/Excuse the mistakes, Ugly work arounds, messy codes, redundancies and other things. hehe.

the tables haven't all been populated yet and some I'm still working on. Its just this damned cbo box type as you search thing.

The problem is in the " sfrmCodeAndPricing" Master Desc Field.
The attachment contains a txt file to a gdrive download link. Since the DB is 8mb even when compressed.

Use my login ID if you like.
ID: jboy
pass: 123

Edit:

I just realized that the link sharing was off.
https://drive.google.com/file/d/0B-1NZUijONI1VlpjUEJ0ZjlRQUk/view?usp=sharing

 

[Jboymeng]

To recreate the problem in access 2010 try this:

Make the tables:
Create tblSupplier with fields
SupplierID(Key,AutoNum), SupplierName(text)

Create tblMasterItems with fields
MSitemsID(Key,AutoNum), MasterDescription(text), Supplier(Num, relate to SupplierID)

Create tblWSitems with fields
WSitemID(key,AutoNum), InvoiceDesc(text), Qty(num), MasterDesc(Num,Relate to MSitemsID)

Popluate the tables
tblSupplier:
Supplier one
Supplier two
Supplier three

TblMasterItems

MasterDesc Supplier
Test Item 1 1
Test Item 2 2
Test Item 3 3
Test Item 4 1
Test Item 5 2
Test Item 6 3

Now we create a query
Show TblMaster items, Select all fields, put =3 on the criteria of supplier, Name that query as test(Names are dificult )

Create a datasheet from tblWSitems with all fields

double check if the MasterDesc field is a combo box, if not make it a combo box. Now Go to MasterDesc field properties and find row source, put the "test" query as the row source, save and try out the Datasheet.

Does it expand?
Does it search as you type?

If no, we have the same problem. Now, go back to row source, invoke the query builder, remove the criteria, save and go back to the data sheet. Try it out.

It expands and it searches as we type..

I'm stumped guys.( banging my head against a brick wall) (im typing coz i cant put it the smiley)

 

[Jboymeng]

Also, Please comment about the DB I made.. The problems you think I will have in the long run.. The DB will be split and there will be 5 copies of the front end, 3 will be connected to the back end thru LAN the other 2 will be remote, Connected with Hamachi. tried it in a trial database, works fine.