Combo box woes

Hi Ken,

I am trying to take a combination of firstname, lastname, and custid to use
for entry into a call log for our home spun Help Desk DB. It is currently in
a wide open search by form which allows the HD users to find a caller by
firstname, lastname or even by company.

I could be happy with a cascading combo box grouping, but with that could it
go from cb1 to cb2 and vice versa? We have some callers who have very
difficult lastnames to spell so we sometimes search on firstname. If I could
get to the point of doing this via a combo I would be very happy.

If you could give me some direction on the multiple combo boxes that would
be great!

Chris Hammer

"Ken Snell [MVP]" wrote:

This cannot be done directly in a combo box. You would need to either use
two separate combo boxes (one for last name and one for first name, where
the second combo box is cascading from the first one, meaning that the list
of first names would be filtered by what is actually selected in the first
combo box), or you would need to use two textboxes to enter the desired
search letters and then click a button to conduct the search.

Perhaps, if you provide more details about the purpose and need for the
search, and how the result is to be used, we can suggest some alternative
ideas.
--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Ken,

The way it works now is that I can enter the first letter or two of the
customers last name in the combo box so that I can quickly get to the m's
for
example. I need to find lastname matches along with firstname. This is
going to replace the current form search that we have in place described
below.

Our current version of customer search allows for searching on first and
lastnames. The old way is done on a form, then broght to another form.
On
mine I am trying to streamline, but the users aren't happy about losing
functionality.

Thanks,
Chris

"Ken Snell [MVP]" wrote:

Define "searchable".... what are you trying to do?

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi all,

I am using a combo box to concantenate 3 fields (custid, firstname,
lastname) I am holding the values as entered in my table just fine. My
question is how can I make more than one column searchable in the
concantenation. I am able to search on lastname only as this is the
first
listed field. I would like to search on first and last names.

Thanks for any advice that may come.

Chris Hammer

If you want to search various fields, then I would write VBA code in the
form that searches the table itself for a match, and that returns the
appropriate primary key value so that you can set the combo box to that
value. Searching the combo box itself is just adding one more step in the
process and is a bit trickier to do.

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi Ken,

I am trying to take a combination of firstname, lastname, and custid to
use
for entry into a call log for our home spun Help Desk DB. It is currently
in
a wide open search by form which allows the HD users to find a caller by
firstname, lastname or even by company.

I could be happy with a cascading combo box grouping, but with that could
it
go from cb1 to cb2 and vice versa? We have some callers who have very
difficult lastnames to spell so we sometimes search on firstname. If I
could
get to the point of doing this via a combo I would be very happy.

If you could give me some direction on the multiple combo boxes that would
be great!

Chris Hammer

"Ken Snell [MVP]" wrote:

This cannot be done directly in a combo box. You would need to either use
two separate combo boxes (one for last name and one for first name, where
the second combo box is cascading from the first one, meaning that the
list
of first names would be filtered by what is actually selected in the
first
combo box), or you would need to use two textboxes to enter the desired
search letters and then click a button to conduct the search.

Perhaps, if you provide more details about the purpose and need for the
search, and how the result is to be used, we can suggest some alternative
ideas.
--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Ken,

The way it works now is that I can enter the first letter or two of the
customers last name in the combo box so that I can quickly get to the
m's
for
example. I need to find lastname matches along with firstname. This
is
going to replace the current form search that we have in place
described
below.

Our current version of customer search allows for searching on first
and
lastnames. The old way is done on a form, then broght to another form.
On
mine I am trying to streamline, but the users aren't happy about losing
functionality.

Thanks,
Chris

"Ken Snell [MVP]" wrote:

Define "searchable".... what are you trying to do?

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi all,

I am using a combo box to concantenate 3 fields (custid, firstname,
lastname) I am holding the values as entered in my table just fine.
My
question is how can I make more than one column searchable in the
concantenation. I am able to search on lastname only as this is the
first
listed field. I would like to search on first and last names.

Thanks for any advice that may come.

Chris Hammer

Okay, what would be an example of the code and how would I place the values
in my entry form?

"Ken Snell [MVP]" wrote:

If you want to search various fields, then I would write VBA code in the
form that searches the table itself for a match, and that returns the
appropriate primary key value so that you can set the combo box to that
value. Searching the combo box itself is just adding one more step in the
process and is a bit trickier to do.

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi Ken,

I am trying to take a combination of firstname, lastname, and custid to
use
for entry into a call log for our home spun Help Desk DB. It is currently
in
a wide open search by form which allows the HD users to find a caller by
firstname, lastname or even by company.

I could be happy with a cascading combo box grouping, but with that could
it
go from cb1 to cb2 and vice versa? We have some callers who have very
difficult lastnames to spell so we sometimes search on firstname. If I
could
get to the point of doing this via a combo I would be very happy.

If you could give me some direction on the multiple combo boxes that would
be great!

Chris Hammer

"Ken Snell [MVP]" wrote:

This cannot be done directly in a combo box. You would need to either use
two separate combo boxes (one for last name and one for first name, where
the second combo box is cascading from the first one, meaning that the
list
of first names would be filtered by what is actually selected in the
first
combo box), or you would need to use two textboxes to enter the desired
search letters and then click a button to conduct the search.

Perhaps, if you provide more details about the purpose and need for the
search, and how the result is to be used, we can suggest some alternative
ideas.
--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Ken,

The way it works now is that I can enter the first letter or two of the
customers last name in the combo box so that I can quickly get to the
m's
for
example. I need to find lastname matches along with firstname. This
is
going to replace the current form search that we have in place
described
below.

Our current version of customer search allows for searching on first
and
lastnames. The old way is done on a form, then broght to another form.
On
mine I am trying to streamline, but the users aren't happy about losing
functionality.

Thanks,
Chris

"Ken Snell [MVP]" wrote:

Define "searchable".... what are you trying to do?

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi all,

I am using a combo box to concantenate 3 fields (custid, firstname,
lastname) I am holding the values as entered in my table just fine.
My
question is how can I make more than one column searchable in the
concantenation. I am able to search on lastname only as this is the
first
listed field. I would like to search on first and last names.

Thanks for any advice that may come.

Chris Hammer

The code will depend upon what you want it to do. Should the code find the
first match and select that person in the combo box? Should the code return
all the matches and let the user select from the filtered list (and if this
option, should the selection be made from the combo box or from another
control, e.g., a listbox?)?

Will the user enter a text string on which to match in either the first name
or the last name? or do you want searching to be possible by letting the
user enter a text string for a first name and a text string for a last name?

If you can identify what you want the form to do, then I can suggest some
programming to do it.
--

Ken Snell
MS ACCESS MVP



"hammey22" wrote in message
...
Okay, what would be an example of the code and how would I place the
values
in my entry form?

"Ken Snell [MVP]" wrote:

If you want to search various fields, then I would write VBA code in the
form that searches the table itself for a match, and that returns the
appropriate primary key value so that you can set the combo box to that
value. Searching the combo box itself is just adding one more step in the
process and is a bit trickier to do.

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi Ken,

I am trying to take a combination of firstname, lastname, and custid to
use
for entry into a call log for our home spun Help Desk DB. It is
currently
in
a wide open search by form which allows the HD users to find a caller
by
firstname, lastname or even by company.

I could be happy with a cascading combo box grouping, but with that
could
it
go from cb1 to cb2 and vice versa? We have some callers who have very
difficult lastnames to spell so we sometimes search on firstname. If I
could
get to the point of doing this via a combo I would be very happy.

If you could give me some direction on the multiple combo boxes that
would
be great!

Chris Hammer

"Ken Snell [MVP]" wrote:

This cannot be done directly in a combo box. You would need to either
use
two separate combo boxes (one for last name and one for first name,
where
the second combo box is cascading from the first one, meaning that the
list
of first names would be filtered by what is actually selected in the
first
combo box), or you would need to use two textboxes to enter the
desired
search letters and then click a button to conduct the search.

Perhaps, if you provide more details about the purpose and need for
the
search, and how the result is to be used, we can suggest some
alternative
ideas.
--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Ken,

The way it works now is that I can enter the first letter or two of
the
customers last name in the combo box so that I can quickly get to
the
m's
for
example. I need to find lastname matches along with firstname.
This
is
going to replace the current form search that we have in place
described
below.

Our current version of customer search allows for searching on first
and
lastnames. The old way is done on a form, then broght to another
form.
On
mine I am trying to streamline, but the users aren't happy about
losing
functionality.

Thanks,
Chris

"Ken Snell [MVP]" wrote:

Define "searchable".... what are you trying to do?

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi all,

I am using a combo box to concantenate 3 fields (custid,
firstname,
lastname) I am holding the values as entered in my table just
fine.
My
question is how can I make more than one column searchable in the
concantenation. I am able to search on lastname only as this is
the
first
listed field. I would like to search on first and last names.

Thanks for any advice that may come.

Chris Hammer

Ken,

I would like to allow the user to enter a few characters and return all
possible matches from the partial. To me, if I am going to be doing a search
like this then I do not have to display the results in a combo box unless
that is the wisest thing to do. Really, all I am trying to accomplish is to
convert our call entry screen from a multiple screen slow to navigate app
into a one screen streamlined entry form.

An example of what I am trying to get is say a user enters in "john" I would
like to have the results that they get back display all firstname matches of
"john" and any lastname matches including "john" as all or part of the
string. Then when they select the person they want, I would like to have it
display their first and last names.

Thanks,

Chris

"Ken Snell [MVP]" wrote:

The code will depend upon what you want it to do. Should the code find the
first match and select that person in the combo box? Should the code return
all the matches and let the user select from the filtered list (and if this
option, should the selection be made from the combo box or from another
control, e.g., a listbox?)?

Will the user enter a text string on which to match in either the first name
or the last name? or do you want searching to be possible by letting the
user enter a text string for a first name and a text string for a last name?

If you can identify what you want the form to do, then I can suggest some
programming to do it.
--

Ken Snell
MS ACCESS MVP



"hammey22" wrote in message
...
Okay, what would be an example of the code and how would I place the
values
in my entry form?

"Ken Snell [MVP]" wrote:

If you want to search various fields, then I would write VBA code in the
form that searches the table itself for a match, and that returns the
appropriate primary key value so that you can set the combo box to that
value. Searching the combo box itself is just adding one more step in the
process and is a bit trickier to do.

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi Ken,

I am trying to take a combination of firstname, lastname, and custid to
use
for entry into a call log for our home spun Help Desk DB. It is
currently
in
a wide open search by form which allows the HD users to find a caller
by
firstname, lastname or even by company.

I could be happy with a cascading combo box grouping, but with that
could
it
go from cb1 to cb2 and vice versa? We have some callers who have very
difficult lastnames to spell so we sometimes search on firstname. If I
could
get to the point of doing this via a combo I would be very happy.

If you could give me some direction on the multiple combo boxes that
would
be great!

Chris Hammer

"Ken Snell [MVP]" wrote:

This cannot be done directly in a combo box. You would need to either
use
two separate combo boxes (one for last name and one for first name,
where
the second combo box is cascading from the first one, meaning that the
list
of first names would be filtered by what is actually selected in the
first
combo box), or you would need to use two textboxes to enter the
desired
search letters and then click a button to conduct the search.

Perhaps, if you provide more details about the purpose and need for
the
search, and how the result is to be used, we can suggest some
alternative
ideas.
--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Ken,

The way it works now is that I can enter the first letter or two of
the
customers last name in the combo box so that I can quickly get to
the
m's
for
example. I need to find lastname matches along with firstname.
This
is
going to replace the current form search that we have in place
described
below.

Our current version of customer search allows for searching on first
and
lastnames. The old way is done on a form, then broght to another
form.
On
mine I am trying to streamline, but the users aren't happy about
losing
functionality.

Thanks,
Chris

"Ken Snell [MVP]" wrote:

Define "searchable".... what are you trying to do?

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi all,

I am using a combo box to concantenate 3 fields (custid,
firstname,
lastname) I am holding the values as entered in my table just
fine.
My
question is how can I make more than one column searchable in the
concantenation. I am able to search on lastname only as this is
the
first
listed field. I would like to search on first and last names.

Thanks for any advice that may come.

Chris Hammer

This setup will let you click a command button to allow the user to do the
search. Clicking the button will make a textbox visible, into which the user
will enter a string that is the search string. The user then presses either
Tab or Enter, and a list box will become visible that shows the results of
the search. The user double-clicks the name desired, and that name is chosen
in the combo box for the user.

I will assume that your table is named tblNames, and that the two name
fields are called FName and LName, and that the ID field is named NameID. I
also will assume that your initial combo box is named cboNames.

On your form, in the form header section, put a command button named cmdFind
(make its caption "Find"). Also put a textbox (name it txtString) in that
section, and a listbox (name it lstNames) in that section. Set the Visible
property of both txtString and lstNames to No.

Set these properties of the lstNames list box:
-- Column Count: 2
-- Column Heads: No
-- Column Widths: 0"; 3"
-- Row Source Type: Table/Query
-- Row Source: (empty)
-- Bound Column: 1

Use this generic code for the Click event of cmdFind:

Private Sub cmdFind_Click()
Me!txtString.Visible = True
Me!txtString.SetFocus
End Sub


Use this generic code for the AfterUpdate event of txtString:

Private Sub txtString_AfterUpdate()
Dim strQuery As String
strQuery = "SELECT NameID, " & _
"FName & "" "" & LName AS FullName " & _
"FROM tblNames " & _
"WHERE FName Like ""*" & _
Me!txtString.Value & "*"" OR " & _
"LName Like ""*" & Me!txtString.Value & _
"*"" ORDER BY LName;"
Me!lstNames.RowSource = strQuery
Me!lstNames.Visible = True
End Sub


Use this generic code for the DblClick event of lstNames:

Private Sub lstNames_DblClick(Cancel As Integer)
Me!cboNames.Value = Me!lstNames.Value
Me!cboNames.SetFocus
Me!txtString.Visible = False
Me!lstNames.Visible = False
End Sub


--

Ken Snell
MS ACCESS MVP



"hammey22" wrote in message
...
Ken,

I would like to allow the user to enter a few characters and return all
possible matches from the partial. To me, if I am going to be doing a
search
like this then I do not have to display the results in a combo box unless
that is the wisest thing to do. Really, all I am trying to accomplish is
to
convert our call entry screen from a multiple screen slow to navigate app
into a one screen streamlined entry form.

An example of what I am trying to get is say a user enters in "john" I
would
like to have the results that they get back display all firstname matches
of
"john" and any lastname matches including "john" as all or part of the
string. Then when they select the person they want, I would like to have
it
display their first and last names.

Thanks,

Chris

"Ken Snell [MVP]" wrote:

The code will depend upon what you want it to do. Should the code find
the
first match and select that person in the combo box? Should the code
return
all the matches and let the user select from the filtered list (and if
this
option, should the selection be made from the combo box or from another
control, e.g., a listbox?)?

Will the user enter a text string on which to match in either the first
name
or the last name? or do you want searching to be possible by letting the
user enter a text string for a first name and a text string for a last
name?

If you can identify what you want the form to do, then I can suggest some
programming to do it.
--

Ken Snell
MS ACCESS MVP



"hammey22" wrote in message
...
Okay, what would be an example of the code and how would I place the
values
in my entry form?

"Ken Snell [MVP]" wrote:

If you want to search various fields, then I would write VBA code in
the
form that searches the table itself for a match, and that returns the
appropriate primary key value so that you can set the combo box to
that
value. Searching the combo box itself is just adding one more step in
the
process and is a bit trickier to do.

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi Ken,

I am trying to take a combination of firstname, lastname, and custid
to
use
for entry into a call log for our home spun Help Desk DB. It is
currently
in
a wide open search by form which allows the HD users to find a
caller
by
firstname, lastname or even by company.

I could be happy with a cascading combo box grouping, but with that
could
it
go from cb1 to cb2 and vice versa? We have some callers who have
very
difficult lastnames to spell so we sometimes search on firstname.
If I
could
get to the point of doing this via a combo I would be very happy.

If you could give me some direction on the multiple combo boxes that
would
be great!

Chris Hammer

"Ken Snell [MVP]" wrote:

This cannot be done directly in a combo box. You would need to
either
use
two separate combo boxes (one for last name and one for first name,
where
the second combo box is cascading from the first one, meaning that
the
list
of first names would be filtered by what is actually selected in
the
first
combo box), or you would need to use two textboxes to enter the
desired
search letters and then click a button to conduct the search.

Perhaps, if you provide more details about the purpose and need for
the
search, and how the result is to be used, we can suggest some
alternative
ideas.
--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Ken,

The way it works now is that I can enter the first letter or two
of
the
customers last name in the combo box so that I can quickly get to
the
m's
for
example. I need to find lastname matches along with firstname.
This
is
going to replace the current form search that we have in place
described
below.

Our current version of customer search allows for searching on
first
and
lastnames. The old way is done on a form, then broght to another
form.
On
mine I am trying to streamline, but the users aren't happy about
losing
functionality.

Thanks,
Chris

"Ken Snell [MVP]" wrote:

Define "searchable".... what are you trying to do?

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi all,

I am using a combo box to concantenate 3 fields (custid,
firstname,
lastname) I am holding the values as entered in my table just
fine.
My
question is how can I make more than one column searchable in
the
concantenation. I am able to search on lastname only as this
is
the
first
listed field. I would like to search on first and last names.

Thanks for any advice that may come.

Chris Hammer

Thanks ken, this looks exactly like what I am hoping to do. I appreciate
your patience and persistence in helping me out.

"Ken Snell [MVP]" wrote:

This setup will let you click a command button to allow the user to do the
search. Clicking the button will make a textbox visible, into which the user
will enter a string that is the search string. The user then presses either
Tab or Enter, and a list box will become visible that shows the results of
the search. The user double-clicks the name desired, and that name is chosen
in the combo box for the user.

I will assume that your table is named tblNames, and that the two name
fields are called FName and LName, and that the ID field is named NameID. I
also will assume that your initial combo box is named cboNames.

On your form, in the form header section, put a command button named cmdFind
(make its caption "Find"). Also put a textbox (name it txtString) in that
section, and a listbox (name it lstNames) in that section. Set the Visible
property of both txtString and lstNames to No.

Set these properties of the lstNames list box:
-- Column Count: 2
-- Column Heads: No
-- Column Widths: 0"; 3"
-- Row Source Type: Table/Query
-- Row Source: (empty)
-- Bound Column: 1

Use this generic code for the Click event of cmdFind:

Private Sub cmdFind_Click()
Me!txtString.Visible = True
Me!txtString.SetFocus
End Sub


Use this generic code for the AfterUpdate event of txtString:

Private Sub txtString_AfterUpdate()
Dim strQuery As String
strQuery = "SELECT NameID, " & _
"FName & "" "" & LName AS FullName " & _
"FROM tblNames " & _
"WHERE FName Like ""*" & _
Me!txtString.Value & "*"" OR " & _
"LName Like ""*" & Me!txtString.Value & _
"*"" ORDER BY LName;"
Me!lstNames.RowSource = strQuery
Me!lstNames.Visible = True
End Sub


Use this generic code for the DblClick event of lstNames:

Private Sub lstNames_DblClick(Cancel As Integer)
Me!cboNames.Value = Me!lstNames.Value
Me!cboNames.SetFocus
Me!txtString.Visible = False
Me!lstNames.Visible = False
End Sub


--

Ken Snell
MS ACCESS MVP



"hammey22" wrote in message
...
Ken,

I would like to allow the user to enter a few characters and return all
possible matches from the partial. To me, if I am going to be doing a
search
like this then I do not have to display the results in a combo box unless
that is the wisest thing to do. Really, all I am trying to accomplish is
to
convert our call entry screen from a multiple screen slow to navigate app
into a one screen streamlined entry form.

An example of what I am trying to get is say a user enters in "john" I
would
like to have the results that they get back display all firstname matches
of
"john" and any lastname matches including "john" as all or part of the
string. Then when they select the person they want, I would like to have
it
display their first and last names.

Thanks,

Chris

"Ken Snell [MVP]" wrote:

The code will depend upon what you want it to do. Should the code find
the
first match and select that person in the combo box? Should the code
return
all the matches and let the user select from the filtered list (and if
this
option, should the selection be made from the combo box or from another
control, e.g., a listbox?)?

Will the user enter a text string on which to match in either the first
name
or the last name? or do you want searching to be possible by letting the
user enter a text string for a first name and a text string for a last
name?

If you can identify what you want the form to do, then I can suggest some
programming to do it.
--

Ken Snell
MS ACCESS MVP



"hammey22" wrote in message
...
Okay, what would be an example of the code and how would I place the
values
in my entry form?

"Ken Snell [MVP]" wrote:

If you want to search various fields, then I would write VBA code in
the
form that searches the table itself for a match, and that returns the
appropriate primary key value so that you can set the combo box to
that
value. Searching the combo box itself is just adding one more step in
the
process and is a bit trickier to do.

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi Ken,

I am trying to take a combination of firstname, lastname, and custid
to
use
for entry into a call log for our home spun Help Desk DB. It is
currently
in
a wide open search by form which allows the HD users to find a
caller
by
firstname, lastname or even by company.

I could be happy with a cascading combo box grouping, but with that
could
it
go from cb1 to cb2 and vice versa? We have some callers who have
very
difficult lastnames to spell so we sometimes search on firstname.
If I
could
get to the point of doing this via a combo I would be very happy.

If you could give me some direction on the multiple combo boxes that
would
be great!

Chris Hammer

"Ken Snell [MVP]" wrote:

This cannot be done directly in a combo box. You would need to
either
use
two separate combo boxes (one for last name and one for first name,
where
the second combo box is cascading from the first one, meaning that
the
list
of first names would be filtered by what is actually selected in
the
first
combo box), or you would need to use two textboxes to enter the
desired
search letters and then click a button to conduct the search.

Perhaps, if you provide more details about the purpose and need for
the
search, and how the result is to be used, we can suggest some
alternative
ideas.
--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Ken,

The way it works now is that I can enter the first letter or two
of
the
customers last name in the combo box so that I can quickly get to
the
m's
for
example. I need to find lastname matches along with firstname.
This
is
going to replace the current form search that we have in place
described
below.

Our current version of customer search allows for searching on
first
and
lastnames. The old way is done on a form, then broght to another
form.
On
mine I am trying to streamline, but the users aren't happy about
losing
functionality.

Thanks,
Chris

"Ken Snell [MVP]" wrote:

Define "searchable".... what are you trying to do?

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi all,

I am using a combo box to concantenate 3 fields (custid,
firstname,
lastname) I am holding the values as entered in my table just
fine.
My
question is how can I make more than one column searchable in
the
concantenation. I am able to search on lastname only as this
is
the
first
listed field. I would like to search on first and last names.

Thanks for any advice that may come.

Chris Hammer

Hi there,

I know it's a fair while after this post but I found Ken's response quite
helpful for something I've been trying to incorporate into a database of my
own, only I seem to be having a little trouble trying to get his solution to
work.

I've included the Find button, which when clicked makes my textbox visible
and below the textbox I have my listbox which becomes visible after I hit
enter to get out of the textbox - all working well!

The issue I'm having is that when I type a first name or surname into the
textbox (even ensuring that the match is absolutely exact), no results are
ever returned, I'm guessing it's not a problem with the code, but rather
something to do with the properties of either the list box or the text box
but I'm not entirely sure.

If I set the properties of the list box to visible, I can see the full names
of the people held in my database but I just don't seem to be able to connect
the list box to the things I'm typing into the text box, If anyone could
point out what I might have overlooked that would be fantastic.

I'd also ideally like to be able to double click a name in my list box or
click a button next to the list box to open that particular person's record
as a completed form.

Any help with this would be very much appreciated.

Thank you!

Duncan


"Ken Snell [MVP]" wrote:

This setup will let you click a command button to allow the user to do the
search. Clicking the button will make a textbox visible, into which the user
will enter a string that is the search string. The user then presses either
Tab or Enter, and a list box will become visible that shows the results of
the search. The user double-clicks the name desired, and that name is chosen
in the combo box for the user.

I will assume that your table is named tblNames, and that the two name
fields are called FName and LName, and that the ID field is named NameID. I
also will assume that your initial combo box is named cboNames.

On your form, in the form header section, put a command button named cmdFind
(make its caption "Find"). Also put a textbox (name it txtString) in that
section, and a listbox (name it lstNames) in that section. Set the Visible
property of both txtString and lstNames to No.

Set these properties of the lstNames list box:
-- Column Count: 2
-- Column Heads: No
-- Column Widths: 0"; 3"
-- Row Source Type: Table/Query
-- Row Source: (empty)
-- Bound Column: 1

Use this generic code for the Click event of cmdFind:

Private Sub cmdFind_Click()
Me!txtString.Visible = True
Me!txtString.SetFocus
End Sub


Use this generic code for the AfterUpdate event of txtString:

Private Sub txtString_AfterUpdate()
Dim strQuery As String
strQuery = "SELECT NameID, " & _
"FName & "" "" & LName AS FullName " & _
"FROM tblNames " & _
"WHERE FName Like ""*" & _
Me!txtString.Value & "*"" OR " & _
"LName Like ""*" & Me!txtString.Value & _
"*"" ORDER BY LName;"
Me!lstNames.RowSource = strQuery
Me!lstNames.Visible = True
End Sub


Use this generic code for the DblClick event of lstNames:

Private Sub lstNames_DblClick(Cancel As Integer)
Me!cboNames.Value = Me!lstNames.Value
Me!cboNames.SetFocus
Me!txtString.Visible = False
Me!lstNames.Visible = False
End Sub


--

Ken Snell
MS ACCESS MVP



"hammey22" wrote in message
...
Ken,

I would like to allow the user to enter a few characters and return all
possible matches from the partial. To me, if I am going to be doing a
search
like this then I do not have to display the results in a combo box unless
that is the wisest thing to do. Really, all I am trying to accomplish is
to
convert our call entry screen from a multiple screen slow to navigate app
into a one screen streamlined entry form.

An example of what I am trying to get is say a user enters in "john" I
would
like to have the results that they get back display all firstname matches
of
"john" and any lastname matches including "john" as all or part of the
string. Then when they select the person they want, I would like to have
it
display their first and last names.

Thanks,

Chris

"Ken Snell [MVP]" wrote:

The code will depend upon what you want it to do. Should the code find
the
first match and select that person in the combo box? Should the code
return
all the matches and let the user select from the filtered list (and if
this
option, should the selection be made from the combo box or from another
control, e.g., a listbox?)?

Will the user enter a text string on which to match in either the first
name
or the last name? or do you want searching to be possible by letting the
user enter a text string for a first name and a text string for a last
name?

If you can identify what you want the form to do, then I can suggest some
programming to do it.
--

Ken Snell
MS ACCESS MVP



"hammey22" wrote in message
...
Okay, what would be an example of the code and how would I place the
values
in my entry form?

"Ken Snell [MVP]" wrote:

If you want to search various fields, then I would write VBA code in
the
form that searches the table itself for a match, and that returns the
appropriate primary key value so that you can set the combo box to
that
value. Searching the combo box itself is just adding one more step in
the
process and is a bit trickier to do.

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi Ken,

I am trying to take a combination of firstname, lastname, and custid
to
use
for entry into a call log for our home spun Help Desk DB. It is
currently
in
a wide open search by form which allows the HD users to find a
caller
by
firstname, lastname or even by company.

I could be happy with a cascading combo box grouping, but with that
could
it
go from cb1 to cb2 and vice versa? We have some callers who have
very
difficult lastnames to spell so we sometimes search on firstname.
If I
could
get to the point of doing this via a combo I would be very happy.

If you could give me some direction on the multiple combo boxes that
would
be great!

Chris Hammer

"Ken Snell [MVP]" wrote:

This cannot be done directly in a combo box. You would need to
either
use
two separate combo boxes (one for last name and one for first name,
where
the second combo box is cascading from the first one, meaning that
the
list
of first names would be filtered by what is actually selected in
the
first
combo box), or you would need to use two textboxes to enter the
desired
search letters and then click a button to conduct the search.

Perhaps, if you provide more details about the purpose and need for
the
search, and how the result is to be used, we can suggest some
alternative
ideas.
--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Ken,

The way it works now is that I can enter the first letter or two
of
the
customers last name in the combo box so that I can quickly get to
the
m's
for
example. I need to find lastname matches along with firstname.
This
is
going to replace the current form search that we have in place
described
below.

Our current version of customer search allows for searching on
first
and
lastnames. The old way is done on a form, then broght to another
form.
On
mine I am trying to streamline, but the users aren't happy about
losing
functionality.

Thanks,
Chris

"Ken Snell [MVP]" wrote:

Define "searchable".... what are you trying to do?

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi all,

I am using a combo box to concantenate 3 fields (custid,
firstname,
lastname) I am holding the values as entered in my table just
fine.
My
question is how can I make more than one column searchable in
the
concantenation. I am able to search on lastname only as this
is
the
first
listed field. I would like to search on first and last names.

Thanks for any advice that may come.

Chris Hammer

Duncan

Is there a chance one of your tables/fields involved uses the "lookup" data
type? If so, what you see in the table and what is stored are two different
things. This leads to problems with queries, combo boxes, list boxes, etc.

--
Regards

Jeff Boyce
Office/Access MVP

"Duncan" wrote in message
...
Hi there,

I know it's a fair while after this post but I found Ken's response quite
helpful for something I've been trying to incorporate into a database of
my
own, only I seem to be having a little trouble trying to get his solution
to
work.

I've included the Find button, which when clicked makes my textbox visible
and below the textbox I have my listbox which becomes visible after I hit
enter to get out of the textbox - all working well!

The issue I'm having is that when I type a first name or surname into the
textbox (even ensuring that the match is absolutely exact), no results are
ever returned, I'm guessing it's not a problem with the code, but rather
something to do with the properties of either the list box or the text box
but I'm not entirely sure.

If I set the properties of the list box to visible, I can see the full
names
of the people held in my database but I just don't seem to be able to
connect
the list box to the things I'm typing into the text box, If anyone could
point out what I might have overlooked that would be fantastic.

I'd also ideally like to be able to double click a name in my list box or
click a button next to the list box to open that particular person's
record
as a completed form.

Any help with this would be very much appreciated.

Thank you!

Duncan


"Ken Snell [MVP]" wrote:

This setup will let you click a command button to allow the user to do
the
search. Clicking the button will make a textbox visible, into which the
user
will enter a string that is the search string. The user then presses
either
Tab or Enter, and a list box will become visible that shows the results
of
the search. The user double-clicks the name desired, and that name is
chosen
in the combo box for the user.

I will assume that your table is named tblNames, and that the two name
fields are called FName and LName, and that the ID field is named
NameID. I
also will assume that your initial combo box is named cboNames.

On your form, in the form header section, put a command button named
cmdFind
(make its caption "Find"). Also put a textbox (name it txtString) in
that
section, and a listbox (name it lstNames) in that section. Set the
Visible
property of both txtString and lstNames to No.

Set these properties of the lstNames list box:
-- Column Count: 2
-- Column Heads: No
-- Column Widths: 0"; 3"
-- Row Source Type: Table/Query
-- Row Source: (empty)
-- Bound Column: 1

Use this generic code for the Click event of cmdFind:

Private Sub cmdFind_Click()
Me!txtString.Visible = True
Me!txtString.SetFocus
End Sub


Use this generic code for the AfterUpdate event of txtString:

Private Sub txtString_AfterUpdate()
Dim strQuery As String
strQuery = "SELECT NameID, " & _
"FName & "" "" & LName AS FullName " & _
"FROM tblNames " & _
"WHERE FName Like ""*" & _
Me!txtString.Value & "*"" OR " & _
"LName Like ""*" & Me!txtString.Value & _
"*"" ORDER BY LName;"
Me!lstNames.RowSource = strQuery
Me!lstNames.Visible = True
End Sub


Use this generic code for the DblClick event of lstNames:

Private Sub lstNames_DblClick(Cancel As Integer)
Me!cboNames.Value = Me!lstNames.Value
Me!cboNames.SetFocus
Me!txtString.Visible = False
Me!lstNames.Visible = False
End Sub


--

Ken Snell
MS ACCESS MVP



"hammey22" wrote in message
...
Ken,

I would like to allow the user to enter a few characters and return
all
possible matches from the partial. To me, if I am going to be doing a
search
like this then I do not have to display the results in a combo box
unless
that is the wisest thing to do. Really, all I am trying to accomplish
is
to
convert our call entry screen from a multiple screen slow to navigate
app
into a one screen streamlined entry form.

An example of what I am trying to get is say a user enters in "john" I
would
like to have the results that they get back display all firstname
matches
of
"john" and any lastname matches including "john" as all or part of the
string. Then when they select the person they want, I would like to
have
it
display their first and last names.

Thanks,

Chris

"Ken Snell [MVP]" wrote:

The code will depend upon what you want it to do. Should the code
find
the
first match and select that person in the combo box? Should the code
return
all the matches and let the user select from the filtered list (and
if
this
option, should the selection be made from the combo box or from
another
control, e.g., a listbox?)?

Will the user enter a text string on which to match in either the
first
name
or the last name? or do you want searching to be possible by letting
the
user enter a text string for a first name and a text string for a
last
name?

If you can identify what you want the form to do, then I can suggest
some
programming to do it.
--

Ken Snell
MS ACCESS MVP



"hammey22" wrote in message
...
Okay, what would be an example of the code and how would I place
the
values
in my entry form?

"Ken Snell [MVP]" wrote:

If you want to search various fields, then I would write VBA code
in
the
form that searches the table itself for a match, and that returns
the
appropriate primary key value so that you can set the combo box to
that
value. Searching the combo box itself is just adding one more step
in
the
process and is a bit trickier to do.

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi Ken,

I am trying to take a combination of firstname, lastname, and
custid
to
use
for entry into a call log for our home spun Help Desk DB. It is
currently
in
a wide open search by form which allows the HD users to find a
caller
by
firstname, lastname or even by company.

I could be happy with a cascading combo box grouping, but with
that
could
it
go from cb1 to cb2 and vice versa? We have some callers who
have
very
difficult lastnames to spell so we sometimes search on
firstname.
If I
could
get to the point of doing this via a combo I would be very
happy.

If you could give me some direction on the multiple combo boxes
that
would
be great!

Chris Hammer

"Ken Snell [MVP]" wrote:

This cannot be done directly in a combo box. You would need to
either
use
two separate combo boxes (one for last name and one for first
name,
where
the second combo box is cascading from the first one, meaning
that
the
list
of first names would be filtered by what is actually selected
in
the
first
combo box), or you would need to use two textboxes to enter the
desired
search letters and then click a button to conduct the search.

Perhaps, if you provide more details about the purpose and need
for
the
search, and how the result is to be used, we can suggest some
alternative
ideas.
--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in
message
...
Ken,

The way it works now is that I can enter the first letter or
two
of
the
customers last name in the combo box so that I can quickly
get to
the
m's
for
example. I need to find lastname matches along with
firstname.
This
is
going to replace the current form search that we have in
place
described
below.

Our current version of customer search allows for searching
on
first
and
lastnames. The old way is done on a form, then broght to
another
form.
On
mine I am trying to streamline, but the users aren't happy
about
losing
functionality.

Thanks,
Chris

"Ken Snell [MVP]" wrote:

Define "searchable".... what are you trying to do?

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in
message
...
Hi all,

I am using a combo box to concantenate 3 fields (custid,
firstname,
lastname) I am holding the values as entered in my table
just
fine.
My
question is how can I make more than one column searchable
in
the
concantenation. I am able to search on lastname only as
this
is
the
first
listed field. I would like to search on first and last
names.

Thanks for any advice that may come.

Chris Hammer

Hi Jeff,

Thanks very much for getting back to me there; none of the data in the
tables or fields I've got is Lookup data, although the 'lookup default' (I'm
not entirely sure what that is) is set to database default in my textbox
properties.

Basically I'm trying to build a quick search mechanism based on a text
search of firstname or surname into a front page of my database which
currently just acts as a gateway to other areas of my database. The form
itself currently has no dependence on other areas of the database (it just
has a graphic and a series of buttons which link to other forms through
macros) although I'm trying to search for any string a user enters in my
fields [First Name] and [Last Name] located in my query 'qry_candidate';
which is simply a query created from data entered into my table
'tbl_candidate' through 'frm_candidate'.

Thanks very much for your help!

Duncan



"Jeff Boyce" wrote:

Duncan

Is there a chance one of your tables/fields involved uses the "lookup" data
type? If so, what you see in the table and what is stored are two different
things. This leads to problems with queries, combo boxes, list boxes, etc.

--
Regards

Jeff Boyce
Office/Access MVP

"Duncan" wrote in message
...
Hi there,

I know it's a fair while after this post but I found Ken's response quite
helpful for something I've been trying to incorporate into a database of
my
own, only I seem to be having a little trouble trying to get his solution
to
work.

I've included the Find button, which when clicked makes my textbox visible
and below the textbox I have my listbox which becomes visible after I hit
enter to get out of the textbox - all working well!

The issue I'm having is that when I type a first name or surname into the
textbox (even ensuring that the match is absolutely exact), no results are
ever returned, I'm guessing it's not a problem with the code, but rather
something to do with the properties of either the list box or the text box
but I'm not entirely sure.

If I set the properties of the list box to visible, I can see the full
names
of the people held in my database but I just don't seem to be able to
connect
the list box to the things I'm typing into the text box, If anyone could
point out what I might have overlooked that would be fantastic.

I'd also ideally like to be able to double click a name in my list box or
click a button next to the list box to open that particular person's
record
as a completed form.

Any help with this would be very much appreciated.

Thank you!

Duncan


"Ken Snell [MVP]" wrote:

This setup will let you click a command button to allow the user to do
the
search. Clicking the button will make a textbox visible, into which the
user
will enter a string that is the search string. The user then presses
either
Tab or Enter, and a list box will become visible that shows the results
of
the search. The user double-clicks the name desired, and that name is
chosen
in the combo box for the user.

I will assume that your table is named tblNames, and that the two name
fields are called FName and LName, and that the ID field is named
NameID. I
also will assume that your initial combo box is named cboNames.

On your form, in the form header section, put a command button named
cmdFind
(make its caption "Find"). Also put a textbox (name it txtString) in
that
section, and a listbox (name it lstNames) in that section. Set the
Visible
property of both txtString and lstNames to No.

Set these properties of the lstNames list box:
-- Column Count: 2
-- Column Heads: No
-- Column Widths: 0"; 3"
-- Row Source Type: Table/Query
-- Row Source: (empty)
-- Bound Column: 1

Use this generic code for the Click event of cmdFind:

Private Sub cmdFind_Click()
Me!txtString.Visible = True
Me!txtString.SetFocus
End Sub


Use this generic code for the AfterUpdate event of txtString:

Private Sub txtString_AfterUpdate()
Dim strQuery As String
strQuery = "SELECT NameID, " & _
"FName & "" "" & LName AS FullName " & _
"FROM tblNames " & _
"WHERE FName Like ""*" & _
Me!txtString.Value & "*"" OR " & _
"LName Like ""*" & Me!txtString.Value & _
"*"" ORDER BY LName;"
Me!lstNames.RowSource = strQuery
Me!lstNames.Visible = True
End Sub


Use this generic code for the DblClick event of lstNames:

Private Sub lstNames_DblClick(Cancel As Integer)
Me!cboNames.Value = Me!lstNames.Value
Me!cboNames.SetFocus
Me!txtString.Visible = False
Me!lstNames.Visible = False
End Sub


--

Ken Snell
MS ACCESS MVP



"hammey22" wrote in message
...
Ken,

I would like to allow the user to enter a few characters and return
all
possible matches from the partial. To me, if I am going to be doing a
search
like this then I do not have to display the results in a combo box
unless
that is the wisest thing to do. Really, all I am trying to accomplish
is
to
convert our call entry screen from a multiple screen slow to navigate
app
into a one screen streamlined entry form.

An example of what I am trying to get is say a user enters in "john" I
would
like to have the results that they get back display all firstname
matches
of
"john" and any lastname matches including "john" as all or part of the
string. Then when they select the person they want, I would like to
have
it
display their first and last names.

Thanks,

Chris

"Ken Snell [MVP]" wrote:

The code will depend upon what you want it to do. Should the code
find
the
first match and select that person in the combo box? Should the code
return
all the matches and let the user select from the filtered list (and
if
this
option, should the selection be made from the combo box or from
another
control, e.g., a listbox?)?

Will the user enter a text string on which to match in either the
first
name
or the last name? or do you want searching to be possible by letting
the
user enter a text string for a first name and a text string for a
last
name?

If you can identify what you want the form to do, then I can suggest
some
programming to do it.
--

Ken Snell
MS ACCESS MVP



"hammey22" wrote in message
...
Okay, what would be an example of the code and how would I place
the
values
in my entry form?

"Ken Snell [MVP]" wrote:

If you want to search various fields, then I would write VBA code
in
the
form that searches the table itself for a match, and that returns
the
appropriate primary key value so that you can set the combo box to
that
value. Searching the combo box itself is just adding one more step
in
the
process and is a bit trickier to do.

--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in message
...
Hi Ken,

I am trying to take a combination of firstname, lastname, and
custid
to
use
for entry into a call log for our home spun Help Desk DB. It is
currently
in
a wide open search by form which allows the HD users to find a
caller
by
firstname, lastname or even by company.

I could be happy with a cascading combo box grouping, but with
that
could
it
go from cb1 to cb2 and vice versa? We have some callers who
have
very
difficult lastnames to spell so we sometimes search on
firstname.
If I
could
get to the point of doing this via a combo I would be very
happy.

If you could give me some direction on the multiple combo boxes
that
would
be great!

Chris Hammer

"Ken Snell [MVP]" wrote:

This cannot be done directly in a combo box. You would need to
either
use
two separate combo boxes (one for last name and one for first
name,
where
the second combo box is cascading from the first one, meaning
that
the
list
of first names would be filtered by what is actually selected
in
the
first
combo box), or you would need to use two textboxes to enter the
desired
search letters and then click a button to conduct the search.

Perhaps, if you provide more details about the purpose and need
for
the
search, and how the result is to be used, we can suggest some
alternative
ideas.
--

Ken Snell
MS ACCESS MVP

"hammey22" wrote in
message
...
Ken,

The way it works now is that I can enter the first letter or
two
of
the
customers last name in the combo box so that I can quickly
get to
the
m's