Solved

VB6 - Send data from 2 MSHFlexgrid to a third MSHFlexgrid without duplication

Posted on 2014-04-12
9
334 Views
Last Modified: 2014-04-13
Hi

I'm trying to send the data from MSHFlexgrid1 and MSHFlexgrid2 into MSHFlexgrid3, but without duplications.

The duplication records are based on Column1, Column2 and Column3 of MSHFlexgrid 1 and 2.

I only want to send a single records of each combinations.

i can also have a multiple rows of data in each grid.

In below picture, you will see an example of MSHFlexgrid1 and 2 data, and what it should look like after the macro ran in MSHFlexgrid3.

3 grids
Can you please help me?

what i have so far is this below:
Dim i As Integer

    For i = 0 To MSHFlexGrid1.Rows - 1

        If MSHFlexGrid1.TextMatrix(i, 0) <> "" Then

            MSHFlexGrid3.TextMatrix(i + 1, 0) = MSHFlexGrid1.TextMatrix(i, 0)
            MSHFlexGrid3.TextMatrix(i + 1, 1) = MSHFlexGrid1.TextMatrix(i, 1)
            MSHFlexGrid3.TextMatrix(i + 1, 2) = MSHFlexGrid1.TextMatrix(i, 2)
            MSHFlexGrid3.Rows = MSHFlexGrid3.Rows + 1
        End If
    Next i

Open in new window


Thanks for your help
0
Comment
Question by:Wilder1626
  • 4
  • 3
  • 2
9 Comments
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 39997162
Change your code to the following.

There is a nicer way to code if you want the code to automatically work out the number of source grids which you wish to scan. I will add another comment in a moment to show that method, but this existing code does exactly what you want.

  'This snippet of code scans two existing MSHFlexGrids and copies any unique
  'records (row data)into a third MSHFlexGrid.
  'You can havae any number of grids but must amend the code lines where indicated 
  ' below  (marked ***)

  Dim nRow1  As Integer  'Row number in source Grid
  Dim nRow3  As Integer  'Row number in destination Grid
  Dim nCol   As Integer    'col number in source grid
  Dim nCol3  As Integer   'Col number in destination grid

  Dim bFound As Boolean   'a flag to see we found an existing record in dest same as                     
                                            'source
  Dim oFlex  As MSHFlexGrid   'a flexgrid which is used in loop to contain source data
  Dim nFlex  As Integer            'the number of the source grid (1-2 in this case)

  With Form1
    .MSHFlexGrid3.Clear        'clear the destination grid of any existing data
    .MSHFlexGrid3.Rows = 0  'an remove any rows (else we may have blanks)

    '***  You need adjust the "To" firgure below to agree with the number of source grids
    ' (2 in this case)
    For nFlex = 1 To 2   'set the max to the base number of flex grids being scanned 
                                    '(We are scanning grids 1 and 2)

       '*** You need to amend the following line if you have more than 2 source grids
      Set oFlex = IIf(nFlex = 1, MSHFlexGrid1, MSHFlexGrid2)  'get the source grid data
      For nRow1 = 0 To oFlex.Rows - 1   
        If oFlex.TextMatrix(nRow1, 0) <> "" Then
          If .MSHFlexGrid3.Rows > 0 Then
            bFound = False 'must initialize this here
            For nRow3 = 0 To .MSHFlexGrid3.Rows - 1
               If (.MSHFlexGrid3.TextMatrix(nRow3, 0) = oFlex.TextMatrix(nRow1, 0)) And _  
                  (.MSHFlexGrid3.TextMatrix(nRow3, 1) = oFlex.TextMatrix(nRow1, 1)) And _ 
                  (.MSHFlexGrid3.TextMatrix(nRow3, 2) = oFlex.TextMatrix(nRow1, 2)) Then
                   bFound = True   'we found an existing record which is the same!
                   Exit For              'so let's get out of here!
               End If
            Next nRow3
          End If
          If Not bFound Then   'aha! a new record!
            .MSHFlexGrid3.Rows = .MSHFlexGrid3.Rows + 1    'ad a row to destination grid
            For nCol3 = 0 To 2
               'copy the data from the working flecgrid to the destination grid
               .MSHFlexGrid3.TextMatrix(.MSHFlexGrid3.Rows - 1, nCol3) = _ 
                             oFlex.TextMatrix(nRow1, nCol3)
            Next nCol3
            End If
        End If
      Next nRow1 
    Next nFlex   'on to the next FlexGrid!
  End With

Open in new window

0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39997165
I dont have vb6 anymore to test with but a few pointers
1. make a routine to do the test for duplicates that you can use for both Grid1 and Grid2
2. add row to destination and then set the data for it
    For i = 0 To MSHFlexGrid1.Rows - 1

        If MSHFlexGrid1.TextMatrix(i, 0) <> "" Then
            CheckAddRow MSHFlexGrid1, i, MSHFlexGrid3
        End If
    Next i
    For i = 0 To MSHFlexGrid1.Rows - 1

        If MSHFlexGrid2.TextMatrix(i, 0) <> "" Then
            CheckAddRow MSHFlexGrid2, i, MSHFlexGrid3
        End If
    Next i


  Sub CheckAddRow( gridSrc as MSHFlexGrid, rowid as integer, gridDest as MSHFlexGrid)
         dim i as integer, j as integer
         'check for duplicate
         dim duplic as boolean
         for i=0 to gridDest.rows-1
              for j=0 to gridSrc.Columns-1
                     if gridSrc.TextMatrix(i , j) = gridSrc.TextMatrix(rowid , j) then
                             duplic = true : exit sub
                     end if
              next j
         next i
         'duplic must be false so continue
         dim newrow as string
         for j=0 to gridSrc.Columns-1
               newrow = vbTab & gridSrc.TextMatrix(rowid , j)
         next j
         'drop the first tab
         newrow = mid$(newrow,2)
          gridDest.AddItem newRow
  End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 39997182
OK.

Now I have tweaked the code so that it finds ALL GRIDS ON THE FORM (so you can have any number provided their name starts with "MSHFlexGrid".

You note in the code the name of the destination grid (in our case MSHFlexGrid3) and the code will ignore that as a source grid (just amend the value of the CONSTANT at the top of the code).

This code is much more flixible in case you want to use more than your existing 2 source grids.

Hope this meets your requirements.

Cheers
Chris (craisin-Australia)

 'This snippet of code scans two existing MSHFlexGrids and copies any unique
  'records (row data)into a third MSHFlexGrid.
  'Note!  The source grids will be processed in any order (not guaranteed to be
  'in alphabetical name order.

  Dim nRow1  As Integer  'Row number in source Grid
  Dim nRow3  As Integer  'Row number in destination Grid
  Dim nCol   As Integer  'Col number in source grid
  Dim nCol3  As Integer  'Col number in destination grid
  Dim oObj   As Object   'used to scan all the objects on the form
  Const DESTINATIONGRID = "MSHFLEXGRID3"    'store the name of your destination grid

  Dim bFound As Boolean  'A flag to see we found an existing record in dest same as
                         'source
  Dim oFlex  As MSHFlexGrid   'A flexgrid which is used in loop to contain source data
  Dim nFlex  As Integer       'The number of the source grid whose data is currently in the working grid "oFlex"
  Dim nSource As Integer      'The number of source grids found
  Dim nCurrentSource As Integer 'the number of the source grid currently being scanned
  
  With Form1
    .MSHFlexGrid3.Clear        'clear the destination grid of any existing data
    .MSHFlexGrid3.Rows = 0  'and remove any rows (else we may have blanks)

    For Each oObj In Form1.Controls
      If oObj.Name Like "MSHFlexGrid*" And oObj.Name <> DESTINATIONGRID Then
        Set oFlex = oObj
      End If
      
      For nRow1 = 0 To oFlex.Rows - 1
        If oFlex.TextMatrix(nRow1, 0) <> "" Then
          If .MSHFlexGrid3.Rows > 0 Then
            bFound = False 'must initialize this here
            For nRow3 = 0 To .MSHFlexGrid3.Rows - 1
               If (.MSHFlexGrid3.TextMatrix(nRow3, 0) = oFlex.TextMatrix(nRow1, 0)) And _
                  (.MSHFlexGrid3.TextMatrix(nRow3, 1) = oFlex.TextMatrix(nRow1, 1)) And _
                  (.MSHFlexGrid3.TextMatrix(nRow3, 2) = oFlex.TextMatrix(nRow1, 2)) Then
                   bFound = True   'we found an existing record which is the same!
                   Exit For              'so let's get out of here!
               End If
            Next nRow3
          End If
          If Not bFound Then   'aha! a new record!
            .MSHFlexGrid3.Rows = .MSHFlexGrid3.Rows + 1    'ad a row to destination grid
            For nCol3 = 0 To 2
               'copy the data from the working flecgrid to the destination grid
               .MSHFlexGrid3.TextMatrix(.MSHFlexGrid3.Rows - 1, nCol3) = _
                             oFlex.TextMatrix(nRow1, nCol3)
            Next nCol3
            End If
        End If
      Next nRow1
    Next oObj   'on to the next FlexGrid!
  End With

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 39997230
Hi craisin, i had the chance to start looking at your code.

We are almost there, and i really like your code.  but i may have some questions.

I like the macro in post: 39997162.

Since i have tweaked the app, i have put the 3 grids in 3 different forms. I was able to update the macro based on this.

But now, here is my questions

Previously MSHFlexgrid1 = Form41.MSHFlexgrid1
Previously MSHFlexgrid2 = Form3.MSHFlexgrid1
Previously MSHFlexgrid3 = Form52.MSHFlexgrid1

The question i have is that now, in Form41.MSHFlexgrid1, the 3 columns needed are Column 0, 2, 4

In Form3.MSHFlexgrid1, the 3 columns need are 1, 19, 21.

But the result in Form52.MSHFlexgrid1 are still in column 0,1,2

How can i update the code to be column specific depending on the grids?

  'This snippet of code scans two existing MSHFlexGrids and copies any unique
  'records (row data)into a third MSHFlexGrid.
  'You can havae any number of grids but must amend the code lines where indicated
  ' below  (marked ***)

  Dim nRow1  As Integer  'Row number in source Grid
  Dim nRow3  As Integer  'Row number in destination Grid
  Dim nCol   As Integer    'col number in source grid
  Dim nCol3  As Integer   'Col number in destination grid

  Dim bFound As Boolean   'a flag to see we found an existing record in dest same as
                                            'source
  Dim oFlex  As MSHFlexGrid   'a flexgrid which is used in loop to contain source data
  Dim nFlex  As Integer            'the number of the source grid (1-2 in this case)

  With Form52
    .MSHFlexGrid1.clear        'clear the destination grid of any existing data
    .MSHFlexGrid1.Rows = 0  'an remove any rows (else we may have blanks)

    '***  You need adjust the "To" firgure below to agree with the number of source grids
    ' (2 in this case)
    For nFlex = 1 To 2  'set the max to the base number of flex grids being scanned
                                    '(We are scanning grids 1 and 2)

       '*** You need to amend the following line if you have more than 2 source grids
      Set oFlex = IIf(nFlex = 1, Form41.MSHFlexGrid1, Form3.MSHFlexGrid1)  'get the source grid data
      For nRow1 = 0 To oFlex.Rows - 1
        If oFlex.TextMatrix(nRow1, 0) <> "" Then
          If Form52.MSHFlexGrid1.Rows > 0 Then
            bFound = False 'must initialize this here
            For nRow3 = 0 To Form52.MSHFlexGrid1.Rows - 1
               If (Form52.MSHFlexGrid1.TextMatrix(nRow3, 0) = oFlex.TextMatrix(nRow1, 0)) And _
                  (Form52.MSHFlexGrid1.TextMatrix(nRow3, 1) = oFlex.TextMatrix(nRow1, 1)) And _
                  (Form52.MSHFlexGrid1.TextMatrix(nRow3, 2) = oFlex.TextMatrix(nRow1, 2)) Then
                   bFound = True   'we found an existing record which is the same!
                   Exit For              'so let's get out of here!
               End If
            Next nRow3
          End If
          If Not bFound Then   'aha! a new record!
            Form52.MSHFlexGrid1.Rows = Form52.MSHFlexGrid1.Rows + 1    'ad a row to destination grid
            For nCol3 = 0 To 2
               'copy the data from the working flecgrid to the destination grid
               Form52.MSHFlexGrid1.TextMatrix(Form52.MSHFlexGrid1.Rows - 1, nCol3) = _
                             oFlex.TextMatrix(nRow1, nCol3)
            Next nCol3
            End If
        End If
      Next nRow1
    Next nFlex   'on to the next FlexGrid!
  End With
   Form52.Show

Open in new window

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 13

Expert Comment

by:Chris Raisin
ID: 39997265
Change the block of code found in lines 32 to 34 above to the following:

For nRow3 = 0 To Form52.MSHFlexGrid1.Rows - 1
   If (Form52.MSHFlexGrid1.TextMatrix(nRow3, 0) = _
            oFlex.TextMatrix(nRow1, IIf(nFlex = 1, 0, 1))) And _
      (Form52.MSHFlexGrid1.TextMatrix(nRow3, 1) = _
            oFlex.TextMatrix(nRow1, IIf(nFlex = 1, 2, 19))) And _
      (Form52.MSHFlexGrid1.TextMatrix(nRow3, 2) = _
            oFlex.TextMatrix(nRow1, IIf(nFlex = 1, 4, 21))) Then
          bFound = True   'we found an existing record which is the same!
          Exit For              'so let's get out of here!
   End If
Next nRow3

Open in new window

0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 200 total points
ID: 39997267
not easily.  you will need to treat each grid separately as the use of columns is a breaking change.  we needed to know beforehand.

my method adjusted below to use an array of columns to match and copy...
each row of the array represents a source and destination column...

    
redim matchCols(3,2) as integer
matchCols(1,1)=0 : matchCols(1,2)=0
matchCols(2,1)=2 : matchCols(2,2)=1
matchCols(3,1)=4 : matchCols(3,2)=2
For i = 0 To Form41.MSHFlexgrid1.Rows - 1

        If Form41.MSHFlexgrid1.TextMatrix(i, 0) <> "" Then
            CheckAddRow Form41.MSHFlexgrid1, i, matchCols, Form52.MSHFlexgrid1
        End If
    Next i
    matchCols(1,1)=0 : matchCols(1,2)=0
    matchCols(2,1)=2 : matchCols(2,2)=1
    matchCols(3,1)=4 : matchCols(3,2)=2
    For i = 0 To Form3.MSHFlexgrid1.Rows - 1

        If Form3.MSHFlexgrid1.TextMatrix(i, 0) <> "" Then
            CheckAddRow Form3.MSHFlexgrid1, i, matchCols, Form52.MSHFlexgrid1
        End If
    Next i


  Sub CheckAddRow( gridSrc as MSHFlexGrid, rowid as integer, arrCol(), gridDest as MSHFlexGrid)
         dim i as integer, j as integer
         dim srcCol as integer, destCol as integer
         'check for duplicate
         dim duplic as boolean
         for i=0 to gridDest.rows-1
              for j=1 to ubound(arrCol)
                     srcCol = arrCol(j,1): destCol = arrCol(j,2)
                     if gridSrc.TextMatrix(i ,destCol ) = gridSrc.TextMatrix(rowid ,srcCol ) then
                             duplic = true : exit sub
                     end if
              next j
         next i
         'duplic must be false so continue
         dim newrow as string
         for j=1 to ubound(arrCol)
                     srcCol = arrCol(j,1): destCol = arrCol(j,2)
               newrow = vbTab & gridSrc.TextMatrix(rowid , srcCol )
         next j
         'drop the first tab
         newrow = mid$(newrow,2)
          gridDest.AddItem newRow
  End Sub 

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 39997269
Hi  Robberbaron (robr) and craisin

I will test both right now and will let you know.
0
 
LVL 13

Accepted Solution

by:
Chris Raisin earned 300 total points
ID: 39997290
Oops, there is one more line to change in my code:
Change applicable line as folows:

For nCol3 = 0 To 2
     'copy the data from the working flexgrid to the destination grid
     Select case nCol3
         Case 0
             Form52.MSHFlexGrid1.TextMatrix(Form52.MSHFlexGrid1.Rows - 1, nCol3) = _
             oFlex.TextMatrix(nRow1, iif(nFlex=1,0,1))
         Case  1
             Form52.MSHFlexGrid1.TextMatrix(Form52.MSHFlexGrid1.Rows - 1, nCol3) = _
             oFlex.TextMatrix(nRow1, iif(nFlex=1,2,19))
         Case 3
             Form52.MSHFlexGrid1.TextMatrix(Form52.MSHFlexGrid1.Rows - 1, nCol3) = _
             oFlex.TextMatrix(nRow1, iif(nFlex=1,4,21))
      End Select
 Next nCol3

Open in new window

0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 39997872
Thanks a lot for all your help. It is now working great, and do exactly what i wanted.

Again, Thanks
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now