How to force the Combo Box to Requery/refresh after typing some text in its control?

Issa S.
Issa S. used Ask the Experts™
on
Hi All,,,
How to force the Combo Box to Requery/refresh after typing some text in its control?

Knowing tat the query of the Combo Box is based on LIKE in  its source field.

Thanks a lot.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Try this:

Me.YourComboboxControlName.Requery

Open in new window

Author

Commented:
Thanks,
But at which event?!
I tried the After Update and didn't help!

Commented:
Re-querying a combo while it has the focus seems pointless.  The values in there should be already refreshed before you enter it.

I suggest that you refresh it when you open the form.

Could you describe the context of your form and the combo rowsource a bit more?

~Tala~
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Most Valuable Expert 2012
Top Expert 2014

Commented:
What do you mean by "refresh"? Do you mean you want the combo to "auto-complete" for you (it should already be doing that, by the way), or do you mean you want to filter the list based on what you're typing (which is still should do).

Author

Commented:
DearScott / Tusitala,,,

I may crazy here but it is a crazy requirement too.

I hope I am not burdening you if I send you the SQL of the source query below.
The source of my CB is an expression made up of 2 fields on the table. So I want to pick based on either part of the code in the first field or part of the text in the second field.

The thing is now working as follows :
1st typing into the CB,
2n  Leave the CB to any where,
3rd Come back to CB and press the drop-down arrow,  and it works and pick correctly.

As a matter of convenience to the user, it is a different behavior.

I hope this gives some clue.

SELECT [Accounts (Cust+CHO)].accno, [Accounts (Cust+CHO)].accnmin, [Accounts (Cust+CHO)].salesm, [Accounts (Cust+CHO)]![accno] & [Accounts (Cust+CHO)]![accnmin] AS Expr1
FROM [Accounts (Cust+CHO)]
GROUP BY [Accounts (Cust+CHO)].accno, [Accounts (Cust+CHO)].accnmin, [Accounts (Cust+CHO)].salesm, [Accounts (Cust+CHO)]![accno] & [Accounts (Cust+CHO)]![accnmin]
HAVING ((([Accounts (Cust+CHO)].accno)<>"") AND (([Accounts (Cust+CHO)]![accno] & [Accounts (Cust+CHO)]![accnmin]) Like "*" & [Forms]![Sanad Cash - cushist Form -Gnrl]![CmboCAccountInfo] & "*"))
ORDER BY [Accounts (Cust+CHO)].accno DESC;

Thanks a lot
Most Valuable Expert 2012
Top Expert 2014

Commented:
So I want to pick based on either part of the code in the first field or part of the text in the second field.
So you want to filter your query based on the value of selected in a combo on a form?

If so, you must refer to the Column of the combo - but I'm NOT sure you can do this directly in a stored query. You can, of course, create the query SQL and filter it in VBA.

SELECT [Accounts (Cust+CHO)].accno, [Accounts (Cust+CHO)].accnmin, [Accounts (Cust+CHO)].salesm, [Accounts (Cust+CHO)]![accno] & [Accounts (Cust+CHO)]![accnmin] AS Expr1
FROM [Accounts (Cust+CHO)]
GROUP BY [Accounts (Cust+CHO)].accno, [Accounts (Cust+CHO)].accnmin, [Accounts (Cust+CHO)].salesm, [Accounts (Cust+CHO)]![accno] & [Accounts (Cust+CHO)]![accnmin]
HAVING ((([Accounts (Cust+CHO)].accno)<>"") AND (([Accounts (Cust+CHO)]![accno] & [Accounts (Cust+CHO)]![accnmin]) Like "*" & [Forms]![Sanad Cash - cushist Form -Gnrl]![CmboCAccountInfo].Column(0) & "*"))
ORDER BY [Accounts (Cust+CHO)].accno DESC;

That would use the value found in the FIRST column (visible or not). Combo Columns are in a zero-based array.

Author

Commented:
It seems that we are nearly there. But am getting this error as below:
Error in the above Select
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
hi Issa,

I suspect that Access is getting confused by the use of ( ) and + in the name:
Accounts (Cust+CHO)

Parentheses are used to group expressions. + is an operator.  It appears that (Cust+CHO) is trying to be evaluated since it looks like a formula.
... even though it is inside [brackets]

in this part:
[Accounts (Cust+CHO)]![accno] & [Accounts (Cust+CHO)]![accnmin] AS Expr1
'AS Expr1' defines the alias, or fieldname for the new expression. It is good to change 'Expr1' to something logical.  Access assigns this to mean the first (1) expression (Expr) that doesn't have a name assigned.

It is a good idea not to use special characters* or spaces in names ... instead, fill the Description

* underscore _ is ok as long as it is not the first letter.  Same with numbers ... don't start a name with a number -- and consider why a name has a number -- is the data normalized?

What is the solution?

Rename.  You will continue to have problems with names like that.

~~~
also, it seems to me that the criteria should be in a WHERE clause, not in HAVING

in WHERE, criteria is imposed before aggregation
in HAVING, it is done after aggregation

on the grid, if criteria is in WHERE, there will be another column for criteria (in addition to the one that shows) and the Show box will not be checked.

--------------------------

as for your question, "Combo Box to Requery/refresh after typing some text in its control?"

until a record is saved, the RowSource cannot show a new or changed value.  What you could do is Requery the combo on the form AfterUpdate event.

have an awesome day,
crystal

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial