Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Collapsing a combo box in VBA

    As always, unsure whether to post this under Access or VBA, but here goes.

    The VBA code I have attached to the NotInList event of a combo box does all the right things except collapse the combo box on completion. The code I'm using is as follows (I have condensed the message for easy reading):

    MsgBox "The text you have entered is not in the list. Please check the spelling or create a new item.", vbOKOnly + VBInformation, "Not Found"
    Me!cmbIssue.undo
    Response = acDataErrContinue

    This results in the previously-displayed entry being re-selected, but the combo box remains dropped down, regardless of the sequence of the three lines of code. I would like to have the dropdown collapse at the end of the subroutine. I have tried setting the focus to another field, which works, but I want the combo box to remain selected. If I set the focus to another field, then back to the combo box, it is dropped down again. Yet, if my OnCurrent code sets the focus to the combo box as I move from record to record, the dropdown isn't activated and the combo box remains collapsed.

    I know there is a Dropdown method to drop down the combo list, but cannot find a means of reversing this process.

    Any ideas?

    Thanks in anticipation...

    AliC

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,483
    Thanks
    3
    Thanked 41 Times in 41 Posts
    Hi Ali,
    What version of Access are you using? I presume you have the Auto-Expand property turned on so the combo box fills in as soon as you have typed a matching entry - turning that feature off may solve your issue, but I don't think you want that behavior either. I think that behavior is actually by design, based on the concept that you want to keep the list displayed just in case the user made a typo. This seems to be a rather common question where the usual answer is to move to another control. You might try actually invoking the OnCurrent event in the Not In List code and see if that keeps the combo collapsed after moving focus to another control.
    Wendell

  4. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Hi Wendell -

    I'm using Access 2010, although with the 2007 file format for backward compatibility.

    Yes, Auto-Expand is turned on, and deliberately so.

    I do understand why I might want the combo box to behave as it does (in leaving the field dropped down); it's just that, as there is the DropDown method that could be used if required, it's a pity the default position isn't just to collapse the box. As mentioned, moving to another control does work, until I move back to the combo box; and I do want that field to be active at the end of the code - but moving back to it drops it down again, even though it doesn't when the OnCurrent event positions me to the same field.

    Now I feel really stupid, because I'm not sure how you're suggesting I invoke the OnCurrent event in the code - I'd certainly be happy to give it a go.

    Cheers
    AliC

  5. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,483
    Thanks
    3
    Thanked 41 Times in 41 Posts
    Well, it may be a long-shot since you are messing with the basics of Access, but the idea would involve doing a SetFocus to another control - possibly hidden - and in the GotFocus event in that control to execute an expression Me.OnCurrent to force the On Current event to fire for the form and move the focus back to the combo and hopefully leave the combo box collapsed.
    Wendell

  6. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Hi Wendell -

    I'm sorry - I feel as though my stupidity level is going through the roof here, but I'm still having trouble getting a handle on what you are suggesting regarding OnCurrent.

    I can easily set the focus to another control and write code for the GotFocus event for that control. But just including the line Me.OnCurrent in the code for that event is meaningless because OnCurrent is a property, and the subroutine is looking for more information on the line - assignment of the property to a variable or such like.

    For the sake of doing something, I just wrote the line Debug.Print Me.OnCurrent, which at least stopped it throwing up an error message. I couldn't set the focus back to the original control from within the same GotFocus event, because Access is too smart (or too stupid?) and gave me an error message saying that it couldn't set the focus to the interim control. Putting the SetFocus command back in the subroutine of the original control gave me no appreciable result (ie the box was left dropped down).

    If I have really proven my stupidity by not understanding what you want me to do to "...execute an expression Me.OnCurrent..." then I would be glad of any clarification.

    Otherwise, I think you're probably right - I'm messing with something that Access fundamentally doesn't want to do, and I should just settle for the status quo.

    Either way, I really appreciate your attempts to help!

    Cheers

    AlisonC

  7. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,533
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Any chance of a compacted zipped database with just the minimum in it.

  8. #7
    New Lounger
    Join Date
    Dec 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    While I do not always advocate this. The quickest easiest way to collapse the box is to use SendKeys and send the escape key.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •