GrayJay's code for dynamic combo box that sub-selects the sourcebased on characters entered

[Bob Alston]

In 2002, "GrayJay" posted the following code:

I did this in a jazz record catalogue to find composers -

On a form "frmComposers"
Create a text box - txtFindComposer, and add the following sub

Private Sub txtFindComposer_Change()
Requery
Me![txtFindComposer].SetFocus
SendKeys "{F2}", True
End Sub

The form's Record Source is:

SELECT tblTunes.Composer,
InStr(1,[composer],[Forms]![frmComposers]![txtFindComposer],1) AS
NamePosition, tblTunes.TuneID, tblTunes.TuneTitle, tblTunes.TuneNotes
FROM tblTunes
WHERE
(((InStr(1,[composer],[Forms]![frmComposers]![txtFindComposer],1))<>0))
ORDER BY tblTunes.Composer;

Now adding one letter at a time the list of composers is reduced until the
one you want can be found. I'm sure you could hack this around to suit your
app. I don't know how well it would work with 132,000 records though.

HTH

"Mike" <mi**@abchansen.ru> wrote:

**********************************
Recently I have been trying to do the same thing. Enhance a combo box I
use for client selection, by allowing the user to start typing the
client's last name - the list in the combo box should be repainted to
show only names that equal the letters typed so far.
I can get it to work for a single keypress. However I have to manually
redisplay the drop down box. If I enter another key, the drop down box
closes up. So essentially it works reasonably but only for 1 character,
although you do have to press the "down arrow" an extra time.

Suggestions?

Bob

[Salad]

Bob Alston wrote:

In 2002, "GrayJay" posted the following code:

I did this in a jazz record catalogue to find composers -

On a form "frmComposers"
Create a text box - txtFindComposer, and add the following sub

Private Sub txtFindComposer_Change()
Requery
Me![txtFindComposer].SetFocus
SendKeys "{F2}", True
End Sub

The form's Record Source is:

SELECT tblTunes.Composer,
InStr(1,[composer],[Forms]![frmComposers]![txtFindComposer],1) AS
NamePosition, tblTunes.TuneID, tblTunes.TuneTitle, tblTunes.TuneNotes
FROM tblTunes
WHERE
(((InStr(1,[composer],[Forms]![frmComposers]![txtFindComposer],1))<>0))
ORDER BY tblTunes.Composer;

Now adding one letter at a time the list of composers is reduced until the
one you want can be found. I'm sure you could hack this around to suit your
app. I don't know how well it would work with 132,000 records though.

HTH

"Mike" <mi**@abchansen.ru> wrote:

**********************************
Recently I have been trying to do the same thing. Enhance a combo box I
use for client selection, by allowing the user to start typing the
client's last name - the list in the combo box should be repainted to
show only names that equal the letters typed so far.
I can get it to work for a single keypress. However I have to manually
redisplay the drop down box. If I enter another key, the drop down box
closes up. So essentially it works reasonably but only for 1 character,
although you do have to press the "down arrow" an extra time.

Suggestions?

Bob

Yes. Create a global variable/field and use the KeyDown for the combo
to concatenate the values then requery or give the combo a new
rowsource. You'd set the value to Null or "" on the GotFocus event of
the control.

BTW, you could enter something like
Me.ComboBox.DropDown = True
to force the combo to drop.

[Bob Alston]

Salad wrote:

Bob Alston wrote:

In 2002, "GrayJay" posted the following code:

I did this in a jazz record catalogue to find composers -

On a form "frmComposers"
Create a text box - txtFindComposer, and add the following sub

Private Sub txtFindComposer_Change()
Requery
Me![txtFindComposer].SetFocus
SendKeys "{F2}", True
End Sub

The form's Record Source is:

SELECT tblTunes.Composer,
InStr(1,[composer],[Forms]![frmComposers]![txtFindComposer],1) AS
NamePosition, tblTunes.TuneID, tblTunes.TuneTitle, tblTunes.TuneNotes
FROM tblTunes
WHERE
(((InStr(1,[composer],[Forms]![frmComposers]![txtFindComposer],1))<>0))
ORDER BY tblTunes.Composer;

Now adding one letter at a time the list of composers is reduced until
the
one you want can be found. I'm sure you could hack this around to suit
your
app. I don't know how well it would work with 132,000 records though.

HTH

"Mike" <mi**@abchansen.ru> wrote:

**********************************
Recently I have been trying to do the same thing. Enhance a combo box
I use for client selection, by allowing the user to start typing the
client's last name - the list in the combo box should be repainted to
show only names that equal the letters typed so far.
I can get it to work for a single keypress. However I have to
manually redisplay the drop down box. If I enter another key, the
drop down box closes up. So essentially it works reasonably but only
for 1 character, although you do have to press the "down arrow" an
extra time.

Suggestions?

Bob

Yes. Create a global variable/field and use the KeyDown for the combo
to concatenate the values then requery or give the combo a new
rowsource. You'd set the value to Null or "" on the GotFocus event of
the control.

BTW, you could enter something like
Me.ComboBox.DropDown = True
to force the combo to drop.

Unfortunately the syntax
Me.ComboBox.DropDown = True
is not correct.
Also tried code to obtain an object referenct to the combobox and set
the property but that failed also.
Anyone know the proper syntax/reference???
Bob

[Tim Marshall]

Bob Alston wrote:

Unfortunately the syntax
Me.ComboBox.DropDown = True
is not correct.
It's just simply Me.ComboBox.DropDown.

When you're coding, look at the intellisense for VBA, it's very helpful.

For example, after you type Me and then a dot, a picklist of a number of
things, including the combobox name appears. Press the tab key when the
VBA picklist highlights the control name (combobox name) and the
intellisense will complete it. After that, key in a dot and another
picklist, specific for the controltype that follows Me. (in this case, a
combobox) appears. When you see a hand pointing to a paper, that means
you're working with a property which requires = True/false or some
value. When it's a little tilted green box with "streak marks" to the
left, it's an executable sort of thing that may not need to be set = to
anything, though it might require parameters afterward.

In your case, "dropdown" is an executable that requires no parameters.

Also tried code to obtain an object referenct to the combobox and set
the property but that failed also.

Did you understand what Salad was suggesting? Maybe he can expand on it
for you?

BTW it's no big shame to admit you don't understand what some folks
write, we all have to learn somewhere. I was asking questions here in
the late 90s that I sometimes see in Google searches and I knew NOTHING.

8)
--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[GrayJay]

Hi Bob,

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

In 2002, "GrayJay" posted the following code:

I did this in a jazz record catalogue to find composers -

On a form "frmComposers"
Create a text box - txtFindComposer, and add the following sub

Private Sub txtFindComposer_Change()
Requery
Me![txtFindComposer].SetFocus
SendKeys "{F2}", True
End Sub

The form's Record Source is:

SELECT tblTunes.Composer,
InStr(1,[composer],[Forms]![frmComposers]![txtFindComposer],1) AS
NamePosition, tblTunes.TuneID, tblTunes.TuneTitle, tblTunes.TuneNotes
FROM tblTunes
WHERE
(((InStr(1,[composer],[Forms]![frmComposers]![txtFindComposer],1))<>0))
ORDER BY tblTunes.Composer;

Now adding one letter at a time the list of composers is reduced until the
one you want can be found. I'm sure you could hack this around to suit your
app. I don't know how well it would work with 132,000 records though.

HTH

"Mike" <mi**@abchansen.ru> wrote:

**********************************
Recently I have been trying to do the same thing. Enhance a combo box I
use for client selection, by allowing the user to start typing the
client's last name - the list in the combo box should be repainted to
show only names that equal the letters typed so far.
I can get it to work for a single keypress. However I have to manually
redisplay the drop down box. If I enter another key, the drop down box
closes up. So essentially it works reasonably but only for 1 character,
although you do have to press the "down arrow" an extra time.

Suggestions?

Bob

I'm not sure if a combo box can do what you are trying to do, though
there may be someone around here who will correct me.

The method I gave in my original post worked for me. It used a simple
form with an unbound text box in the header and the detail consisted of
the records which matched it.

I'm sure this method could be used in your app, perhaps with a dbl-click
on the appropriate record to open another form with the full client
info.

BTW this does not take spaces into account, I haven't figured this yet.
--
»«»«»« Graham »«»«»«

[Salad]

Bob Alston wrote:

Salad wrote:

Bob Alston wrote:

In 2002, "GrayJay" posted the following code:

I did this in a jazz record catalogue to find composers -

On a form "frmComposers"
Create a text box - txtFindComposer, and add the following sub

Private Sub txtFindComposer_Change()
Requery
Me![txtFindComposer].SetFocus
SendKeys "{F2}", True
End Sub

The form's Record Source is:

SELECT tblTunes.Composer,
InStr(1,[composer],[Forms]![frmComposers]![txtFindComposer],1) AS
NamePosition, tblTunes.TuneID, tblTunes.TuneTitle, tblTunes.TuneNotes
FROM tblTunes
WHERE
(((InStr(1,[composer],[Forms]![frmComposers]![txtFindComposer],1))<>0))
ORDER BY tblTunes.Composer;

Now adding one letter at a time the list of composers is reduced
until the
one you want can be found. I'm sure you could hack this around to
suit your
app. I don't know how well it would work with 132,000 records though.

HTH

"Mike" <mi**@abchansen.ru> wrote:

**********************************
Recently I have been trying to do the same thing. Enhance a combo
box I use for client selection, by allowing the user to start typing
the client's last name - the list in the combo box should be
repainted to show only names that equal the letters typed so far.
I can get it to work for a single keypress. However I have to
manually redisplay the drop down box. If I enter another key, the
drop down box closes up. So essentially it works reasonably but only
for 1 character, although you do have to press the "down arrow" an
extra time.

Suggestions?

Bob

Yes. Create a global variable/field and use the KeyDown for the combo
to concatenate the values then requery or give the combo a new
rowsource. You'd set the value to Null or "" on the GotFocus event of
the control.

BTW, you could enter something like
Me.ComboBox.DropDown = True
to force the combo to drop.

Unfortunately the syntax
Me.ComboBox.DropDown = True
is not correct.
Also tried code to obtain an object referenct to the combobox and set
the property but that failed also.
Anyone know the proper syntax/reference???
Bob

There was someone that asked a very similar question to yours on Nov 11,
2004. The subject line is "Dynamic combo box - limit selection based on
user entry" See if my reply in that thread provides more info. It may
be in your newsreaders header list, otherwise find it at
http://www.groups.google.com

I see that I used the wrong syntax for the .DropDown but that has been
addressed very well. Sometimes it may be best to highlight a method or
property (ex: DropDown), while in the code window, and press F1.

[PC Datasheet]

Ken Getz has a procedure to do what you want to do using a textbox and a
listbox in the Access Developer's Handbook. It works extremely well! I have
used it numerous times.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Bob Alston" <tu****************@cox.net> wrote in message
news:dDcod.58567$_g6.51785@okepread03...

In 2002, "GrayJay" posted the following code:

I did this in a jazz record catalogue to find composers -

On a form "frmComposers"
Create a text box - txtFindComposer, and add the following sub

Private Sub txtFindComposer_Change()
Requery
Me![txtFindComposer].SetFocus
SendKeys "{F2}", True
End Sub

The form's Record Source is:

SELECT tblTunes.Composer,
InStr(1,[composer],[Forms]![frmComposers]![txtFindComposer],1) AS
NamePosition, tblTunes.TuneID, tblTunes.TuneTitle, tblTunes.TuneNotes
FROM tblTunes
WHERE
(((InStr(1,[composer],[Forms]![frmComposers]![txtFindComposer],1))<>0))
ORDER BY tblTunes.Composer;

Now adding one letter at a time the list of composers is reduced until the
one you want can be found. I'm sure you could hack this around to suit your app. I don't know how well it would work with 132,000 records though.

HTH

"Mike" <mi**@abchansen.ru> wrote:

**********************************
Recently I have been trying to do the same thing. Enhance a combo box I
use for client selection, by allowing the user to start typing the
client's last name - the list in the combo box should be repainted to
show only names that equal the letters typed so far.
I can get it to work for a single keypress. However I have to manually
redisplay the drop down box. If I enter another key, the drop down box
closes up. So essentially it works reasonably but only for 1 character,
although you do have to press the "down arrow" an extra time.

Suggestions?

Bob

[Bas Cost Budde]

Tim Marshall wrote:

BTW it's no big shame to admit you don't understand what some folks
write, we all have to learn somewhere. I was asking questions here in
the late 90s that I sometimes see in Google searches and I knew NOTHING.

Fools and wise make mistakes alike, only fools hide them.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Bob Alston]

Tim Marshall wrote:

Bob Alston wrote:

Unfortunately the syntax
Me.ComboBox.DropDown = True
is not correct.

It's just simply Me.ComboBox.DropDown.

When you're coding, look at the intellisense for VBA, it's very helpful.

For example, after you type Me and then a dot, a picklist of a number of
things, including the combobox name appears. Press the tab key when the
VBA picklist highlights the control name (combobox name) and the
intellisense will complete it. After that, key in a dot and another
picklist, specific for the controltype that follows Me. (in this case, a
combobox) appears. When you see a hand pointing to a paper, that means
you're working with a property which requires = True/false or some
value. When it's a little tilted green box with "streak marks" to the
left, it's an executable sort of thing that may not need to be set = to
anything, though it might require parameters afterward.

In your case, "dropdown" is an executable that requires no parameters.

Also tried code to obtain an object referenct to the combobox and set
the property but that failed also.

Did you understand what Salad was suggesting? Maybe he can expand on it
for you?

BTW it's no big shame to admit you don't understand what some folks
write, we all have to learn somewhere. I was asking questions here in
the late 90s that I sometimes see in Google searches and I knew NOTHING.

8)

Thank you. I had misunderstood the object type. I thought it was a
property that needed a value.

Also thanks for clarifying intellisense for VBA. I use it and have
found it helpful. I just didn't understand the nuances. Thanks.

Bob

[Bob Alston]

PC Datasheet wrote:

Ken Getz has a procedure to do what you want to do using a textbox and a
listbox in the Access Developer's Handbook. It works extremely well! I have
used it numerous times.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Bob Alston" <tu****************@cox.net> wrote in message
news:dDcod.58567$_g6.51785@okepread03...

In 2002, "GrayJay" posted the following code:

I did this in a jazz record catalogue to find composers -

On a form "frmComposers"
Create a text box - txtFindComposer, and add the following sub

Private Sub txtFindComposer_Change()
Requery
Me![txtFindComposer].SetFocus
SendKeys "{F2}", True
End Sub

The form's Record Source is:

SELECT tblTunes.Composer,
InStr(1,[composer],[Forms]![frmComposers]![txtFindComposer],1) AS
NamePosition, tblTunes.TuneID, tblTunes.TuneTitle, tblTunes.TuneNotes
FROM tblTunes
WHERE
(((InStr(1,[composer],[Forms]![frmComposers]![txtFindComposer],1))<>0))
ORDER BY tblTunes.Composer;

Now adding one letter at a time the list of composers is reduced until the
one you want can be found. I'm sure you could hack this around to suit

your

app. I don't know how well it would work with 132,000 records though.

HTH

"Mike" <mi**@abchansen.ru> wrote:

**********************************
Recently I have been trying to do the same thing. Enhance a combo box I
use for client selection, by allowing the user to start typing the
client's last name - the list in the combo box should be repainted to
show only names that equal the letters typed so far.
I can get it to work for a single keypress. However I have to manually
redisplay the drop down box. If I enter another key, the drop down box
closes up. So essentially it works reasonably but only for 1 character,
although you do have to press the "down arrow" an extra time.

Suggestions?

Bob

I bought both the 2002 "Access Developer's Handbook" series you
mentioned. Can you point me to where it is in the book?

Bob