Solved

MsFlexGrid to Array

Posted on 2004-04-07
5
1,158 Views
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 @ planet-source-code.com) ;

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
Next


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
            Else
                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.Clear
 Combo1.AddItem LastNames
End Sub

For better clarity, the full code can be downloaded from:-
http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp txtCodeId=40094&lngWId=1

Please help, crucial project!!

Thank you!!
0
Comment
Question by:claracruz
  • 3
  • 2
5 Comments
 
LVL 1

Accepted Solution

by:
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
lastnames=""
with MsData
for i=0 to .Rows-1
.col=0
.row=i
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...

CaN®
0
 
LVL 4

Author Comment

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

http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=40094&lngWId=1.

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...

0
 
LVL 1

Expert Comment

by:CaN
ID: 10775975
hi,

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

'name1','name2','name3','name4'

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
0
 
LVL 4

Author Comment

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

Thank you!!!!!!!
0
 
LVL 1

Expert Comment

by:CaN
ID: 10776093
You're welcome claracruz.

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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
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…
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…

706 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

19 Experts available now in Live!

Get 1:1 Help Now