Collapsing a combo box in VBA

Topic

New Reply

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
:confused:

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Any chance of a compacted zipped database with just the minimum in it.

Reply | Quote

While I do not always advocate this. The quickest easiest way to collapse the box is to use SendKeys and send the escape key.

Reply | Quote