Re: Combo box woes
- From: Duncan <Duncan@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 30 Dec 2005 03:17:02 -0800
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: Jeff Boyce
- Re: Combo box woes
- Prev by Date: RE: Help. Form subform problem...
- Next by Date: Re: Calculating speed
- Previous by thread: RE: Help. Form subform problem...
- Next by thread: Re: Combo box woes
- Index(es):
Relevant Pages
|
