Re: Combo box woes
- From: "Jeff Boyce" <JeffBoyce_IF@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 30 Dec 2005 07:04:58 -0800
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" <Duncan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ADCDCFE3-7B97-4331-AD20-002DF77DA665@xxxxxxxxxxxxxxxx
> 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" <hammey22@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:8F52A17B-DD82-4F51-BB7D-8583B9130B67@xxxxxxxxxxxxxxxx
> > > 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" <hammey22@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > >> news:97DF0F05-BCC9-4972-A1C1-CB895C3D10AA@xxxxxxxxxxxxxxxx
> > >> > 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" <hammey22@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > >> >> news:B2F0BF0A-9C89-47BA-B7B5-124F926C2F61@xxxxxxxxxxxxxxxx
> > >> >> > 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" <hammey22@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
> > >> >> >> news:8A9E42C7-1EE7-44E2-850F-2A4F26BFD631@xxxxxxxxxxxxxxxx
> > >> >> >> > 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" <hammey22@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
> > >> >> >> >> news:73DD0876-64FB-4963-B46D-C6E0C6914442@xxxxxxxxxxxxxxxx
> > >> >> >> >> > 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
> > >> >> >> >>
> > >> >> >> >>
> > >> >> >> >>
> > >> >> >>
> > >> >> >>
> > >> >> >>
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >
.
- Follow-Ups:
- Re: Combo box woes
- From: Duncan
- Re: Combo box woes
- References:
- Re: Combo box woes
- From: Duncan
- Re: Combo box woes
- Prev by Date: Re: Using radiobuttons to dictate which form to open
- Next by Date: Re: On Change Bug?
- Previous by thread: Re: Combo box woes
- Next by thread: Re: Combo box woes
- Index(es):
Relevant Pages
|
