Combo box woes

[Guest]

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 Snell [MVP]]

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

 

[Guest]

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]]

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.

 

[Guest]

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]]

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.

 

[Guest]

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

 

[Ken Snell [MVP]]

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>

 

[Guest]

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]]

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>

 

[Guest]

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

 

[Jeff Boyce]

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>

 

[Guest]

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]

Duncan

Are you saying that your query doesn't return rows for the FirstName and
LastName values you enter into the form?

What's your query look like (please post the SQL statement)?

--
Regards

Jeff Boyce
<Office/Access MVP>

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

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>

of
my solution
to do
the the
user results
of is
chosen return
all accomplish
is navigate
app to
have (and
if letting
the suggest
some place
the code
in returns
the step
in and
custid with
that boxes
that first
name, meaning
that selected
in need
for or
two quickly
get to

 

[Guest]

Hi Jeff ,

Thanks again for your help, Hope you're having had a good new year. The
query I'm looking up against that I've created looks exactly as below; the
forms I've created use exactly the same code that Ken laid out in the
previous posts (except with my field and table names in the appropriate
places so Ken's NameID has become my CANDIDATEID) - I've got the list box
working in that it displays the full names of all the candidates when
visible, only I would like any text strings entered into the text box to
scroll down that list to the relevant strings. Ideally, I'd then like to be
able to open the selected record by hitting enter.

SELECT tbl_candidate.CANDIDATEID, tbl_candidate.ContactID,
tbl_candidate.Title, tbl_candidate.[First Name], tbl_candidate.[Last Name],
tbl_candidate.[Current Employer], tbl_candidate.[Salary Requirement],
tbl_candidate.[Mobile Phone Number], tbl_candidate.[Home Phone Number],
tbl_candidate.[e-mail Address], tbl_candidate.AgencyID,
tbl_candidate.[Current Position]
FROM tbl_candidate
ORDER BY tbl_candidate.[Last Name];

Thanks very much!

Duncan

Duncan

Are you saying that your query doesn't return rows for the FirstName and
LastName values you enter into the form?

What's your query look like (please post the SQL statement)?

--
Regards

Jeff Boyce
<Office/Access MVP>

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

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>

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


:

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>



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

:

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>



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

:

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>

Hi Ken,

 

[Jeff Boyce]

Duncan

I don't see a WHERE clause in your SQL statement. How are you using the
[FirstName] and/or [LastName] to look something up (?between your textbox
and your listbox?)

Could you describe again where you are using this SQL statement?

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi Jeff ,

Thanks again for your help, Hope you're having had a good new year. The
query I'm looking up against that I've created looks exactly as below; the
forms I've created use exactly the same code that Ken laid out in the
previous posts (except with my field and table names in the appropriate
places so Ken's NameID has become my CANDIDATEID) - I've got the list box
working in that it displays the full names of all the candidates when
visible, only I would like any text strings entered into the text box to
scroll down that list to the relevant strings. Ideally, I'd then like to be
able to open the selected record by hitting enter.

SELECT tbl_candidate.CANDIDATEID, tbl_candidate.ContactID,
tbl_candidate.Title, tbl_candidate.[First Name], tbl_candidate.[Last Name],
tbl_candidate.[Current Employer], tbl_candidate.[Salary Requirement],
tbl_candidate.[Mobile Phone Number], tbl_candidate.[Home Phone Number],
tbl_candidate.[e-mail Address], tbl_candidate.AgencyID,
tbl_candidate.[Current Position]
FROM tbl_candidate
ORDER BY tbl_candidate.[Last Name];

Thanks very much!

Duncan

Duncan

Are you saying that your query doesn't return rows for the FirstName and
LastName values you enter into the form?

What's your query look like (please post the SQL statement)?

--
Regards

Jeff Boyce
<Office/Access MVP>

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



:

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>

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


:

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>



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

:

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>



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

:

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>

Hi Ken,

 

[Guest]

Hi Jeff,

Thanks so much again for your help.

The list box I have created has a Table/Query Row Source Type and that is
the Query it is looking at and I copied and pasted the SQL view that the
query is looking at.

The Row Source of the list box reads:

SELECT [CANDIDATEID], [First Name] & " " & [Last Name] AS FullName FROM
tbl_candidate WHERE [First Name] Like "**" OR [Last Name] like "**" ORDER BY
[Last Name];

Hope this helps, sorry if I'm not explaining myself too well only I'm quite
new to Access and not entirely sure exactly what's relevant - but I really
appreciate all your help!

Duncan

I don't see a WHERE clause in your SQL statement. How are you using the
[FirstName] and/or [LastName] to look something up (?between your textbox
and your listbox?)

Could you describe again where you are using this SQL statement?

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi Jeff ,

Thanks again for your help, Hope you're having had a good new year. The
query I'm looking up against that I've created looks exactly as below; the
forms I've created use exactly the same code that Ken laid out in the
previous posts (except with my field and table names in the appropriate
places so Ken's NameID has become my CANDIDATEID) - I've got the list box
working in that it displays the full names of all the candidates when
visible, only I would like any text strings entered into the text box to
scroll down that list to the relevant strings. Ideally, I'd then like to be
able to open the selected record by hitting enter.

SELECT tbl_candidate.CANDIDATEID, tbl_candidate.ContactID,
tbl_candidate.Title, tbl_candidate.[First Name], tbl_candidate.[Last Name],
tbl_candidate.[Current Employer], tbl_candidate.[Salary Requirement],
tbl_candidate.[Mobile Phone Number], tbl_candidate.[Home Phone Number],
tbl_candidate.[e-mail Address], tbl_candidate.AgencyID,
tbl_candidate.[Current Position]
FROM tbl_candidate
ORDER BY tbl_candidate.[Last Name];

Thanks very much!

Duncan

Duncan

Are you saying that your query doesn't return rows for the FirstName and
LastName values you enter into the form?

What's your query look like (please post the SQL statement)?

--
Regards

Jeff Boyce
<Office/Access MVP>

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



:

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>

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


:

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>



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.

 

[Jeff Boyce]

Duncan

Copy that SQL statement into the SQL view of a new query (in design mode).
Try to run it.

You may be having a problem with there not being anything between the **'s.
The usual use of this wildcard is something more akin to:
WHERE [First Name] Like "*" & [Enter First Name] & "*"
(this is untested aircode).

And by using the " OR " clause in your WHERE clause, you are telling Access
to find something if there's a match for either the [First Name] or the
[Last Name].

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi Jeff,

Thanks so much again for your help.

The list box I have created has a Table/Query Row Source Type and that is
the Query it is looking at and I copied and pasted the SQL view that the
query is looking at.

The Row Source of the list box reads:

SELECT [CANDIDATEID], [First Name] & " " & [Last Name] AS FullName FROM
tbl_candidate WHERE [First Name] Like "**" OR [Last Name] like "**" ORDER BY
[Last Name];

Hope this helps, sorry if I'm not explaining myself too well only I'm quite
new to Access and not entirely sure exactly what's relevant - but I really
appreciate all your help!

Duncan

I don't see a WHERE clause in your SQL statement. How are you using the
[FirstName] and/or [LastName] to look something up (?between your textbox
and your listbox?)

Could you describe again where you are using this SQL statement?

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi Jeff ,

Thanks again for your help, Hope you're having had a good new year. The
query I'm looking up against that I've created looks exactly as below; the
forms I've created use exactly the same code that Ken laid out in the
previous posts (except with my field and table names in the appropriate
places so Ken's NameID has become my CANDIDATEID) - I've got the list box
working in that it displays the full names of all the candidates when
visible, only I would like any text strings entered into the text box to
scroll down that list to the relevant strings. Ideally, I'd then like

to
be

able to open the selected record by hitting enter.

SELECT tbl_candidate.CANDIDATEID, tbl_candidate.ContactID,
tbl_candidate.Title, tbl_candidate.[First Name], tbl_candidate.[Last Name],
tbl_candidate.[Current Employer], tbl_candidate.[Salary Requirement],
tbl_candidate.[Mobile Phone Number], tbl_candidate.[Home Phone Number],
tbl_candidate.[e-mail Address], tbl_candidate.AgencyID,
tbl_candidate.[Current Position]
FROM tbl_candidate
ORDER BY tbl_candidate.[Last Name];

Thanks very much!

Duncan

:

Duncan

Are you saying that your query doesn't return rows for the FirstName and
LastName values you enter into the form?

What's your query look like (please post the SQL statement)?

--
Regards

Jeff Boyce
<Office/Access MVP>

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



:

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>

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


:

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>



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.

 

[Guest]

Thanks very much Jeff,

I've copied the original query into the sql view and tried to run it and it
seems to run just fine - (at least it pulls up a full list of everyone in my
databases CandidateID and full name which I think is what I'm after). This
information (without the CandidateID) is also currently visible in the list
box on my form, it's just that whenever I type anything into the text box and
hit enter as opposed to shifting the highlighted row to the closest string
which is what I'm ideally after.

Thanks again!

Duncan

Copy that SQL statement into the SQL view of a new query (in design mode).
Try to run it.

You may be having a problem with there not being anything between the **'s.
The usual use of this wildcard is something more akin to:
WHERE [First Name] Like "*" & [Enter First Name] & "*"
(this is untested aircode).

And by using the " OR " clause in your WHERE clause, you are telling Access
to find something if there's a match for either the [First Name] or the
[Last Name].

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi Jeff,

Thanks so much again for your help.

The list box I have created has a Table/Query Row Source Type and that is
the Query it is looking at and I copied and pasted the SQL view that the
query is looking at.

The Row Source of the list box reads:

SELECT [CANDIDATEID], [First Name] & " " & [Last Name] AS FullName FROM
tbl_candidate WHERE [First Name] Like "**" OR [Last Name] like "**" ORDER BY
[Last Name];

Hope this helps, sorry if I'm not explaining myself too well only I'm quite
new to Access and not entirely sure exactly what's relevant - but I really
appreciate all your help!

Duncan

I don't see a WHERE clause in your SQL statement. How are you using the
[FirstName] and/or [LastName] to look something up (?between your textbox
and your listbox?)

Could you describe again where you are using this SQL statement?

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi Jeff ,

Thanks again for your help, Hope you're having had a good new year. The
query I'm looking up against that I've created looks exactly as below; the
forms I've created use exactly the same code that Ken laid out in the
previous posts (except with my field and table names in the appropriate
places so Ken's NameID has become my CANDIDATEID) - I've got the list box
working in that it displays the full names of all the candidates when
visible, only I would like any text strings entered into the text box to
scroll down that list to the relevant strings. Ideally, I'd then like to
be
able to open the selected record by hitting enter.

SELECT tbl_candidate.CANDIDATEID, tbl_candidate.ContactID,
tbl_candidate.Title, tbl_candidate.[First Name], tbl_candidate.[Last
Name],
tbl_candidate.[Current Employer], tbl_candidate.[Salary Requirement],
tbl_candidate.[Mobile Phone Number], tbl_candidate.[Home Phone Number],
tbl_candidate.[e-mail Address], tbl_candidate.AgencyID,
tbl_candidate.[Current Position]
FROM tbl_candidate
ORDER BY tbl_candidate.[Last Name];

Thanks very much!

Duncan

:

Duncan

Are you saying that your query doesn't return rows for the FirstName and
LastName values you enter into the form?

What's your query look like (please post the SQL statement)?

--
Regards

Jeff Boyce
<Office/Access MVP>

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



:

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>

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


:

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)

 

[Guest]

Hi Ken, this code is just what I am looking for, works a treat, I was
wondering now, how I go about adding a field 'PostTitle' into the mix, as
people might want to also search this list by post title as well.

Can you help? What code should I be adding the script in txtString, I have
tried a few variations but to no avail.

Regards