Hi,
I have a adp (access 2003 sp3) with sql server 2000. On a simple form with a
simple recordsource query, a combobox (cbt) with a standard sql query in
rowsource. All event proc are running well (it is a linked control). The
query result is a large set of records. I imagined a solution where with the
"change" event, I computed a "where" clause for my query as "where personName
LIKE 'xxx%'" with xxx is the start of word striked by the user.

Private Sub cbt_Change()
cbt.RowSource = "SELECT TOP 8 N_person AS GUID, Name + ' ' + Surname AS
Label FROM Person WHERE Name + ' ' + Surname LIKE '" & Mid(cbt.Text, 1,
cbt.SelStart) & "%' ORDER BY Name + ' ' + Surname"
End Sub

This works correctly (combo is correctly populated, suggest is ok, edit is
ok) except when you use "enter" or "tab" keys before the end of the word
(cbt.sellength>0). The combobox stores a value that's not expected.

I said to myself, maybe I made something wrong and I tried it with a
classical mdb. It's work fine.

Somebody has an idea.

Thanks

Yup, that seems to be another ADP's bug. I would suggest that you create
instead an unbound combobox and change the value of the record in its
AfterUpdate event.

A second possibility would be to set the rows source as a list instead of a
query string but I don't know if this will work OK in this case.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


"Belu33fr" <(E-Mail Removed)> wrote in message
news:853EA0B1-94BE-4A32-A670-(E-Mail Removed)...
> Hi,
> I have a adp (access 2003 sp3) with sql server 2000. On a simple form with
> a
> simple recordsource query, a combobox (cbt) with a standard sql query in
> rowsource. All event proc are running well (it is a linked control). The
> query result is a large set of records. I imagined a solution where with
> the
> "change" event, I computed a "where" clause for my query as "where
> personName
> LIKE 'xxx%'" with xxx is the start of word striked by the user.
>
> Private Sub cbt_Change()
> cbt.RowSource = "SELECT TOP 8 N_person AS GUID, Name + ' ' + Surname AS
> Label FROM Person WHERE Name + ' ' + Surname LIKE '" & Mid(cbt.Text, 1,
> cbt.SelStart) & "%' ORDER BY Name + ' ' + Surname"
> End Sub
>
> This works correctly (combo is correctly populated, suggest is ok, edit is
> ok) except when you use "enter" or "tab" keys before the end of the word
> (cbt.sellength>0). The combobox stores a value that's not expected.
>
> I said to myself, maybe I made something wrong and I tried it with a
> classical mdb. It's work fine.
>
> Somebody has an idea.
>
> Thanks

Hi,
Thanks for your answer. I followed your first way before your message. It
was a little bit complicated (around you need many things). I did not
imaginated your second way. But, this last evening, I found something
interresting. The time where Access makes change, select, and so and is
between keypress and change event. So, because the change of the rowsource is
too late in the change event, I think that it is the reason of this uncorrect
result, but I'm not sure.
In all case, I made a little test in the keypress event and all is fine. I
include this solution into my final code and it is good.
The code of my test:
Private Sub Cbt_KeyPress(KeyAscii As Integer)
Dim A As String
A = Chr(KeyAscii)
If AscB(A) < 32 And AscB(A) = 127 Then A=""
cbt.RowSource = "SELECT TOP 8 N_person AS GUID, Name + ' ' + Surname AS
Label FROM Person WHERE Name + ' ' + Surname LIKE '" & Mid(cbt.Text, 1,
cbt.SelStart) & A & "%' ORDER BY Name + ' ' + Surname"
End Sub

Thanks for your help and idea.
Luc


"Sylvain Lafontaine" wrote:

> Yup, that seems to be another ADP's bug. I would suggest that you create
> instead an unbound combobox and change the value of the record in its
> AfterUpdate event.
>
> A second possibility would be to set the rows source as a list instead of a
> query string but I don't know if this will work OK in this case.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Email: sylvain aei ca (fill the blanks, no spam please)
> Independent consultant and remote programming for Access and SQL-Server
> (French)
>
>
> "Belu33fr" <(E-Mail Removed)> wrote in message
> news:853EA0B1-94BE-4A32-A670-(E-Mail Removed)...
> > Hi,
> > I have a adp (access 2003 sp3) with sql server 2000. On a simple form with
> > a
> > simple recordsource query, a combobox (cbt) with a standard sql query in
> > rowsource. All event proc are running well (it is a linked control). The
> > query result is a large set of records. I imagined a solution where with
> > the
> > "change" event, I computed a "where" clause for my query as "where
> > personName
> > LIKE 'xxx%'" with xxx is the start of word striked by the user.
> >
> > Private Sub cbt_Change()
> > cbt.RowSource = "SELECT TOP 8 N_person AS GUID, Name + ' ' + Surname AS
> > Label FROM Person WHERE Name + ' ' + Surname LIKE '" & Mid(cbt.Text, 1,
> > cbt.SelStart) & "%' ORDER BY Name + ' ' + Surname"
> > End Sub
> >
> > This works correctly (combo is correctly populated, suggest is ok, edit is
> > ok) except when you use "enter" or "tab" keys before the end of the word
> > (cbt.sellength>0). The combobox stores a value that's not expected.
> >
> > I said to myself, maybe I made something wrong and I tried it with a
> > classical mdb. It's work fine.
> >
> > Somebody has an idea.
> >
> > Thanks
>
>
>