asked on

VB6- Validate 4 fields between 2 grid

Hi all

I would like to validate by clicking on the command button 4 fields from MSHFlexgrid1 with my other grid called IB_GRID following this logic:

If Combo1 = IB_GRID column 0
and MSHFlexgrid1 column 13 = IB_GRID column 1
and MSHFlexgrid1 column 2 >= IB_GRID column 2 and <= IB_GRID column3 then

If MSHFlexgrid1 column 14 <> IB_GRID column 4 then issue.text = MSHFlexgrid1. column 3 & MSHFlexgrid1. column 14 and IB_GRID column 4.

How can i do this ?

Thanks again
Form1.zip

Martin Liss

Will all the entries in IB_Grid column 0 always be the same as the value of Combo1? In other words when Combo1 = "ONTARIO" will all the entries in column 0 of IB_Grid be "ONTARIO"?

Martin Liss

Anyhow try this:

Private Sub Command1_Click()

Dim lngIB_Row As Long
Dim lngMSH_Row As Long

' This assumes that row 0 in IB_GRID is reserved for headings
For lngIB_Row = 1 To IB_GRID.Rows - 1
    If Combo1.Text = IB_GRID.TextMatrix(lngIB_Row, 0) Then
        For lngMSH_Row = 1 To MSHFlexGrid1.Rows - 1
            If MSHFlexGrid1.TextMatrix(lngMSH_Row, 13) = IB_GRID.TextMatrix(lngIB_Row, 1) And _
               MSHFlexGrid1.TextMatrix(lngMSH_Row, 2) >= IB_GRID.TextMatrix(lngIB_Row, 2) And _
               MSHFlexGrid1.TextMatrix(lngMSH_Row, 2) <= IB_GRID.TextMatrix(lngIB_Row, 3) Then
                If MSHFlexGrid1.TextMatrix(lngMSH_Row, 14) <> IB_GRID.TextMatrix(lngIB_Row, 4) Then
                    issue.Text = MSHFlexGrid1.TextMatrix(lngMSH_Row, 3) & " " & MSHFlexGrid1.TextMatrix(lngMSH_Row, 14) & " " & IB_GRID.TextMatrix(lngIB_Row, 0)
                    Exit Sub
                End If
            End If
        Next
    End If
Next
End Sub

Open in new window

Wilder1626

ASKER

Hi MartinLiss,

In combo1, i have 4 values in my combo to be : WEST, ONTARIO, QUEBEC, ATLANTIC.

Those 4 values will also be the values in grid IB_GRID column 0.

It looks like it's working great.

Let me do some more test and i will let you know shortly.

Thanks again

Wilder1626

ASKER

Ok, i see one small issue. when it hit Exit Sub, it should still validate all the other rows from MSHFlexgrid1. Now, what it does is that as soon it found an mismatch, it stop there and dont continue to validate the other rows.

Dim lngIB_Row As Long
Dim lngMSH_Row As Long

' This assumes that row 0 in IB_GRID is reserved for headings
For lngIB_Row = 1 To IB_GRID.Rows - 1
    If Combo1.Text = IB_GRID.TextMatrix(lngIB_Row, 0) Then
        For lngMSH_Row = 1 To MSHFlexGrid1.Rows - 1
            If MSHFlexGrid1.TextMatrix(lngMSH_Row, 13) = IB_GRID.TextMatrix(lngIB_Row, 1) And _
               MSHFlexGrid1.TextMatrix(lngMSH_Row, 2) >= IB_GRID.TextMatrix(lngIB_Row, 2) And _
               MSHFlexGrid1.TextMatrix(lngMSH_Row, 2) <= IB_GRID.TextMatrix(lngIB_Row, 3) Then
                If MSHFlexGrid1.TextMatrix(lngMSH_Row, 14) <> IB_GRID.TextMatrix(lngIB_Row, 4) Then
                   issue.Text = issue.Text & vbCrLf & "Bol: " & MSHFlexGrid1.TextMatrix(lngMSH_Row, 3) & " requested with rate group: " & MSHFlexGrid1.TextMatrix(lngMSH_Row, 15) & " when it should be: " & IB_GRID.TextMatrix(lngIB_Row, 4)
                    Exit Sub
                End If
            End If
        Next
    End If
Next

Open in new window

Martin Liss

This corrects that problem

Private Sub Command1_Click()

Dim lngIB_Row As Long
Dim lngMSH_Row As Long

issue.Text = ""
' This was done in the IDE
'issue.MultiLine = True

' This assumes that row 0 in IB_GRID is reserved for headings
For lngIB_Row = 1 To IB_GRID.Rows - 1
    If Combo1.Text = IB_GRID.TextMatrix(lngIB_Row, 0) Then
        For lngMSH_Row = 1 To MSHFlexGrid1.Rows - 1
            If MSHFlexGrid1.TextMatrix(lngMSH_Row, 13) = IB_GRID.TextMatrix(lngIB_Row, 1) And _
               MSHFlexGrid1.TextMatrix(lngMSH_Row, 2) >= IB_GRID.TextMatrix(lngIB_Row, 2) And _
               MSHFlexGrid1.TextMatrix(lngMSH_Row, 2) <= IB_GRID.TextMatrix(lngIB_Row, 3) Then
                If MSHFlexGrid1.TextMatrix(lngMSH_Row, 14) <> IB_GRID.TextMatrix(lngIB_Row, 4) Then
                    issue.Text = issue.Text & _
                    MSHFlexGrid1.TextMatrix(lngMSH_Row, 3) & " " & _
                    MSHFlexGrid1.TextMatrix(lngMSH_Row, 14) & " " & _
                    IB_GRID.TextMatrix(lngIB_Row, 0) & vbCrLf
                End If
            End If
        Next
    End If
Next
End Sub

Open in new window

Martin Liss

You may like this better. It replaces the issues textbox with a 3rd grid. The two advantages of that are better formatting and the user can't change the text.
Q-28256515.vbp

Wilder1626

ASKER

Hi again

sorry to ask the question but how can i open the vbp in attachment?

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

Thanks, i'm seeing that with firefox, the link don't work.

OK let me take a look at this now and i will let you know.