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?

On May 24, 9:06 am, cfuhrhop.patter...@gmail.com wrote:
> 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

On May 24, 11:01 am, "Mr. B" <mrbp...@suddenlink.net> wrote:
>
> 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...

In news:1180015574.499969.10250@p77g2000hsh.googlegroups.com,
cfuhrhop.patterson@gmail.com <cfuhrhop.patterson@gmail.com> wrote:
> 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)

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

On May 24, 11:09 am, "Dirk Goldgar" <d...@NOdataSPAMgnostics.com>
wrote:
>
> 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 Goldgar, MS Access MVPwww.datagnostics.com
>
> (please reply to the newsgroup)

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.

In news:1180025659.998257.214020@q69g2000hsb.googlegroups.com,
cfuhrhop.patterson@gmail.com <cfuhrhop.patterson@gmail.com> wrote:
> 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.

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

On May 24, 12:19 pm, "Dirk Goldgar" <d...@NOdataSPAMgnostics.com>
wrote:
> 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.
>
> --
> Dirk Goldgar, MS Access MVPwww.datagnostics.com
>
> (please reply to the newsgroup)

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

In news:1180027581.986865.302630@q66g2000hsg.googlegroups.com,
cfuhrhop.patterson@gmail.com <cfuhrhop.patterson@gmail.com> wrote:
> On May 24, 12:19 pm, "Dirk Goldgar" <d...@NOdataSPAMgnostics.com>
> wrote:
>> 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.

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)