Solved

MsFlexGrid to Array

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Macro which automatically send Excel spreadsheet to Outlook problem 2 65
MsgBox 2 54
Using "ScreenUpdating" 6 63
Error with a code discussed on this page 5 13
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
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…

770 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