Search for data in a MSHFlexGrid

[Skate Bart]

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.

  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

[DataMiser]

Already answered this one on Code Guru

[Skate Bart]

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!!!

[Hack]

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.

[Zvoni]

Dim xString As String, xRow As Integer, xNext As Integer
Dim StringFound As Boolean
 
  '#######################
  StringFound=False
  '#######################
  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
  '#######################
  StringFound = True
  '#######################
  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
 
  If Not StringFound Then MsgBox("Record not found")

[DataMiser]

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

[Zvoni]

Great minds think alike, or what?

[Doogle]

I, personally, have an aversion to 'Exit Sub' and 'Exit For', with a little thought the logic can be reversed to avoid using them:

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

[SamOscarBrown]

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

[Skate Bart]

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

[Skate Bart]

I, personally, have an aversion to 'Exit Sub' and 'Exit For', with a little thought the logic can be reversed to avoid using them:

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?

[Skate Bart]

Dim xString As String, xRow As Integer, xNext As Integer
Dim StringFound As Boolean
 
  '#######################
  StringFound=False
  '#######################
  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
  '#######################
  StringFound = True
  '#######################
  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
 
  If Not StringFound Then MsgBox("Record not found")

It highlights the line : " fgdCompany.Row = xNext " and gives an error saying "Invalid row value"

[Doogle]

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)

[Skate Bart]

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"

[Skate Bart]

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".

[Doogle]

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:

            boFound = True
            xRow = xRow + 1

[Skate Bart]

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:

            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....

[Doogle]

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

[Skate Bart]

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

[Doogle]

OK, I've found it, my mistake. Change

    boFound = True
    xRow = xRow + 1
End If

to

    boFound = True
End If
xRow = xRow + 1

[Skate Bart]

OK, I've found it, my mistake. Change

    boFound = True
    xRow = xRow + 1
End If

to

    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

[Skate Bart]

OK, I've found it, my mistake. Change

    boFound = True
    xRow = xRow + 1
End If

to

    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