Results 1 to 22 of 22

Thread: Search for data in a MSHFlexGrid

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2012
    Posts
    41

    Search for data in a MSHFlexGrid

    In my form, I have a MSHFlexGrid (fgdCompany) and a Search button.
    What this search button should do is when I enter data into the InputBox, it should search for the entered data and highlight the row that the data was found in green in the MSHFlexGrid. My searching part works great.
    But my problem is, if the entered data is not found, I want it to output a MsgBox saying "Record not found".
    I can't get the piece of code for that function to work. Can anybody help me to get it right?
    Here is the code I am using for the Search button.

    Code:
      Dim xString As String, xRow As Integer, xNext As Integer
     
      cmdSaveNew.Enabled = False
    
      x = InputBox("Enter Company Name : ", "Search")
      If x = "" Then
        MsgBox "No data entered"
        Exit Sub
      Else
        txtSearch = x
      End If
          
      xString = txtSearch
      
      For xNext = 0 To fgdCompany.Rows
      fgdCompany.Row = xNext
      If xNext = fgdCompany.Rows Then
      MsgBox "Record not found"
      Exit Sub
      End If
      xRow = fgdCompany.Row
      If xString = fgdCompany.TextMatrix(xRow, 1) Then
      myRow = fgdCompany.Row
      fgdCompany.TopRow = myRow
      fgdCompany.RowSel = myRow
      Me.fgdCompany.Row = myRow
      For j = 0 To Me.fgdCompany.Cols - 1
      Me.fgdCompany.Col = j
      Me.fgdCompany.CellBackColor = vbGreen
      Next j
      Exit For
      End If
      Next xNext
    Last edited by Skate Bart; Sep 12th, 2012 at 09:03 AM.

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Search for data in a MSHFlexGrid

    Already answered this one on Code Guru

  3. #3

    Thread Starter
    Member
    Join Date
    Jun 2012
    Posts
    41

    Re: Search for data in a MSHFlexGrid

    Quote Originally Posted by DataMiser View Post
    Already answered this one on Code Guru
    I tried the method you said but it didn't work. Can you please give me the code for it..... I would greatly appreciate the help!!!

  4. #4
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Search for data in a MSHFlexGrid

    Quote Originally Posted by DataMiser View Post
    Already answered this one on Code Guru
    That doesn't help the folks on this site. Could you please repost your answer or at least post a link to it?

    Thanks.

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Search for data in a MSHFlexGrid

    vb Code:
    1. Dim xString As String, xRow As Integer, xNext As Integer
    2. Dim StringFound As Boolean
    3.  
    4.   '#######################
    5.   StringFound=False
    6.   '#######################
    7.   cmdSaveNew.Enabled = False
    8.  
    9.   x = InputBox("Enter Company Name : ", "Search")
    10.   If x = "" Then
    11.     MsgBox "No data entered"
    12.     Exit Sub
    13.   Else
    14.     txtSearch = x
    15.   End If
    16.      
    17.   xString = txtSearch
    18.  
    19.   For xNext = 0 To fgdCompany.Rows
    20.   fgdCompany.Row = xNext
    21.   If xNext = fgdCompany.Rows Then
    22.   MsgBox "Record not found"
    23.   Exit Sub
    24.   End If
    25.   xRow = fgdCompany.Row
    26.   If xString = fgdCompany.TextMatrix(xRow, 1) Then
    27.   '#######################
    28.   StringFound = True
    29.   '#######################
    30.   myRow = fgdCompany.Row
    31.   fgdCompany.TopRow = myRow
    32.   fgdCompany.RowSel = myRow
    33.   Me.fgdCompany.Row = myRow
    34.   For j = 0 To Me.fgdCompany.Cols - 1
    35.   Me.fgdCompany.Col = j
    36.   Me.fgdCompany.CellBackColor = vbGreen
    37.   Next j
    38.   Exit For
    39.   End If
    40.   Next xNext
    41.  
    42.   If Not StringFound Then MsgBox("Record not found")
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Search for data in a MSHFlexGrid

    Quote Originally Posted by Hack View Post
    That doesn't help the folks on this site. Could you please repost your answer or at least post a link to it?

    Thanks.

    http://forums.codeguru.com/showthrea...59#post2084659
    Simply dim a boolean variable at the top of your code which will be false by default.

    In your search routine set this variable to True if a match is found
    At the bottom after the search loop is complete check the value of the variable and if false show your message

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Search for data in a MSHFlexGrid



    Great minds think alike, or what?

    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Search for data in a MSHFlexGrid

    I, personally, have an aversion to 'Exit Sub' and 'Exit For', with a little thought the logic can be reversed to avoid using them:
    Code:
    Option Explicit
    
    Private Sub cmdSearch_Click()
    Dim xString As String, xRow As Integer
    Dim boFound As Boolean, J As Integer
    cmdSaveNew.Enabled = False
    xString = InputBox("Enter Company Name : ", "Search")
    If xString <> vbNullString Then
        txtSearch.Text = xString
        Do
            fgdCompany.Row = xRow
            If xString = fgdCompany.TextMatrix(xRow, 1) Then
                fgdCompany.TopRow = xRow
                fgdCompany.RowSel = xRow
                fgdCompany.Row = xRow
                For J = 0 To fgdCompany.Cols - 1
                    fgdCompany.Col = J
                    fgdCompany.CellBackColor = vbGreen
                Next J
                boFound = True
            Else
                xRow = xRow + 1
            End If
        Loop Until xRow > fgdCompany.Rows - 1 Or boFound = True ' Remove the 'Or boFound = True'
                                                                ' if you want to highlight duplicates
        If Not boFound Then
            MsgBox "Company " & xString & " Not Found"
        End If
    Else
        MsgBox "Please Enter a Company Name to Find"
    End If
    End Sub
    Last edited by Doogle; Sep 13th, 2012 at 04:18 AM.

  9. #9
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: Search for data in a MSHFlexGrid

    A very easy way is to do this in your code is:

    Dimension an integer (or double) at the click of the button (Dim aa as Double) (This also resets it to zero each time the button in clicked)
    In your loop, right after " If xString = fgdCompany.TextMatrix(xRow, 1) Then", type in "aa = aa + 1"
    Then, AFTER your loop, right after "Nex J", put this short 'if statement' in:

    If aa = 0 Then
    MsgBox mySearch + " was not found. Try Again."
    End If

    DELETE the following if statement in your code:
    If xNext = fgdCompany.Rows Then
    MsgBox "Record not found"
    Exit Sub

  10. #10

    Thread Starter
    Member
    Join Date
    Jun 2012
    Posts
    41

    Re: Search for data in a MSHFlexGrid

    Quote Originally Posted by Hack View Post
    That doesn't help the folks on this site. Could you please repost your answer or at least post a link to it?

    Thanks.
    I'm so so sorry. I completely forgot.
    Here's a link to the CodeGuru post : http://forums.codeguru.com/showthrea...81#post2084681

  11. #11

    Thread Starter
    Member
    Join Date
    Jun 2012
    Posts
    41

    Re: Search for data in a MSHFlexGrid

    Quote Originally Posted by Doogle View Post
    I, personally, have an aversion to 'Exit Sub' and 'Exit For', with a little thought the logic can be reversed to avoid using them:
    Code:
    Option Explicit
    
    Private Sub cmdSearch_Click()
    Dim xString As String, xRow As Integer
    Dim boFound As Boolean, J As Integer
    cmdSaveNew.Enabled = False
    xString = InputBox("Enter Company Name : ", "Search")
    If xString <> vbNullString Then
        txtSearch.Text = xString
        Do
            fgdCompany.Row = xRow
            If xString = fgdCompany.TextMatrix(xRow, 1) Then
                fgdCompany.TopRow = xRow
                fgdCompany.RowSel = xRow
                fgdCompany.Row = xRow
                For J = 0 To fgdCompany.Cols - 1
                    fgdCompany.Col = J
                    fgdCompany.CellBackColor = vbGreen
                Next J
                boFound = True
            Else
                xRow = xRow + 1
            End If
        Loop Until xRow > fgdCompany.Rows - 1 Or boFound = True ' Remove the 'Or boFound = True'
                                                                ' if you want to highlight duplicates
        If Not boFound Then
            MsgBox "Company " & xString & " Not Found"
        End If
    Else
        MsgBox "Please Enter a Company Name to Find"
    End If
    End Sub
    It seems to be that there is a MAJOR problem in this code.
    The code works and gives the correct output when wrong data is entered.
    But this is the problem. When correct data is entered, it highlights the correct row in green.
    And instantly, the program gets stuck and becomes unresponsive. (Windows puts the application into "Not Responding" mode.)
    I have to close Visual Basic and start it again.
    Any idea on what's wrong?
    Last edited by Skate Bart; Sep 15th, 2012 at 09:54 AM.

  12. #12

    Thread Starter
    Member
    Join Date
    Jun 2012
    Posts
    41

    Re: Search for data in a MSHFlexGrid

    Quote Originally Posted by Zvoni View Post
    vb Code:
    1. Dim xString As String, xRow As Integer, xNext As Integer
    2. Dim StringFound As Boolean
    3.  
    4.   '#######################
    5.   StringFound=False
    6.   '#######################
    7.   cmdSaveNew.Enabled = False
    8.  
    9.   x = InputBox("Enter Company Name : ", "Search")
    10.   If x = "" Then
    11.     MsgBox "No data entered"
    12.     Exit Sub
    13.   Else
    14.     txtSearch = x
    15.   End If
    16.      
    17.   xString = txtSearch
    18.  
    19.   For xNext = 0 To fgdCompany.Rows
    20.   fgdCompany.Row = xNext
    21.   If xNext = fgdCompany.Rows Then
    22.   MsgBox "Record not found"
    23.   Exit Sub
    24.   End If
    25.   xRow = fgdCompany.Row
    26.   If xString = fgdCompany.TextMatrix(xRow, 1) Then
    27.   '#######################
    28.   StringFound = True
    29.   '#######################
    30.   myRow = fgdCompany.Row
    31.   fgdCompany.TopRow = myRow
    32.   fgdCompany.RowSel = myRow
    33.   Me.fgdCompany.Row = myRow
    34.   For j = 0 To Me.fgdCompany.Cols - 1
    35.   Me.fgdCompany.Col = j
    36.   Me.fgdCompany.CellBackColor = vbGreen
    37.   Next j
    38.   Exit For
    39.   End If
    40.   Next xNext
    41.  
    42.   If Not StringFound Then MsgBox("Record not found")
    It highlights the line : " fgdCompany.Row = xNext " and gives an error saying "Invalid row value"

  13. #13
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Search for data in a MSHFlexGrid

    Did you copy and paste the whole of the code in Post#8 and did you change anything. There's no way the code as posted will display the problem you describe. (I did test it prior to posting and I had no problems at all)

  14. #14

    Thread Starter
    Member
    Join Date
    Jun 2012
    Posts
    41

    Re: Search for data in a MSHFlexGrid

    Quote Originally Posted by SamOscarBrown View Post
    A very easy way is to do this in your code is:

    Dimension an integer (or double) at the click of the button (Dim aa as Double) (This also resets it to zero each time the button in clicked)
    In your loop, right after " If xString = fgdCompany.TextMatrix(xRow, 1) Then", type in "aa = aa + 1"
    Then, AFTER your loop, right after "Nex J", put this short 'if statement' in:

    If aa = 0 Then
    MsgBox mySearch + " was not found. Try Again."
    End If

    DELETE the following if statement in your code:
    If xNext = fgdCompany.Rows Then
    MsgBox "Record not found"
    Exit Sub
    It highlights the line : " fgdCompany.Row = xNext " and gives an error saying "Invalid row value"

  15. #15

    Thread Starter
    Member
    Join Date
    Jun 2012
    Posts
    41

    Re: Search for data in a MSHFlexGrid

    Quote Originally Posted by Doogle View Post
    Did you copy and paste the whole of the code in Post#8 and did you change anything. There's no way the code as posted will display the problem you describe. (I did test it prior to posting and I had no problems at all)
    I did remove the "txtSearch.Text = xString" line as it kept highlighting that line and giving the error "Object required".

  16. #16
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Search for data in a MSHFlexGrid

    The only problem I can see is if you took out the 'Or boFound = True' on the Loop statement, you'd also need to remove the 'Else' so it would read like:
    Code:
                boFound = True
                xRow = xRow + 1

  17. #17

    Thread Starter
    Member
    Join Date
    Jun 2012
    Posts
    41

    Re: Search for data in a MSHFlexGrid

    Quote Originally Posted by Doogle View Post
    The only problem I can see is if you took out the 'Or boFound = True' on the Loop statement, you'd also need to remove the 'Else' so it would read like:
    Code:
                boFound = True
                xRow = xRow + 1
    I did what you said.
    And now whether I enter correct/incorrect data, the whole program becomes unresponsive when clicked "OK" in the InputBox....

  18. #18
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Search for data in a MSHFlexGrid

    Can you please post the exact code you're using (just copy and paste it into a Post here. This is really weird !

  19. #19

    Thread Starter
    Member
    Join Date
    Jun 2012
    Posts
    41

    Re: Search for data in a MSHFlexGrid

    Dim xString As String, xRow As Integer
    Dim boFound As Boolean, J As Integer

    cmdSaveNew.Enabled = False
    xString = InputBox("Enter Company Name : ", "Search")
    If xString <> vbNullString Then
    Do
    fgdCompany.Row = xRow
    If xString = fgdCompany.TextMatrix(xRow, 1) Then
    fgdCompany.TopRow = xRow
    fgdCompany.RowSel = xRow
    fgdCompany.Row = xRow
    For J = 0 To fgdCompany.Cols - 1
    fgdCompany.Col = J
    fgdCompany.CellBackColor = vbGreen
    Next J
    boFound = True
    xRow = xRow + 1
    End If
    Loop Until xRow > fgdCompany.Rows - 1


    If Not boFound Then
    MsgBox "Company " & xString & " Not Found"
    End If
    Else
    MsgBox "Please Enter a Company Name to Find"
    End If

  20. #20
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Search for data in a MSHFlexGrid

    OK, I've found it, my mistake. Change
    Code:
        boFound = True
        xRow = xRow + 1
    End If
    to
    Code:
        boFound = True
    End If
    xRow = xRow + 1

  21. #21

    Thread Starter
    Member
    Join Date
    Jun 2012
    Posts
    41

    Re: Search for data in a MSHFlexGrid

    Quote Originally Posted by Doogle View Post
    OK, I've found it, my mistake. Change
    Code:
        boFound = True
        xRow = xRow + 1
    End If
    to
    Code:
        boFound = True
    End If
    xRow = xRow + 1
    YESS!!!!!! THANK YOU SO MUCH DOOGLE!!!!!!!!!
    you never fail to help me!!!! I am so grateful to you man.
    Thank you so so much!!! XD

  22. #22

    Thread Starter
    Member
    Join Date
    Jun 2012
    Posts
    41

    Re: Search for data in a MSHFlexGrid

    Quote Originally Posted by Doogle View Post
    OK, I've found it, my mistake. Change
    Code:
        boFound = True
        xRow = xRow + 1
    End If
    to
    Code:
        boFound = True
    End If
    xRow = xRow + 1
    YESS!!!!!! THANK YOU SO MUCH DOOGLE!!!!!!!!!
    you never fail to help me!!!! I am so grateful to you man.
    Thank you so so much!!! XD

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
  •  



Click Here to Expand Forum to Full Width