Consulting

Results 1 to 10 of 10

Thread: Search data using Userform and then scroll through matching records

  1. #1
    VBAX Regular
    Joined
    Dec 2019
    Posts
    15
    Location

    Search data using Userform and then scroll through matching records

    I have a userform which is currently used to search for data using a person's surname and then displays all data for that match. All works OK except it finds the first instance of a matching surname only and I want to then scroll through (on the userform) other matching instances of that same surname using a next button (and previous to go back when needed) until I reach the exact match I am looking for. (I would be OK with the next button simply scrolling alphabetically through the data list once the searched surnames are exhausted. So if for example there are five "Smiths" followed alphabetically by Soper and so on then it does not matter that it continues onto Soper and not stopping at end of Smiths but keeps going as it were.) Once the appropriate record is found the user can update details on the form and save using an update button and exit form or clear and search for another record.

    I have a search button and code that works but I cannot work out how to then get a next and previous button to work from that record displayed. (I do have next/previous buttons that operate but always revert to the first record and not from the displayed record.) Ideally I would also want the search to ignore upper/lower case so any matching record would be found regardless of user input of surname with capital letter or not. I have clear and close buttons that work fine.

    Code I have for next is:

    Private Sub CmdButtonNext_Click()
    ' next button
    Dim lastrow As Long

    lastrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row

    If currentrow = lastrow Then
    MsgBox "You have reached the last record in the database"
    Exit Sub
    End If

    currentrow = currentrow + 1

    TxtMemNo = Cells(currentrow, 1)
    TxtTitle = Cells(currentrow, 2)
    TxtFirstName = Cells(currentrow, 3)
    TxtSurname = Cells(currentrow, 4)
    TxtEmail = Cells(currentrow, 5)

    End Sub

    The back button operates in the same way except using "currentrow - 1"

    The search button code is:

    Private Sub CmdButtonSearch_Click()

    Dim totRows As Long, i As Long

    totRows = ThisWorkbook.Worksheets("Membership").range("A10").CurrentRegion.Rows.Count

    For i = 10 To totRows
    If Trim(Sheet2.Cells(i, 4)) = Trim(TxtSurname.Text) Then
    TxtMemNo.Text = Sheet2.Cells(i, 1)
    TxtTitle.Text = Sheet2.Cells(i, 2)
    TxtFirstName.Text = Sheet2.Cells(i, 3)
    TxtSurname.Text = Sheet2.Cells(i, 4)
    TxtEmail.Text = Sheet2.Cells(i, 5)

    Exit For

    End If

    Next i

    End Sub

    I am a newbie and learning fast but any help greatly appreciated (or a pointer in the right direction and I will carry on searching the internet/trying ideas for solutions). I am thinking maybe I should be using a FindNext command or somehow call the code for the next/back buttons from within the search sub, however, despite trying several options I haven't managed to solve it. Thanks in advance for all/any thoughts.

  2. #2
    VBAX Expert paulked's Avatar
    Joined
    Apr 2006
    Posts
    613
    Location
    I would put a ListBox on the UserForm and populate that from the search. The user can then pick the required 'record' from the ListBox.
    Semper in excretia sumus; solum profundum variat.

  3. #3
    VBAX Regular
    Joined
    Dec 2019
    Posts
    15
    Location
    Quote Originally Posted by paulked View Post
    I would put a ListBox on the UserForm and populate that from the search. The user can then pick the required 'record' from the ListBox.
    If I place a ListBox on the UserForm, I'd still need a search function as there are over 800 records to look through (plus a lot more fields than my example code suggested; I left out most out of "repetitive" code for simplicity). I will look into adding a search function to a ListBox but suspect I might end up with same dilemma. It will be non-technical people updating records so it needs to be "simple".

    Thanks for your suggestion.

    I guess I could also, look at adding two fields to search on i.e. surname and first name - the chances of having duplicate records in that case would be minimal. Not sure how to achieve that but a bit more research needed!

  4. #4
    VBAX Expert paulked's Avatar
    Joined
    Apr 2006
    Posts
    613
    Location
    I haven't got much time today but have muddled this as an example:

    PS I searched on email as these tend to be unique and I don't know how your MemNo is set up.
    Attached Files Attached Files
    Semper in excretia sumus; solum profundum variat.

  5. #5
    VBAX Regular
    Joined
    Dec 2019
    Posts
    15
    Location
    Thanks Paul. I myself have been too busy today to delve into the depths but plan to do so over the weekend, family matters allowing! I have taken a quick look at the code and can see how the search function is operating. Being a newbie, one thing confuses me in that search button code and perhaps you'd explain. You have declared a variable "sLst as string" but I cannot see where that variable is used. Is it a case of declaring and then deciding you didn't need it or am I missing something?

    I have not fully got to grips with the code you show against the select button; you've introduced me to two new functions - split and unbound. I have started to read up what they mean/do but not had time to fully take in. I need to go back to that section.

    Thanks for your help. Lots to mull over and I still need to adapt to suit to what I am doing. Since I have some 24 columns of data I suspect I also need to find a way to loop through to create the listbox results without typing everyone individually.

    I may have further questions when I have taken it myself to the next stage and hopefully can ask again for some guidance if needed. Thanks.

  6. #6
    VBAX Expert paulked's Avatar
    Joined
    Apr 2006
    Posts
    613
    Location
    I was rushing, sLst isn't needed!

    Split puts all the words in ListBox1 string into the array aEmail. UBound is the last element of that array, ie the email address.

    Just pick the cells from the columns to put in the list box that can identify the row you want. Typing everyone? No idea why you need to do that.

    Sure thing, there are lots of people here willing to help.

    Post your workbook, or a sample if it contains sensitive data, and I'll have a look tomorrow
    Semper in excretia sumus; solum profundum variat.

  7. #7
    VBAX Regular
    Joined
    Dec 2019
    Posts
    15
    Location
    Quote Originally Posted by paulked View Post
    Typing everyone? No idea why you need to do that.

    Thanks Paul - still need to think things through fully but when you ask "typing everyone?" your code includes:

    With Sheet2
    If .Cells(i, 3) = TextBox1 Then
    ListBox1.AddItem .Cells(i, 1) & " " & .Cells(i, 2) & " " & .Cells(i, 3) & " " & .Cells(i, 4) & " " & .Cells(i, 5)
    c = c + 1
    End If
    End With

    I have some 20 plus columns of data and was thinking how to avoid typing each out for each column "cells (i, [number])" right up to 20 plus. And ideally make it dynamic anyway so should another column be added the code recognises and adds automatically. That was where I was coming from.

  8. #8
    VBAX Expert paulked's Avatar
    Joined
    Apr 2006
    Posts
    613
    Location
    Aha! So if I understand you correctly, you don't need all the data in the listbox to identify the record, so just select the cells you do need. eg First name, last name, Town, email address. Then the user can double click on that item in the listbox to get all the information on that person (instead of CommandButton3_Click() use ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) to run the code).
    Semper in excretia sumus; solum profundum variat.

  9. #9
    VBAX Expert paulked's Avatar
    Joined
    Apr 2006
    Posts
    613
    Location
    If you do want all the columns then:

    Private Sub CommandButton1_Click()
    ' SEARCH button
        Dim lastrow As Long, i As Long, j As Long, c As Long, sLst As String
        lastrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
        ListBox1.Clear
        For i = 1 To lastrow
            With Sheet2
                If .Cells(i, 3) = TextBox1 Then
                    For j = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column
                        sLst = sLst & .Cells(i, j) & " "
                    Next
                    sLst = Left(sLst, (Len(sLst) - 1))
                    ListBox1.AddItem sLst
                    c = c + 1
                    sLst = ""
                End If
            End With
        Next
        If c = 0 Then MsgBox "Not found"
    End Sub
    That's where sLst came from
    Semper in excretia sumus; solum profundum variat.

  10. #10
    VBAX Regular
    Joined
    Dec 2019
    Posts
    15
    Location
    Excellent and thanks, I knew there would be a better method and yep I just felt that sLst variable had a purpose.

Tags for this Thread

Posting Permissions

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