How to display combo box drop down

thandel
thandel used Ask the Experts™
on
I have a form that a user enters a zip code and the form can then lookup cities... some cities share the same zip code (technically the same city)  In the past I would look up the zip and if more than 1 using send keys I would display the drop down in the combo box showing just the options for that zip:

Me.cbZip.RowSource = "SELECT tZip.Zip, tZip.City, tZip.State FROM tZip WHERE (((tZip.Zip)=[Forms]!        Me.cbZip.RowSource = "SELECT tZip.Zip, tZip.City, tZip.State FROM tZip WHERE (((tZip.Zip)=[Forms]![FEntry]![cbzip])) ORDER BY tZip.City, tZip.Zip"

SendKeys "{TAB}", True 'move out of combo
SendKeys "+{TAB}", True 'move back into combo
SendKeys "(%{DOWN})", False 'display new findings


However I don't think Send keys is support  any more.... any way to make this work with the same behavior as with the send keys?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
Me.cbZip.DropDown

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
Correction
Probably you will need
Me.cbZip.SetFocus
Me.cbZip.DropDown

Open in new window

Author

Commented:
Does not work in the after update, it displays it during the after update but as soon as the sub is done its not displayed.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

John TsioumprisSoftware & Systems Engineer

Commented:
Maybe you need a requery to refeed the combo after you set the filtered RowSource
Me.cbZip.Requery

Open in new window

Paul MacDonaldDirector, Information Systems

Commented:
Shouldn't a combobox do this all by itself?  What version of Access are you using?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Just to be clear, ....based on John's suggestion, ...your code should look like this:

Me.cbZip.RowSource = "SELECT tZip.Zip, tZip.City, tZip.State FROM tZip WHERE (((tZip.Zip)=[Forms]![FEntry]![cbzip])) ORDER BY tZip.City, tZip.Zip"
Me.cbZip.Requery
Me.cbZip.SetFocus
Me.cbZip.DropDown

Open in new window


...(No sendkeys needed)
Try it like this,....then get back to him
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
and FYI, sendkeys is still supported.   However SendKeys is always problematic in that the keys are sent to whatever window is currently active.

If your using clicks somewhere else when you issue a sendkeys, they will go to that window, not Access.   Thus the use of sendkeys can be unpredictable.

Avoid sendkeys at all costs.   Should be the very last way you attempt to do something and only if there absolutely is no other way.

Jim.

Author

Commented:
No dice, just doesn't display the found records if I go into the combo then it does.

Been using the same code for 10 years without issue (send keys) now it fails with Win 10.  If you can tell me why it not working then I can correct and reuse send keys for another 10 years  :)

Error with send keys is "permission denied"

If I tab out of the field, then  tab back in and press ALT - DOWN ARROW I get exactly what  I want which is what I was using send keys for.
Commented:
Found a solution using send keys... seem that the permission error is from Win 10 ACS.  But here is a work around

    Dim WshShell As Object
    Set WshShell = CreateObject("wscript.shell")
   
    WshShell.SendKeys "+{TAB}", True
    WshShell.SendKeys "(%{DOWN})", False
    Set WshShell = Nothing
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I'm confused, why would you want to reset the rowsource of a combo based on the current selection in that combo.  Normally you would use cascading combos, where you select the zip code from one combo, reset the rowsource property of another combo (cbo_City) and then set the focus to cbo_City and drop it down.

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