Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filling a listbox (97/ SR2)

    I have a database that is eventually going to have about 70,000 records. I'm currently building a search form. Here is how it is layed out and what I want it to do. The form consist of 2 textboxes (txtLastName, txtFirstName) and 1 listbox (lstMatches). I left the rowsource of the listbox blank, because I want to fill it at runtime. Here is what I want the user to do. The user enters a last name in the txtLastName textbox, then after they type in the 3rd character in txtFirstName textbox I want to generate the sql based on what was typed in the textboxes. I want to hold off from populating the listbox as long as possible. Could I get some ideas on how to go about doing this? Any suggestion or examples would be great!

    Thanks,
    Jols

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling a listbox (97/ SR2)

    <A target="_blank" HREF=http://secure.necaweb.com/caryp-cgi/hazel.cgi?client=98533829&action=SERVE&item=freebi es.htm>Down load the 25 Cool Control Tricks</A> 25 Cool Control Tricks

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling a listbox (97/ SR2)

    Thanks Rupert for the great reference!
    I almost have everything working as I would like but I'm stuck on one thing. I'll quickly explain what I am doing again. My search form has 2 textboxes and one listbox. The user will type in the 1st text box the last name they want to search and then in the after update of this textbox I have this code:

    Private Sub txtLastName_AfterUpdate()
    Dim strSQL As String

    strSQL = "SELECT tblNames.DocketNo, tblNames.LastName, tblNames.FirstName, tblNames.MiddleName, tblNames.City FROM tblNames"

    lstMatches.RowSource = strSQL & " WHERE (tblNames.LastName) Like '" & Trim(txtLastName) & "*' Order By tblNames.LastName;"

    End Sub

    This code populates the listbox. Now I would like to go even further and allow the user to type the first name in the txtFirstName textbox. So if they type smith in the last name textbox and e in the first name text box it will only show persons who's last name is smith and first name begins with e. Any ideas on the best way to go about doing this?
    Thanks so much for any help.
    Jols

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling a listbox (97/ SR2)

    Instead of two text boxes and a list box, have you considered using two unbound combo boxes for selecting the last name & first name and then displaying the results in a sub form displayed as a Datasheet? This is the way I would do it. Your way may have benefits with a large number of records, one of the moderators may know, I don

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filling a listbox (97/ SR2)

    If you haven't figure out how to do this by now, the answer is fairly simple. I would create a separate routine called SetRowSource and make it look something like this:

    <pre>Private Sub SetRowSource()
    Dim strSQL As String

    strSQL = "SELECT tblNames.DocketNo, tblNames.LastName, tblNames.FirstName, " _
    & "tblNames.MiddleName, tblNames.City FROM tblNames " _
    & " WHERE (tblNames.LastName) Like '" & Trim(Me.txtLastName) & "*' " _
    & "AND (tblNames.FirstName) Like '" & Trim(Me.txtFirstName) & "*' " _
    & "ORDER BY tblNames.LastName;"

    lstMatches.RowSource = strSQL

    End Sub</pre>


    Then call the subroutine from each of the afterupdate events, for txtLastName and txtFirstName. If you fill in a lastname value, the listbox will be filtered for all the lastnames like that value and all the firstnames like '*'. If you fill in a firstname value, the listbox will be filtered for both the last and first name values entered.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •