Hi all...I am developing this search form where it has txtCassID as unbound
text box and lstResults as list box. On the open event of the form I populate
my list box with a query that has customer name, id, movie, rent date and
cass id. What I would like to do is in a text box end user can type cass id
and as they are typing the focus on list box should be directed towards the
cass id they are typing. I mean having the background color different from
the rest of the records so it can be easily spotted. I am not able to do
this. Below is my code on change event of text box.


Dim strSQL
Dim txtSearching

txtSearching = Me.txtCassID.Text

strSQL = "SELECT tblMovie.Movie_Name AS Movie,
tblRentalTransaction.Movie_CaseID AS [Cas/DVD ID], tblCustomer.Customer_ID &
' ' & [Customer_LastName] & ',' & ' ' & [Customer_FirstName] AS Customer,
tblCustomer.Customer_Phone AS Phone, tblRentalTransaction.Movie_OutDate AS
RentDt, tblRentalTransaction.Movie_ProInDate AS [Expected Return Date]"
strSQL = strSQL & "FROM tblMovie INNER JOIN (tblRentalTransaction INNER
JOIN tblCustomer ON tblRentalTransaction.Customer_ID =
tblCustomer.Customer_ID) ON tblMovie.Movie_SerialNumber =
tblRentalTransaction.Movie_SerialNumber"
strSQL = strSQL & "where tblRentalTransaction.Movie_CaseID Like '" &
txtSearching & "*' "

Me.lstRentalList.RowSource = strSQL
Me.lstRentalList.Requery

Why not just use a Combo Box with the Auto Expand property set to Yes.
What you are trying to do will be very slow to execute. There would be a
significant delay between each keystroke. Most people can type faster than
this will execute.


"learningMikey" wrote:

> Hi all...I am developing this search form where it has txtCassID as unbound
> text box and lstResults as list box. On the open event of the form I populate
> my list box with a query that has customer name, id, movie, rent date and
> cass id. What I would like to do is in a text box end user can type cass id
> and as they are typing the focus on list box should be directed towards the
> cass id they are typing. I mean having the background color different from
> the rest of the records so it can be easily spotted. I am not able to do
> this. Below is my code on change event of text box.
>
>
> Dim strSQL
> Dim txtSearching
>
> txtSearching = Me.txtCassID.Text
>
> strSQL = "SELECT tblMovie.Movie_Name AS Movie,
> tblRentalTransaction.Movie_CaseID AS [Cas/DVD ID], tblCustomer.Customer_ID &
> ' ' & [Customer_LastName] & ',' & ' ' & [Customer_FirstName] AS Customer,
> tblCustomer.Customer_Phone AS Phone, tblRentalTransaction.Movie_OutDate AS
> RentDt, tblRentalTransaction.Movie_ProInDate AS [Expected Return Date]"
> strSQL = strSQL & "FROM tblMovie INNER JOIN (tblRentalTransaction INNER
> JOIN tblCustomer ON tblRentalTransaction.Customer_ID =
> tblCustomer.Customer_ID) ON tblMovie.Movie_SerialNumber =
> tblRentalTransaction.Movie_SerialNumber"
> strSQL = strSQL & "where tblRentalTransaction.Movie_CaseID Like '" &
> txtSearching & "*' "
>
> Me.lstRentalList.RowSource = strSQL
> Me.lstRentalList.Requery
>
>

Mikey,

Try setting this up this pick list using a combobox instead
of a listbox control. Behaviour very similar to what you
seem to be trying to do (autofill) is built into the
combobox naturally. If you put "Me.Dropdown" into the
GotFocus event of the combobox it will expand your list and
the user can see the choices being narrowed as they type.

--
Gary Miller
Sisters, OR



"learningMikey" <(E-Mail Removed)>
wrote in message
news:AABFD534-123D-4AA8-8283-(E-Mail Removed)...
> Hi all...I am developing this search form where it has
> txtCassID as unbound
> text box and lstResults as list box. On the open event of
> the form I populate
> my list box with a query that has customer name, id,
> movie, rent date and
> cass id. What I would like to do is in a text box end
> user can type cass id
> and as they are typing the focus on list box should be
> directed towards the
> cass id they are typing. I mean having the background
> color different from
> the rest of the records so it can be easily spotted. I am
> not able to do
> this. Below is my code on change event of text box.
>
>
> Dim strSQL
> Dim txtSearching
>
> txtSearching = Me.txtCassID.Text
>
> strSQL = "SELECT tblMovie.Movie_Name AS Movie,
> tblRentalTransaction.Movie_CaseID AS [Cas/DVD ID],
> tblCustomer.Customer_ID &
> ' ' & [Customer_LastName] & ',' & ' ' &
> [Customer_FirstName] AS Customer,
> tblCustomer.Customer_Phone AS Phone,
> tblRentalTransaction.Movie_OutDate AS
> RentDt, tblRentalTransaction.Movie_ProInDate AS [Expected
> Return Date]"
> strSQL = strSQL & "FROM tblMovie INNER JOIN
> (tblRentalTransaction INNER
> JOIN tblCustomer ON tblRentalTransaction.Customer_ID =
> tblCustomer.Customer_ID) ON tblMovie.Movie_SerialNumber =
> tblRentalTransaction.Movie_SerialNumber"
> strSQL = strSQL & "where
> tblRentalTransaction.Movie_CaseID Like '" &
> txtSearching & "*' "
>
> Me.lstRentalList.RowSource = strSQL
> Me.lstRentalList.Requery
>
>

Klatuu & Gary,

Thanks for your comments. I am aware I can do this by drop-down but this
form is Video Rental entry screen. Where it displays all outstanding movies
that have not yet been returned. So end-user can type in the movie cassette
id in text box and below in the list box the focus will show exactly the same
cassette that was rented and is now being returned. All of it together I have
6 columns in list box. Having all this in drop-down box does not make the
design good.

Mikey



"Klatuu" wrote:

> Why not just use a Combo Box with the Auto Expand property set to Yes.
> What you are trying to do will be very slow to execute. There would be a
> significant delay between each keystroke. Most people can type faster than
> this will execute.
>
>
> "learningMikey" wrote:
>
> > Hi all...I am developing this search form where it has txtCassID as unbound
> > text box and lstResults as list box. On the open event of the form I populate
> > my list box with a query that has customer name, id, movie, rent date and
> > cass id. What I would like to do is in a text box end user can type cass id
> > and as they are typing the focus on list box should be directed towards the
> > cass id they are typing. I mean having the background color different from
> > the rest of the records so it can be easily spotted. I am not able to do
> > this. Below is my code on change event of text box.
> >
> >
> > Dim strSQL
> > Dim txtSearching
> >
> > txtSearching = Me.txtCassID.Text
> >
> > strSQL = "SELECT tblMovie.Movie_Name AS Movie,
> > tblRentalTransaction.Movie_CaseID AS [Cas/DVD ID], tblCustomer.Customer_ID &
> > ' ' & [Customer_LastName] & ',' & ' ' & [Customer_FirstName] AS Customer,
> > tblCustomer.Customer_Phone AS Phone, tblRentalTransaction.Movie_OutDate AS
> > RentDt, tblRentalTransaction.Movie_ProInDate AS [Expected Return Date]"
> > strSQL = strSQL & "FROM tblMovie INNER JOIN (tblRentalTransaction INNER
> > JOIN tblCustomer ON tblRentalTransaction.Customer_ID =
> > tblCustomer.Customer_ID) ON tblMovie.Movie_SerialNumber =
> > tblRentalTransaction.Movie_SerialNumber"
> > strSQL = strSQL & "where tblRentalTransaction.Movie_CaseID Like '" &
> > txtSearching & "*' "
> >
> > Me.lstRentalList.RowSource = strSQL
> > Me.lstRentalList.Requery
> >
> >

Mikey,

As I see it you are much better off having them enter into a
combobox where you can control the fact that what they are
entering is a valid cassetteID through the limit to list
feature rather than letting them enter invalid ID's into a
textbox. You could populate the combobox with either your
list of cassette ID's or even cassette ID's that have not
been returned.

Personally I don't see that much difference between a 6
column combobox and a 6 column listbox if you feel that you
need to display that much data that way. Not knowing
anything about your structure I can't give you specific
advice, but sometimes subforms can be used very efficiently
to display a lot of details. You can certainly link a
subform to the choice on the combobox. There are probably a
lot of different approaches that can be used depending on
how you intend to flag them as returned.

--
Gary Miller
Sisters, OR



"learningMikey" <(E-Mail Removed)>
wrote in message
news1E6A0D7-F185-4670-835F-(E-Mail Removed)...
> Klatuu & Gary,
>
> Thanks for your comments. I am aware I can do this by
> drop-down but this
> form is Video Rental entry screen. Where it displays all
> outstanding movies
> that have not yet been returned. So end-user can type in
> the movie cassette
> id in text box and below in the list box the focus will
> show exactly the same
> cassette that was rented and is now being returned. All of
> it together I have
> 6 columns in list box. Having all this in drop-down box
> does not make the
> design good.
>
> Mikey
>
>
>
> "Klatuu" wrote:
>
>> Why not just use a Combo Box with the Auto Expand
>> property set to Yes.
>> What you are trying to do will be very slow to execute.
>> There would be a
>> significant delay between each keystroke. Most people
>> can type faster than
>> this will execute.
>>
>>
>> "learningMikey" wrote:
>>
>> > Hi all...I am developing this search form where it has
>> > txtCassID as unbound
>> > text box and lstResults as list box. On the open event
>> > of the form I populate
>> > my list box with a query that has customer name, id,
>> > movie, rent date and
>> > cass id. What I would like to do is in a text box end
>> > user can type cass id
>> > and as they are typing the focus on list box should be
>> > directed towards the
>> > cass id they are typing. I mean having the background
>> > color different from
>> > the rest of the records so it can be easily spotted. I
>> > am not able to do
>> > this. Below is my code on change event of text box.
>> >
>> >
>> > Dim strSQL
>> > Dim txtSearching
>> >
>> > txtSearching = Me.txtCassID.Text
>> >
>> > strSQL = "SELECT tblMovie.Movie_Name AS Movie,
>> > tblRentalTransaction.Movie_CaseID AS [Cas/DVD ID],
>> > tblCustomer.Customer_ID &
>> > ' ' & [Customer_LastName] & ',' & ' ' &
>> > [Customer_FirstName] AS Customer,
>> > tblCustomer.Customer_Phone AS Phone,
>> > tblRentalTransaction.Movie_OutDate AS
>> > RentDt, tblRentalTransaction.Movie_ProInDate AS
>> > [Expected Return Date]"
>> > strSQL = strSQL & "FROM tblMovie INNER JOIN
>> > (tblRentalTransaction INNER
>> > JOIN tblCustomer ON tblRentalTransaction.Customer_ID =
>> > tblCustomer.Customer_ID) ON tblMovie.Movie_SerialNumber
>> > =
>> > tblRentalTransaction.Movie_SerialNumber"
>> > strSQL = strSQL & "where
>> > tblRentalTransaction.Movie_CaseID Like '" &
>> > txtSearching & "*' "
>> >
>> > Me.lstRentalList.RowSource = strSQL
>> > Me.lstRentalList.Requery
>> >
>> >

The design is actually much better using the combo box. You can do
everything you are trying to do with the list box.
The combo box can be multiple columns
If you want them to be able to see everything, just use the DropDown method
of the combo box and they will see everything in the combo box - if it will
fit, but the same would be true of a list box.

Again, the code you originally posted would never run fast enough. If you
have to execute a query after each key stroke, it will be an abismal failure.

Believe me, I am trying to help

"learningMikey" wrote:

> Klatuu & Gary,
>
> Thanks for your comments. I am aware I can do this by drop-down but this
> form is Video Rental entry screen. Where it displays all outstanding movies
> that have not yet been returned. So end-user can type in the movie cassette
> id in text box and below in the list box the focus will show exactly the same
> cassette that was rented and is now being returned. All of it together I have
> 6 columns in list box. Having all this in drop-down box does not make the
> design good.
>
> Mikey
>
>
>
> "Klatuu" wrote:
>
> > Why not just use a Combo Box with the Auto Expand property set to Yes.
> > What you are trying to do will be very slow to execute. There would be a
> > significant delay between each keystroke. Most people can type faster than
> > this will execute.
> >
> >
> > "learningMikey" wrote:
> >
> > > Hi all...I am developing this search form where it has txtCassID as unbound
> > > text box and lstResults as list box. On the open event of the form I populate
> > > my list box with a query that has customer name, id, movie, rent date and
> > > cass id. What I would like to do is in a text box end user can type cass id
> > > and as they are typing the focus on list box should be directed towards the
> > > cass id they are typing. I mean having the background color different from
> > > the rest of the records so it can be easily spotted. I am not able to do
> > > this. Below is my code on change event of text box.
> > >
> > >
> > > Dim strSQL
> > > Dim txtSearching
> > >
> > > txtSearching = Me.txtCassID.Text
> > >
> > > strSQL = "SELECT tblMovie.Movie_Name AS Movie,
> > > tblRentalTransaction.Movie_CaseID AS [Cas/DVD ID], tblCustomer.Customer_ID &
> > > ' ' & [Customer_LastName] & ',' & ' ' & [Customer_FirstName] AS Customer,
> > > tblCustomer.Customer_Phone AS Phone, tblRentalTransaction.Movie_OutDate AS
> > > RentDt, tblRentalTransaction.Movie_ProInDate AS [Expected Return Date]"
> > > strSQL = strSQL & "FROM tblMovie INNER JOIN (tblRentalTransaction INNER
> > > JOIN tblCustomer ON tblRentalTransaction.Customer_ID =
> > > tblCustomer.Customer_ID) ON tblMovie.Movie_SerialNumber =
> > > tblRentalTransaction.Movie_SerialNumber"
> > > strSQL = strSQL & "where tblRentalTransaction.Movie_CaseID Like '" &
> > > txtSearching & "*' "
> > >
> > > Me.lstRentalList.RowSource = strSQL
> > > Me.lstRentalList.Requery
> > >
> > >