MsFlexGrid to Array

Posted on 2004-04-07
Last Modified: 2008-01-09
hello experts,
this is a tough one to explain, and may prove a bit tricky, but bear with me.

I have a MsFlexGrid(msData) which I have been able to add a check box to using the following code (see below).
This allows me to multi-select without using ctrl click.

here's the problem;
1.  for each row that is selected, I need to be able to store the value of the 3rd column of the selected row in a variable array.

something like this:

Private Sub MsData_Click()
  Dim LastNames As String
  LastNames = MsData.TextMatrix(MsData.Row, 3)
end sub

the above code does NOT work, but essentially, I want to assign the value as above.

2. when the user deselects the checkbox, the value has to be removed from the array

3. I will then need to use the values in the array in the following SQL statement:
              select * from Table1 where Table1.Field1 = "  & LastNames & "
               so I will need a loop. (Is this possible and if so how do I do it.)

here's the code I used to add the checkbox (COURTESY OF: mgfranz @ ;

For i = 1 To objRec.RecordCount     'objrec is my recordset
     With MsData
          .Row = i: .Col = 0: .CellPictureAlignment = 4 ' Align the checkbox
          Set .CellPicture = picUnchecked.Picture  ' Set the default checkbox picture to the empty box
          .TextMatrix(i, 1) = i
     End With

then the following code is used by the original coder for select and deselect events:
(I have modified it slightly)

Private Sub MsData_Click()
Dim i As Integer
Dim oldx, oldy, cell2text As String, strTextCheck As String

  'this will highlight the current row
   MsData.HighLight = flexHighlightAlways
   MsData.SelectionMode = flexSelectionByRow
   MsData.Col = 0
   MsData.ColSel = MsData.cols - 1
' Check or uncheck the grid checkbox
With MsData
    oldx = .Col
    oldy = .Row
        If .Col = 0 Then
            If .CellPicture = picChecked Then
                Set .CellPicture = picUnchecked
                .Col = .Col + 1  ' I use data that is in column #1, usually an Index or ID #
                strTextCheck = .Text
                ' When you de-select a CheckBox, we need to strip out the #
                strChecked = Replace(strChecked, strTextCheck & ",", "")
                ' Don't forget to strip off the trailing , before passing the string
                Debug.Print strChecked
                Set .CellPicture = picChecked
                .Col = .Col + 1
                strTextCheck = MsData.TextMatrix(MsData.Row, 3)
                strChecked = strChecked & strTextCheck & ","
                Debug.Print strChecked
            End If
        End If
    .Col = oldx
    .Row = oldy
End With
 'For i = 0 To 2
     LastNames = MsData.TextMatrix(MsData.Row, 3)
  'Next i
 Combo1.AddItem LastNames
End Sub

For better clarity, the full code can be downloaded from:- txtCodeId=40094&lngWId=1

Please help, crucial project!!

Thank you!!
Question by:claracruz
  • 3
  • 2

Accepted Solution

CaN earned 500 total points
ID: 10775458
First of all the URL you gave cannot be viewed. It gives an error.

Why don't you just put a button under the grid and user can hit the button when all selections end. When the button clicked you can do something like that:

Private Sub cmdSelectNames_Click()
Dim i as integer
dim lastnames,strSQL as string
with MsData
for i=0 to .Rows-1
if .cellpicture=picChecked then
lastnames=lastnames & "'" & .TextMatrix(i,3) & "',"
end if
next i
end with
lastnames=left(lastnames,len(lastnames)-1) ' To remove the last comma
strSQL="select * from Table1 where Table1.Field1 in (" & lastnames & ")"
End Sub

This should work...


Author Comment

ID: 10775855
hi CaN,
sorry about that, here's the URL again.

a little more help please....(my vb/access is not that great..)
would your code not give the values in the variable LastName as:
name1, name2, name3, name4....
I tried this in access and it gives me a syntax error in access.

thank you...


Expert Comment

ID: 10775975

it shouldn't give an error
LastName becomes a string as


and SQL becomes a string as

select * from Table1 where Table1.Field1 in ('name1','name2','name3','name4')

I checked again and it works in Access

Author Comment

ID: 10776053
Great Stuff CaN, you are a genius, it was my mistake......

Thank you!!!!!!!

Expert Comment

ID: 10776093
You're welcome claracruz.

But I'm not a genius "yet" :)

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

895 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

18 Experts available now in Live!

Get 1:1 Help Now