Searchable listbox using Combo lookup box

[Joe]

I have a form that uses a listbox to display data. In order to search the
data displayed in the listbox, I've put a combo box on the form.

When an identifier is entered in the combo box, it does a lookup from a
table. This makes sure that anything attempting to be searched is
legitimate, instead of just arbitrary text being entered. The lookup for
the combo box is working fine.

I need to put some code in the after update event on the combo box to use
the entered data to search a field (Itinerary) in the listbox and then have
the listbox display only records containing the entered text.

I've tried variations of the like statement with * but no success. Any
ideas?

 

[Duane Hookom]

The list box has a Row Source. You can use code in the after update event of
the combo box to edit the value of the Row Source.
Me.lboUnName.RowSource = "SELECT * FROM tblA WHERE [Itinerary]=""" &
Me.cboUnNamed & """"
This code assumes the Itinerary field is text. If it is numeric, remove a
couple pairs of quotes.

 

[JethroUK©]

why not cut out the middle man and set the row source of the combo so that
only legtimate entries are displyed/chosen

 

[Joe]

I think we're getting close, but the statement didn't work.

My listbox is called TripList, which is based on a query, which is called
qryTripListforListBox, which is based on data contained in a table called
TripHeader.

All the qryTripListforListBox does is rename a field which is called
TripWhere in the TripHeader table to a field called Itinerary in the query.
I did this originally so that the column name in the listbox would reflect
more of what the data actually is. (Probably shouldn't have designed the
table the way I did.)

Now...when the listbox first opens, all of the trips are listed. I thought
it would be a good idea to have a combo box which the user can type in an ID
and "find" any trips that have that ID contained somewhere in the Itinerary.
The Itinerary will have other data in it, too. But, whatever ID is entered
in the combo box has to be in the Itinerary, because the ID was used to
"build" the data for the Itinerary, else if it's not in the Itinerary I
would put up a message that says, "No records found."

In other words, I have to search through the Itinerary field and find that
ID and then display only those records.

Will some variant of what you proposed work with a LIKE statement?

The list box has a Row Source. You can use code in the after update event
of the combo box to edit the value of the Row Source.
Me.lboUnName.RowSource = "SELECT * FROM tblA WHERE [Itinerary]=""" &
Me.cboUnNamed & """"
This code assumes the Itinerary field is text. If it is numeric, remove a
couple pairs of quotes.

--
Duane Hookom
MS Access MVP

I have a form that uses a listbox to display data. In order to search the
data displayed in the listbox, I've put a combo box on the form.

When an identifier is entered in the combo box, it does a lookup from a
table. This makes sure that anything attempting to be searched is
legitimate, instead of just arbitrary text being entered. The lookup for
the combo box is working fine.

I need to put some code in the after update event on the combo box to use
the entered data to search a field (Itinerary) in the listbox and then
have the listbox display only records containing the entered text.

I've tried variations of the like statement with * but no success. Any
ideas?

 

[Joe]

The combo box is working fine. It does show the correct information. The
concept is taking what the user enters in the combo box and searching a
field called Itinerary in the triplist box to find any records that contain
that data.

 

[Duane Hookom]

Just reply with your code, your row source, and what you want the code to
do.

--
Duane Hookom
MS Access MVP

I think we're getting close, but the statement didn't work.

My listbox is called TripList, which is based on a query, which is called
qryTripListforListBox, which is based on data contained in a table called
TripHeader.

All the qryTripListforListBox does is rename a field which is called
TripWhere in the TripHeader table to a field called Itinerary in the
query. I did this originally so that the column name in the listbox would
reflect more of what the data actually is. (Probably shouldn't have
designed the table the way I did.)

Now...when the listbox first opens, all of the trips are listed. I
thought it would be a good idea to have a combo box which the user can
type in an ID and "find" any trips that have that ID contained somewhere
in the Itinerary. The Itinerary will have other data in it, too. But,
whatever ID is entered in the combo box has to be in the Itinerary,
because the ID was used to "build" the data for the Itinerary, else if
it's not in the Itinerary I would put up a message that says, "No records
found."

In other words, I have to search through the Itinerary field and find that
ID and then display only those records.

Will some variant of what you proposed work with a LIKE statement?

The list box has a Row Source. You can use code in the after update event
of the combo box to edit the value of the Row Source.
Me.lboUnName.RowSource = "SELECT * FROM tblA WHERE [Itinerary]=""" &
Me.cboUnNamed & """"
This code assumes the Itinerary field is text. If it is numeric, remove a
couple pairs of quotes.

--
Duane Hookom
MS Access MVP

I have a form that uses a listbox to display data. In order to search
the data displayed in the listbox, I've put a combo box on the form.

When an identifier is entered in the combo box, it does a lookup from a
table. This makes sure that anything attempting to be searched is
legitimate, instead of just arbitrary text being entered. The lookup
for the combo box is working fine.

I need to put some code in the after update event on the combo box to
use the entered data to search a field (Itinerary) in the listbox and
then have the listbox display only records containing the entered text.

I've tried variations of the like statement with * but no success. Any
ideas?

 

[Joe]

For example, [Itinerary] = (ABC) ATLANTA, GA; (DEF) MIAMI, FL; (GHI) DALLAS,
TX

cbolocation is name of combo box, which allows user to lookup city with 3 or
4 character identifier, ie., ABC is Atlanta, GA

not code, but idea:

If cbolocation = ABC
find ABC in [Itinerary]
If ABC not found
display "No Records Found"

row source for listbox (TripList) is: tripnumber, tripdate, itinerary,
remarks

Just reply with your code, your row source, and what you want the code to
do.

--
Duane Hookom
MS Access MVP

I think we're getting close, but the statement didn't work.

My listbox is called TripList, which is based on a query, which is called
qryTripListforListBox, which is based on data contained in a table called
TripHeader.

All the qryTripListforListBox does is rename a field which is called
TripWhere in the TripHeader table to a field called Itinerary in the
query. I did this originally so that the column name in the listbox would
reflect more of what the data actually is. (Probably shouldn't have
designed the table the way I did.)

Now...when the listbox first opens, all of the trips are listed. I
thought it would be a good idea to have a combo box which the user can
type in an ID and "find" any trips that have that ID contained somewhere
in the Itinerary. The Itinerary will have other data in it, too. But,
whatever ID is entered in the combo box has to be in the Itinerary,
because the ID was used to "build" the data for the Itinerary, else if
it's not in the Itinerary I would put up a message that says, "No records
found."

In other words, I have to search through the Itinerary field and find
that ID and then display only those records.

Will some variant of what you proposed work with a LIKE statement?

The list box has a Row Source. You can use code in the after update
event of the combo box to edit the value of the Row Source.
Me.lboUnName.RowSource = "SELECT * FROM tblA WHERE [Itinerary]=""" &
Me.cboUnNamed & """"
This code assumes the Itinerary field is text. If it is numeric, remove
a couple pairs of quotes.

--
Duane Hookom
MS Access MVP

I have a form that uses a listbox to display data. In order to search
the data displayed in the listbox, I've put a combo box on the form.

When an identifier is entered in the combo box, it does a lookup from a
table. This makes sure that anything attempting to be searched is
legitimate, instead of just arbitrary text being entered. The lookup
for the combo box is working fine.

I need to put some code in the after update event on the combo box to
use the entered data to search a field (Itinerary) in the listbox and
then have the listbox display only records containing the entered text.

I've tried variations of the like statement with * but no success. Any
ideas?

 

[warrior]

I think we're getting close, but the statement didn't work.

My listbox is called TripList, which is based on a query, which is called
qryTripListforListBox, which is based on data contained in a table called
TripHeader.

All the qryTripListforListBox does is rename a field which is called
TripWhere in the TripHeader table to a field called Itinerary in the query.
I did this originally so that the column name in the listbox would reflect
more of what the data actually is. (Probably shouldn't have designed the
table the way I did.)

Now...when the listbox first opens, all of the trips are listed. I thought
it would be a good idea to have a combo box which the user can type in an ID
and "find" any trips that have that ID contained somewhere in the Itinerary.
The Itinerary will have other data in it, too. But, whatever ID is entered
in the combo box has to be in the Itinerary, because the ID was used to
"build" the data for the Itinerary, else if it's not in the Itinerary I
would put up a message that says, "No records found."

In other words, I have to search through the Itinerary field and find that
ID and then display only those records.

Will some variant of what you proposed work with a LIKE statement?

The list box has a Row Source. You can use code in the after update event
of the combo box to edit the value of the Row Source.
Me.lboUnName.RowSource = "SELECT * FROM tblA WHERE [Itinerary]=""" &
Me.cboUnNamed & """"
This code assumes the Itinerary field is text. If it is numeric, remove a
couple pairs of quotes.

--
Duane Hookom
MS Access MVP

I have a form that uses a listbox to display data. In order to search the
data displayed in the listbox, I've put a combo box on the form.

When an identifier is entered in the combo box, it does a lookup from a
table. This makes sure that anything attempting to be searched is
legitimate, instead of just arbitrary text being entered. The lookup for
the combo box is working fine.

I need to put some code in the after update event on the combo box to use
the entered data to search a field (Itinerary) in the listbox and then
have the listbox display only records containing the entered text.

I've tried variations of the like statement with * but no success. Any
ideas?

If I understand you correctly, your listbox is based on a query and has a field name Itinerary. If so, in your query under [Itinerary] criteria reference your combobox ID field. Forms!frmname!comboboxname!ID

 

[Duane Hookom]

A Row Source is not a field list, it is a query name, table name, or SQL
statement.

Use the after update of the cboLocation like

Me.TripList.RowSource = "SELECT * FROM qselYourQuery " & _
"WHERE [Itinerary] Like ""*" & Me.cboLocation & "*"""


--
Duane Hookom
MS Access MVP

For example, [Itinerary] = (ABC) ATLANTA, GA; (DEF) MIAMI, FL; (GHI)
DALLAS, TX

cbolocation is name of combo box, which allows user to lookup city with 3
or 4 character identifier, ie., ABC is Atlanta, GA

not code, but idea:

If cbolocation = ABC
find ABC in [Itinerary]
If ABC not found
display "No Records Found"

row source for listbox (TripList) is: tripnumber, tripdate, itinerary,
remarks

Just reply with your code, your row source, and what you want the code to
do.

--
Duane Hookom
MS Access MVP

I think we're getting close, but the statement didn't work.

My listbox is called TripList, which is based on a query, which is
called qryTripListforListBox, which is based on data contained in a
table called TripHeader.

All the qryTripListforListBox does is rename a field which is called
TripWhere in the TripHeader table to a field called Itinerary in the
query. I did this originally so that the column name in the listbox
would reflect more of what the data actually is. (Probably shouldn't
have designed the table the way I did.)

Now...when the listbox first opens, all of the trips are listed. I
thought it would be a good idea to have a combo box which the user can
type in an ID and "find" any trips that have that ID contained somewhere
in the Itinerary. The Itinerary will have other data in it, too. But,
whatever ID is entered in the combo box has to be in the Itinerary,
because the ID was used to "build" the data for the Itinerary, else if
it's not in the Itinerary I would put up a message that says, "No
records found."

In other words, I have to search through the Itinerary field and find
that ID and then display only those records.

Will some variant of what you proposed work with a LIKE statement?

"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
The list box has a Row Source. You can use code in the after update
event of the combo box to edit the value of the Row Source.
Me.lboUnName.RowSource = "SELECT * FROM tblA WHERE [Itinerary]=""" &
Me.cboUnNamed & """"
This code assumes the Itinerary field is text. If it is numeric, remove
a couple pairs of quotes.

--
Duane Hookom
MS Access MVP

I have a form that uses a listbox to display data. In order to search
the data displayed in the listbox, I've put a combo box on the form.

When an identifier is entered in the combo box, it does a lookup from
a table. This makes sure that anything attempting to be searched is
legitimate, instead of just arbitrary text being entered. The lookup
for the combo box is working fine.

I need to put some code in the after update event on the combo box to
use the entered data to search a field (Itinerary) in the listbox and
then have the listbox display only records containing the entered
text.

I've tried variations of the like statement with * but no success.
Any ideas?

 

[Joe]

That worked. Thank you.

A Row Source is not a field list, it is a query name, table name, or SQL
statement.

Use the after update of the cboLocation like

Me.TripList.RowSource = "SELECT * FROM qselYourQuery " & _
"WHERE [Itinerary] Like ""*" & Me.cboLocation & "*"""


--
Duane Hookom
MS Access MVP

For example, [Itinerary] = (ABC) ATLANTA, GA; (DEF) MIAMI, FL; (GHI)
DALLAS, TX

cbolocation is name of combo box, which allows user to lookup city with 3
or 4 character identifier, ie., ABC is Atlanta, GA

not code, but idea:

If cbolocation = ABC
find ABC in [Itinerary]
If ABC not found
display "No Records Found"

row source for listbox (TripList) is: tripnumber, tripdate, itinerary,
remarks

Just reply with your code, your row source, and what you want the code
to do.

--
Duane Hookom
MS Access MVP

I think we're getting close, but the statement didn't work.

My listbox is called TripList, which is based on a query, which is
called qryTripListforListBox, which is based on data contained in a
table called TripHeader.

All the qryTripListforListBox does is rename a field which is called
TripWhere in the TripHeader table to a field called Itinerary in the
query. I did this originally so that the column name in the listbox
would reflect more of what the data actually is. (Probably shouldn't
have designed the table the way I did.)

Now...when the listbox first opens, all of the trips are listed. I
thought it would be a good idea to have a combo box which the user can
type in an ID and "find" any trips that have that ID contained
somewhere in the Itinerary. The Itinerary will have other data in it,
too. But, whatever ID is entered in the combo box has to be in the
Itinerary, because the ID was used to "build" the data for the
Itinerary, else if it's not in the Itinerary I would put up a message
that says, "No records found."

In other words, I have to search through the Itinerary field and find
that ID and then display only those records.

Will some variant of what you proposed work with a LIKE statement?

"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
The list box has a Row Source. You can use code in the after update
event of the combo box to edit the value of the Row Source.
Me.lboUnName.RowSource = "SELECT * FROM tblA WHERE [Itinerary]="""
& Me.cboUnNamed & """"
This code assumes the Itinerary field is text. If it is numeric,
remove a couple pairs of quotes.

--
Duane Hookom
MS Access MVP

I have a form that uses a listbox to display data. In order to search
the data displayed in the listbox, I've put a combo box on the form.

When an identifier is entered in the combo box, it does a lookup from
a table. This makes sure that anything attempting to be searched is
legitimate, instead of just arbitrary text being entered. The lookup
for the combo box is working fine.

I need to put some code in the after update event on the combo box to
use the entered data to search a field (Itinerary) in the listbox and
then have the listbox display only records containing the entered
text.

I've tried variations of the like statement with * but no success.
Any ideas?

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George