-
Sep 12th, 2012, 08:18 AM
#1
Thread Starter
Member
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.
-
Sep 12th, 2012, 09:23 AM
#2
Re: Search for data in a MSHFlexGrid
Already answered this one on Code Guru
-
Sep 12th, 2012, 10:04 AM
#3
Thread Starter
Member
Re: Search for data in a MSHFlexGrid
Originally Posted by DataMiser
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!!!
-
Sep 12th, 2012, 10:45 AM
#4
Re: Search for data in a MSHFlexGrid
Originally Posted by DataMiser
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.
-
Sep 12th, 2012, 10:54 AM
#5
Re: Search for data in a MSHFlexGrid
vb Code:
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")
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
-
Sep 12th, 2012, 12:07 PM
#6
Re: Search for data in a MSHFlexGrid
Originally Posted by Hack
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
-
Sep 13th, 2012, 02:34 AM
#7
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
-
Sep 13th, 2012, 03:37 AM
#8
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.
-
Sep 13th, 2012, 07:32 AM
#9
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
-
Sep 15th, 2012, 09:06 AM
#10
Thread Starter
Member
Re: Search for data in a MSHFlexGrid
Originally Posted by Hack
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
-
Sep 15th, 2012, 09:26 AM
#11
Thread Starter
Member
Re: Search for data in a MSHFlexGrid
Originally Posted by 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:
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.
-
Sep 15th, 2012, 09:46 AM
#12
Thread Starter
Member
Re: Search for data in a MSHFlexGrid
Originally Posted by Zvoni
vb Code:
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"
-
Sep 15th, 2012, 10:02 AM
#13
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)
-
Sep 15th, 2012, 10:03 AM
#14
Thread Starter
Member
Re: Search for data in a MSHFlexGrid
Originally Posted by 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
It highlights the line : " fgdCompany.Row = xNext " and gives an error saying "Invalid row value"
-
Sep 15th, 2012, 10:06 AM
#15
Thread Starter
Member
Re: Search for data in a MSHFlexGrid
Originally Posted by 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)
I did remove the "txtSearch.Text = xString" line as it kept highlighting that line and giving the error "Object required".
-
Sep 15th, 2012, 10:11 AM
#16
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
-
Sep 15th, 2012, 10:23 AM
#17
Thread Starter
Member
Re: Search for data in a MSHFlexGrid
Originally Posted by 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:
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....
-
Sep 15th, 2012, 10:26 AM
#18
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 !
-
Sep 15th, 2012, 10:31 AM
#19
Thread Starter
Member
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
-
Sep 15th, 2012, 10:41 AM
#20
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
-
Sep 15th, 2012, 10:45 AM
#21
Thread Starter
Member
Re: Search for data in a MSHFlexGrid
Originally Posted by Doogle
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
-
Sep 15th, 2012, 10:45 AM
#22
Thread Starter
Member
Re: Search for data in a MSHFlexGrid
Originally Posted by Doogle
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|