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

[namax]

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.

 

[Hypetia]

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.
[pre]
___

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
___
[/pre]
Looking at the code and symptoms, it is not difficult to revise the For loop and If structure so that it behaves correctly.
[pre]
___

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"[/pre]

 

[strongm]

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

 

[Hypetia]

>other requirements

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

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"
___
[/pre]
And follow event procedure will be needed to reset the highlighted cell.
[pre]
___

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

 

[strongm]

Not convinced the Event triggers ...

 

[Andrzejek]

I would try something like this (a small modification to Hypetia's 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.

 

[strongm]

Valiant 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)

 

[Hypetia]

>none of the code presented guarantees requirement

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

 

[Andrzejek]

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

Dim r As Integer
Dim b As Boolean[blue]
Dim intRow As Integer[/blue]

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

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

Have fun.

---- Andy

There is a great need for a sarcasm font.