Dynamic combo box - limit selection based on user entry

[Bob Alston]

I am using a combo box to select the key to records and then go to the
selected record on my form. Works well. However, the list, which
contains people names, is rather long. What I would like to do is to
allow entry of one or more letters of the beginning of the last name,
like "j" for someone named Johnson (or Johnston) , where the list in the
combo box will only show names equivalent to a "like {j*}" clause.

So far it appears Access wants the combo box select statement to be
text, not using any field variable names.

Suggestions?
Bob Alston

[Salad]

Bob Alston <Tu**********@cox.net> wrote:

I am using a combo box to select the key to records and then go to the
selected record on my form. Works well. However, the list, which
contains people names, is rather long. What I would like to do is to
allow entry of one or more letters of the beginning of the last name,
like "j" for someone named Johnson (or Johnston) , where the list in the
combo box will only show names equivalent to a "like {j*}" clause.

So far it appears Access wants the combo box select statement to be
text, not using any field variable names.

Suggestions?
Bob Alston

Well...here's some code
Private Sub Combo0_KeyDown(KeyCode As Integer, Shift As Integer)
MsgBox "keycode is " & KeyCode
End Sub

Here's some more code.
Private Sub Combo0_Change()
MsgBox "I changed " & Me.Combo0.SelStart
End Sub

The Change event works semi-OK. Let's say I have a customer called
JoeBlow Tires. As I type J, selstart is 1. When I enter o, selstart is
2. But there is a problem. Change does not recognize backspacing.
So..you may want KeyDown.

You'd set a global variable to "" when it gets focus. Then grab the
keys and store them to the global variable. Ex:
Option Compare Database
Option Explicit
Dim strGlobal As String

Private Sub Combo0_GotFocus()
strGlobal = ""
End Sub
Private Sub Combo0_KeyDown(KeyCode As Integer, Shift As Integer)
Dim strWhere as STring
If KeyCOde <> 37 then
strGlobal = strGlobal & keycode
else
If Len(strGlobal) > 1 then
strGlobal = Left(strGlobal,len(strGlobal)-1
else
strGlobal = ""
Endif
Endif
If strGlobal > ""
strWhere = " Where Left(CustomerName," & _
Len(strGlobal) & ") = '" strGlobal & "'"
Endif
Me.Combo0.RowSource = "Select CustomerID, CustomerName From
Customers " & strWhere & " Order By CustomerName
End Sub

Let's say strGlobal was 3 chars at this point. The where clause would
be "Where Left(CustomerName,3) = 'Joe'"

Changing the rowsource will pull only those records that meet your input
at that point. You may have to check for tab keys, enter keys,
etc...but you have the idea.

[Sunil Korah]

"Bob Alston <Tu**********@cox.net>" <Tu**********@cox.net> wrote in message news:<CdPkd.53364$_g6.34507@okepread03>...

I am using a combo box to select the key to records and then go to the
selected record on my form. Works well. However, the list, which
contains people names, is rather long. What I would like to do is to
allow entry of one or more letters of the beginning of the last name,
like "j" for someone named Johnson (or Johnston) , where the list in the
combo box will only show names equivalent to a "like {j*}" clause.

So far it appears Access wants the combo box select statement to be
text, not using any field variable names.

Suggestions?
Bob Alston

You can give a query as the source for the combo box

Sunil Korah

[Bob Alston]

hb*****@indiatimes.com (Sunil Korah) wrote in message news:<72**************************@posting.google. com>...

"Bob Alston <Tu**********@cox.net>" <Tu**********@cox.net> wrote in message news:<CdPkd.53364$_g6.34507@okepread03>...

I am using a combo box to select the key to records and then go to the
selected record on my form. Works well. However, the list, which
contains people names, is rather long. What I would like to do is to
allow entry of one or more letters of the beginning of the last name,
like "j" for someone named Johnson (or Johnston) , where the list in the
combo box will only show names equivalent to a "like {j*}" clause.

So far it appears Access wants the combo box select statement to be
text, not using any field variable names.

Suggestions?
Bob Alston

You can give a query as the source for the combo box

Sunil Korah

OK. I tried your code two above. Fixed the couple of syntax errors.
When I enter a character it gets written to the field. If I click on
the "down arror" again, the code is applied and I get a query
restricted by the one character I have entered. If I do it again, it
is as if the process ends after 1 character and it gets stored in the
field.

Suggestions?

Bob Alston