searching the list box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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" <[email protected]>
wrote in message
news:[email protected]...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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" <[email protected]>
wrote in message
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top