Auto DropDown Combo Boxes

[KRR]

I have a form with over 125 combo boxes and would like every one of them to
automatically drop down when they receive the focus. Is there a way to do
this without having to code a gotfocus() event for each one?

Thanks,
Ken

 

[Douglas J. Steele]

Not really, although you can simplify it considerably.

Create a function (must be a function, not a sub) along the lines of:

Function DropCombo()
If TypeOf Me.ActiveControl Is ComboBox Then
Me.ActiveControl.Dropdown
End If
End Function

Then, with the form in Design mode, select each of the combo boxes ("rope"
them, hold down the shift key while clicking on them, whatever) Whatever you
enter in the Properties box now will apply to all of the selected controls.
Set the OnFocus property to =DropCombo() (including the equal sign and
parentheses). You're done!

 

[Douglas J. Steele]

Nice trick! However, if you're only "roping" comboboxes, why the
conditional check for a combobox? It seems redundant.

I never like to take anything for granted. <g>

 

[CW]

I would like to use this but...sorry, stoopid question.... how and where do I
create that Function? I'm quite familiar with Subs and have done quite a bit
of coding, but never a Function before, please clarify for me
Many thanks
CW

 

[John Spencer]

Just copy the code as posted and then paste it into the FORM's VBA module. It
could be made to work if you posted it into a standard VBA module, but then
you would not be able to use
Me.ActiveControl
but would need to use
Screen.ActiveControl

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[CW]

Thanks John - I had been trying to put it into the individual combo's event
code but now I understand, it relates to the form rather than a control.
Many thanks
CW

 

[David W. Fenton]

Create a function (must be a function, not a sub) along the lines
of:

Function DropCombo()
If TypeOf Me.ActiveControl Is ComboBox Then
Me.ActiveControl.Dropdown
End If
End Function

Then, with the form in Design mode, select each of the combo boxes
("rope" them, hold down the shift key while clicking on them,
whatever) Whatever you enter in the Properties box now will apply
to all of the selected controls. Set the OnFocus property to
=DropCombo() (including the equal sign and parentheses). You're
done!

I'd use OnChange and check Len(Screen.ActiveControl.Text)=1 and
dropdown only on that. I don't like dropdowns popping out ust when
you are tabbing through the controls on your form.

 

[loner]

Hi All
I have a situation where there has been a changed mail server. We now
have 500 incorrect mail addresses in our database.
That is, (e-mail address removed) has changed to (e-mail address removed)
so in all cases everything before the @ symbol stays the same.
How do I strip everything forward of the @ symbol so I can make the changes.
Thanks in advance.

 

[John W. Vinson]

Hi All
I have a situation where there has been a changed mail server. We now
have 500 incorrect mail addresses in our database.
That is, (e-mail address removed) has changed to (e-mail address removed)
so in all cases everything before the @ symbol stays the same.
How do I strip everything forward of the @ symbol so I can make the changes.
Thanks in advance.

Don't strip it. Instead, use the builtin Replace() function:

UPDATE mytable
SET (e-mail address removed)