Solved

MsFlexGrid to Array

Posted on 2004-04-07
5
1,182 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…

808 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