Solved

MsFlexGrid to Array

Posted on 2004-04-07
5
1,189 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
passing a value with stream reader AFTER a ";" 3 84
VB6 - Compare and highlight cell not the same 3 57
Help me. 3 71
VB script to continue despite error 2 58
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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 …
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

697 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