combobox dropdown

[Gil]

Is there a way to tell if a combbox is in dropdown mode.
I tried and if statement combobox.dropdown = true
but i get an error. dropwndown function doesnt store if its true or
false
what i am trying to do is make an autoscroll combobox. like you have on
html textbox's, but this time you hit enter for a change to be made. i
do this because i dont want to requery every time a single letter is
inputed. it would be too slow. so i make them hit enter when they are
ready to search.
you type something in the combox, hit enter, and the combobox refreshes
itself with the new data. then you can choose an item from the dropdown
but this time when you hit enter again rather then refresh the
combobox rowsource, i want the combobox to finally update and move on.
If i had a activecontrol.dropdown state return true or false i would
tell it to update rowsource when activecontrol.dropdown = false and to
update field when activecontrol.dropdown = true.
But i dont have that so i came up with this longggg way...

heres how i did it:

'WHEN YOU HIT ENTER REFRESH THE ROWSOURCE WITH NEW CRITERIA AND
'SET DROPDOWN VARIABLE TO TRUE

Dim onEnter as boolean ' Lets me know if enter was hit already
Dim onDropDown as Boolean ' Lets me know if the combobox is in dropdown
state
Private Sub COMBOBOX1_KeyDown(KeyCode As Integer, Shift As Integer)
If IsNull(Me.COMBOBOX1.Text) Then Exit Sub
Select Case KeyCode
Case 13 'enter
If onDropDown = False Then
Dim strRow As String
Dim x As Integer
Dim strStr1 As String

onEnter = True
Me.COMBOBOX1.SetFocus
x = Nz(Me.txtSTNUM1)
str1 = Me.COMBOBOX1.Text
strRow = "SELECT * WHERE COMBO LIKE '" & Str1 &"' & '*' "
Me.COMBOBOX1.RowSource = strRow

End If
Case 37, 38, 39, 40, 9 'IF ANY KEY IS HIT OTHER THAN ARROW KEYS AND TAB
THEN
Case Else
If onDropDown = True Then onDropDown = False 'RESET DROPDOWN STATE
End Select
End Sub

'WHEN YOU GET FOCUS RESET DROPDOWN STATE

Private Sub COMBOBOX1_GotFocus()
If onDropDown = True Then onDropDown = False
End Sub

'WHEN YOU HIT ENTER YOUR AUTOMATICALLY LOOSE THE FOCUS SO
'WHEN YOU LOOSE FOCUS DURING DROPDOWN STATE BRING THE FOCUS BACK
Private Sub COMBOBOX1_Exit(Cancel As Integer)
If onEnter Then
onDropDown = True
onEnter = False
Me.TEXTBOX.SetFocus 'YOU HAVE TO SET FOCUS TO SOMETHING ELSE FIRST
Me.COMBOBOX1.SetFocus
Call PutCursorAtEnd 'THIS FUNCTION PUTS CURSOR AT END OF CONTROL
Me.COMBOBOX1.Dropdown
End If
End Sub
if someone has a better idea please let me know
Thanks!

[Larry Linson]

Why would you want to recreate a clumsier version of the built-in AutoExpand
feature of Access' Combo Boxes? AutoExpand is set to yes by default, out of
the box. If you've turned it off as a default, open the Property Sheet, and
choose Yes for AutoExpand. It scrolls as you type, without your having to
click Enter. If I understand your post, that is what you wanted to do.

Larry Linson
Microsoft Access MVP

"Gil" <gi*******@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

Is there a way to tell if a combbox is in dropdown mode.
I tried and if statement combobox.dropdown = true
but i get an error. dropwndown function doesnt store if its true or
false
what i am trying to do is make an autoscroll combobox. like you have on
html textbox's, but this time you hit enter for a change to be made. i
do this because i dont want to requery every time a single letter is
inputed. it would be too slow. so i make them hit enter when they are
ready to search.
you type something in the combox, hit enter, and the combobox refreshes
itself with the new data. then you can choose an item from the dropdown
but this time when you hit enter again rather then refresh the
combobox rowsource, i want the combobox to finally update and move on.
If i had a activecontrol.dropdown state return true or false i would
tell it to update rowsource when activecontrol.dropdown = false and to
update field when activecontrol.dropdown = true.
But i dont have that so i came up with this longggg way...

heres how i did it:

'WHEN YOU HIT ENTER REFRESH THE ROWSOURCE WITH NEW CRITERIA AND
'SET DROPDOWN VARIABLE TO TRUE

Dim onEnter as boolean ' Lets me know if enter was hit already
Dim onDropDown as Boolean ' Lets me know if the combobox is in dropdown
state
Private Sub COMBOBOX1_KeyDown(KeyCode As Integer, Shift As Integer)
If IsNull(Me.COMBOBOX1.Text) Then Exit Sub
Select Case KeyCode
Case 13 'enter
If onDropDown = False Then
Dim strRow As String
Dim x As Integer
Dim strStr1 As String

onEnter = True
Me.COMBOBOX1.SetFocus
x = Nz(Me.txtSTNUM1)
str1 = Me.COMBOBOX1.Text
strRow = "SELECT * WHERE COMBO LIKE '" & Str1 &"' & '*' "
Me.COMBOBOX1.RowSource = strRow

End If
Case 37, 38, 39, 40, 9 'IF ANY KEY IS HIT OTHER THAN ARROW KEYS AND TAB
THEN
Case Else
If onDropDown = True Then onDropDown = False 'RESET DROPDOWN STATE
End Select
End Sub

'WHEN YOU GET FOCUS RESET DROPDOWN STATE

Private Sub COMBOBOX1_GotFocus()
If onDropDown = True Then onDropDown = False
End Sub

'WHEN YOU HIT ENTER YOUR AUTOMATICALLY LOOSE THE FOCUS SO
'WHEN YOU LOOSE FOCUS DURING DROPDOWN STATE BRING THE FOCUS BACK
Private Sub COMBOBOX1_Exit(Cancel As Integer)
If onEnter Then
onDropDown = True
onEnter = False
Me.TEXTBOX.SetFocus 'YOU HAVE TO SET FOCUS TO SOMETHING ELSE FIRST
Me.COMBOBOX1.SetFocus
Call PutCursorAtEnd 'THIS FUNCTION PUTS CURSOR AT END OF CONTROL
Me.COMBOBOX1.Dropdown
End If
End Sub
if someone has a better idea please let me know
Thanks!

[Gil]

Hello,
thanks for the reply.
I am trying to fliter a 500,000 record combobox down to only the
results typed into the combobox. It will speed up the form. Besides
that you are right. I could stick with the default autoexpand.
How would you handle such large data in a single combobox?

[pietlinden]

YUCK... don't populate the combobox (don't assign a rowsource) until
AFTER you have typed in maybe 5 characters.

Wow. How does the form perform if your have a split front and back
end? I'd imagine that performance would be hideous...

[salad]

Gil wrote:

Hello,
thanks for the reply.
I am trying to fliter a 500,000 record combobox down to only the
results typed into the combobox. It will speed up the form. Besides
that you are right. I could stick with the default autoexpand.
How would you handle such large data in a single combobox?

You have got to be trolling...or kidding...or something. A 500,000
record combo box? Hahahahaha.

Have you ever heard of a form? 500,000 record combo box? Hahahahaha.
Good one.

[Larry Linson]

"Gil" <gi*******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I am trying to fliter a 500,000 record combobox down to only the
results typed into the combobox. It will speed up the form. Besides
that you are right. I could stick with the default autoexpand.
How would you handle such large data in a single combobox?

I understand your situation. I agree with Piet and Salad that is too much
for a Combo Box -- I can't remember the limit in Row Source, but I don't
remember that it is half-a-million. Do you have any idea of the distribution
of the contents by beginning letter of the alphabet?

You may have to do some filtering, as Piet suggested, before you even load
the Combo Box.

Very likely the users will not be just randomly browsing, so the information
they'll have before searching is important to know in designing a solution.

I once worked on someone else's database application that populated a
continous forms view form with thousands of records, and I really wanted to
change that. But, what I found out was that the users did not use that
screen to select -- they always had a paper file in hand that had a specific
identification code that they typed in to a text box that took them directly
to the record they needed.

If you have to "go brute force", in the Change event, which fires on every
character typed in, you would use VBA code to pick up the value entered so
far, build an SQL statement with a LIKE clause, and replace the Row Source
of the Combo with your new SQL.

Larry Linson
Microsoft Access MVP