INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to search a certain record in mshflexgrid with a value selected from a combobox in vb6

How to search a certain record in mshflexgrid with a value selected from a combobox in vb6

How to search a certain record in mshflexgrid with a value selected from a combobox in vb6

(OP)
Hi All,

I have a form that has a mshflexgrid with data populated from ms access database. A combobox with client names populated from my access database during form load. All is working fine here.
My problem is:
1) to set the focus of the cursor to the record selected from the combobox and
2) highlight the record searched in the mshflexgrid
3) set the color back to default ie vbWhite when another record is searched from the combobox

I have a code here but it is not working the way I intend to.

Dim r As Integer
Dim c As String
For r = 1 To MSHFlexGrid1.Rows - 1
MSHFlexGrid1.Row = r
MSHFlexGrid1.Col = 3
If MSHFlexGrid1.Text = cboSearch.Text Then
MsgBox " Record Found"
With MSHFlexGrid1
.Col = .FixedCols
.ColSel = .Cols - 1
.CellBackColor = vbGreen
.ColSel = .Col
.RowSel = .Row
End With
Exit Sub
Else
MsgBox " Record NOT Found"
End If
Next r

What it's doing is that on clicking a client name in the combobox, it firstly displays 'Msgbox "Record NOT Found"' and sets the focus on the 2nd row instead of positioning the cursor on the first row in column 3 (column where the clients names are stored). The cursor then moves from the first record to the second and so forth upon clicking the OK button of the Msgbox "Record NOT Found" which appears when the cursor loops through each records. When it reaches the record specified in the combobox then it does what I intends it to do and the msgbox also stops showing up.

What I want is, when the user clicks a client name in the cboSearch (combobox) it should automatically highlight that particular client name in the mshflexgrid.

Could you please assist me in getting this problem solved.

Thank you in advance for your time and consideration.

RE: How to search a certain record in mshflexgrid with a value selected from a combobox in vb6

First of all, try to indent your code. It makes things a lot easier to read and understand.
I hope you have enabled auto indentation which is turned on by default. If not, you are strongly advised to do so. (Tools>Options>Auto Indent).

When you indent the above code, it looks like the following and naturally starts making a lot of sense.
___

Dim r As Integer
Dim c As String
For r = 1 To MSHFlexGrid1.Rows - 1
    MSHFlexGrid1.Row = r
    MSHFlexGrid1.Col = 3
    If MSHFlexGrid1.Text = cboSearch.Text Then
        MsgBox " Record Found"
        With MSHFlexGrid1
            .Col = .FixedCols
            .ColSel = .Cols - 1
            .CellBackColor = vbGreen
            .ColSel = .Col
            .RowSel = .Row
        End With
        Exit Sub
    Else
        MsgBox " Record NOT Found"
    End If
Next r
___
 
Looking at the code and symptoms, it is not difficult to revise the For loop and If structure so that it behaves correctly.
___

Dim r As Integer
Dim c As String
For r = 1 To MSHFlexGrid1.Rows - 1
    MSHFlexGrid1.Row = r
    MSHFlexGrid1.Col = 3
    If MSHFlexGrid1.Text = cboSearch.Text Then
        MsgBox " Record Found"
        With MSHFlexGrid1
           .Col = .FixedCols
           .ColSel = .Cols - 1
           .CellBackColor = vbGreen
           .ColSel = .Col
           .RowSel = .Row
        End With
        Exit Sub
    End If
Next r
MsgBox " Record NOT Found" 

RE: How to search a certain record in mshflexgrid with a value selected from a combobox in vb6

That fixes the Found/Not found issue - but not the other requirements the OP included ...

RE: How to search a certain record in mshflexgrid with a value selected from a combobox in vb6

>other requirements

I revisited the question. OK, the code needs to be simplified as follows.
___

Dim r As Integer
MSHFlexGrid1.Col = 3
For r = 1 To MSHFlexGrid1.Rows - 1
    MSHFlexGrid1.Row = r
    If MSHFlexGrid1.Text = cboSearch.Text Then
        MSHFlexGrid1.CellBackColor = vbGreen
        MsgBox " Record Found"
        Exit Sub
    End If
Next r
MsgBox " Record NOT Found"
___
 
And follow event procedure will be needed to reset the highlighted cell.
___

Private Sub MSHFlexGrid1_LeaveCell()
    MSHFlexGrid1.CellBackColor = vbWindowBackground
End Sub
___
 
I haven't tested the code. I hope OP will test and make it work.

RE: How to search a certain record in mshflexgrid with a value selected from a combobox in vb6

Not convinced the Event triggers ...

RE: How to search a certain record in mshflexgrid with a value selected from a combobox in vb6

I would try something like this (a small modification to Hypetia's code):

CODE

Dim r As Integer
Dim b As Boolean

With MSHFlexGrid1
    .Redraw = False
    .Col = 3
    For r = 1 To .Rows - 1
        .Row = r
        If .Text = cboSearch.Text Then
            .CellBackColor = vbGreen
            b = True
        Else
            .CellBackColor = vbWhite
        End If
    Next r
    .Redraw = True
End With

If b Then
    MsgBox "Record Found"
Else
    MsgBox "Record NOT Found"
End If 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: How to search a certain record in mshflexgrid with a value selected from a combobox in vb6

Valiant smile However, none of the code presented guarantees requirement 1: 1) to set the focus of the cursor to the record selected from the combobox ... (I'm just playing here, you understand)

RE: How to search a certain record in mshflexgrid with a value selected from a combobox in vb6

>none of the code presented guarantees requirement

As I said, I leave it to the OP to make amends as required.

RE: How to search a certain record in mshflexgrid with a value selected from a combobox in vb6

OK smile let's "set the focus of the cursor to the record selected from the combobox"

CODE

Dim r As Integer
Dim b As Boolean
Dim intRow As Integer

With MSHFlexGrid1
    .Redraw = False
    .Col = 3
    For r = 1 To .Rows - 1
        .Row = r
        If .Text = cboSearch.Text Then
            intRow = r
            .CellBackColor = vbGreen
            b = True
        Else
            .CellBackColor = vbWhite
        End If
    Next r
    .Redraw = True
End With

If b Then
    With MSHFlexGrid1
        .SetFocus
        .Col = 3
        .Row = intRow
    End With
    MsgBox "Record Found"
Else
    MsgBox "Record NOT Found"
End If 

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close