Ignore predictive text in a search combo box

[cfuhrhop.patterson]

I have a combo box populated from a certain column. This box also
acts as the input box for a search function that i have updating per
keyup in a list box. The problem is that I would like to retain the
auto-expand feature, but would like the text it is suggesting to be
ignored until the user hits tab or enter or something to that effect.

For example, if one types in "month" the suggested text is "months of
treatment". This updates the list box to show only records containing
"months of treatment," and excludes records that should otherwise be
included, like those containing "month/year".

Any ideas?

 

[Mr. B]

I have a combo box populated from a certain column. This box also
acts as the input box for a search function that i have updating per
keyup in a list box. The problem is that I would like to retain the
auto-expand feature, but would like the text it is suggesting to be
ignored until the user hits tab or enter or something to that effect.

For example, if one types in "month" the suggested text is "months of
treatment". This updates the list box to show only records containing
"months of treatment," and excludes records that should otherwise be
included, like those containing "month/year".

Any ideas?

I nornally use the "After Update" event of the combo box to run the
code to requery the list box.

This way, the list box will not be changing with each key stroke.

Hope this helps.

Mr. B

 

[cfuhrhop.patterson]

I nornally use the "After Update" event of the combo box to run the
code to requery the list box.

This way, the list box will not be changing with each key stroke.

Hope this helps.

Mr. B

The problem with that is that I want to show what options are being
eliminated as the user types. Because I am using a simple LIKE
statement in my sql, if they type something like birthday and the
values are all birthdate, then nothing would come up afterupdate, they
would, however see that there are values up until birthda...

 

[Dirk Goldgar]

In

I have a combo box populated from a certain column. This box also
acts as the input box for a search function that i have updating per
keyup in a list box. The problem is that I would like to retain the
auto-expand feature, but would like the text it is suggesting to be
ignored until the user hits tab or enter or something to that effect.

For example, if one types in "month" the suggested text is "months of
treatment". This updates the list box to show only records containing
"months of treatment," and excludes records that should otherwise be
included, like those containing "month/year".

Any ideas?

If I understand you properly, you are using the combo box's KeyUp event
to update the list box. Could we see that code, please? I guess the
problem is that you are using the combo's Text property as a criterion
for the list box's rowsource, but the Text includes the auto-expanded
text, not just what the user has typed. Is that correct? If you want
to abandon the list box's auto-narrowing search functionality, you can
just use the combo box's AfterUpdate event instead of the KeyUp event.

If you want to keep the list box's auto-narrowing search with each
keystroke, I think your solution is going to involve something like
this:

Dim strText As String

With Me!MyComboBox
strText = Left(.Text, .SelStart)
End With

Me.MyListBox.RowSource =
"SELECT ... FROM ... WHERE SomeField LIKE " &
Chr(34) & strText & "*" & Chr(34)

 

[cfuhrhop.patterson]

If I understand you properly, you are using the combo box's KeyUp event
to update the list box. Could we see that code, please? I guess the
problem is that you are using the combo's Text property as a criterion
for the list box's rowsource, but the Text includes the auto-expanded
text, not just what the user has typed. Is that correct? If you want
to abandon the list box's auto-narrowing search functionality, you can
just use the combo box's AfterUpdate event instead of the KeyUp event.

If you want to keep the list box's auto-narrowing search with each
keystroke, I think your solution is going to involve something like
this:

Dim strText As String

With Me!MyComboBox
strText = Left(.Text, .SelStart)
End With

Me.MyListBox.RowSource =
"SELECT ... FROM ... WHERE SomeField LIKE " &
Chr(34) & strText & "*" & Chr(34)

Dirk,
You are correct in your understanding, and I am certain that your code
is right on for what I am looking for, but for some reason, when I
type the first letter into MyComboBox, an input prompt comes up and
asks for a value for whatever letter it is that I typed. Any ideas on
what would cause that and how to fix it?

Thanks so much for your response.

 

[Dirk Goldgar]

In

You are correct in your understanding, and I am certain that your code
is right on for what I am looking for, but for some reason, when I
type the first letter into MyComboBox, an input prompt comes up and
asks for a value for whatever letter it is that I typed. Any ideas on
what would cause that and how to fix it?

Thanks so much for your response.

It sounds like the rowsource string you're assigning to the list box
isn't formed correctly, so that the text value from the combo box is
being interpreted as a field/parameter name, not a literal. Please post
your code.

 

[cfuhrhop.patterson]

It sounds like the rowsource string you're assigning to the list box
isn't formed correctly, so that the text value from the combo box is
being interpreted as a field/parameter name, not a literal. Please post
your code.

Private Sub Heading_KeyUp(KeyCode As Integer, Shift As Integer)
Dim strText As String

With Me!Heading
strText = Left(.Text, .SelStart)
End With

Me!Name.RowSource = "SELECT DISTINCT Reports.Heading, Reports.
[Report Name] FROM Reports WHERE (((Reports.Heading) Like " & Chr(34)
& "*" & Chr(34) & " & " & strText & " & " & Chr(34) & "*" & Chr(34) &
"));"
Me!Name.Requery
End Sub

 

[Dirk Goldgar]

In

It sounds like the rowsource string you're assigning to the list box
isn't formed correctly, so that the text value from the combo box is
being interpreted as a field/parameter name, not a literal. Please
post your code.

Private Sub Heading_KeyUp(KeyCode As Integer, Shift As Integer)
Dim strText As String

With Me!Heading
strText = Left(.Text, .SelStart)
End With

Me!Name.RowSource = "SELECT DISTINCT Reports.Heading, Reports.
[Report Name] FROM Reports WHERE (((Reports.Heading) Like " & Chr(34)
& "*" & Chr(34) & " & " & strText & " & " & Chr(34) & "*" & Chr(34) &
"));"
Me!Name.Requery
End Sub

Apparently you modified the statement with the intention of searching
for the text anywhere in the Heading field, rather than just at the
start of the field, but you made a small mistake and ended up with
strText outside the embedded quotes. I don't know if you need to search
anywhere in the field, since your combo box is always going to be
choosing from the start of the field, but if that's what you want to do,
I think this will work better:

Me!Name.RowSource = _
"SELECT DISTINCT " & _
"Reports.Heading, Reports.[Report Name] " & _
"FROM Reports " & _
"WHERE Reports.Heading Like " & _
Chr(34) & "*" & strText & "*" & Chr(34)

In addition to fixing the quoting, I removed the extra parentheses and
the terminal semicolon. They did no harm, but you don't really need
them.

Also, delete the requery line:

Me!Name.Requery

Changing the RowSource property forces a requery, so requerying it a
second time serves no purpose.