DataGrid or Flexgrid for Multiple Selections?

Which control do I use? I use an ADO Control to fill the
Datagrid/Flexgrid with database information. After that is done, I
want to give the user the ability to select multiple records. BUT,
after the user selects the multiple records, I want the ability to use
a field ("recordid") in each of the records to UPDATE the database. I
have tried using both of these controls, and I can get it to select
multiple selections, but I can not retrieve a field within each
record.

Which control do I use, Datagrid or Flexgrid? Then, how can I allow
the user to select multiple records and then UPDATE the database
according to the records chosen (using one field within each record)

I hope I can get some help with this. So far, I have been getting the
overall answer that it can not be done.

Thanks for any help,
Reaper

I'm thinking that you will probably have an easier time with this
if you throw away your datacontrol.
Datacontrols while useful for "quick" setup and simple data manipulation,
are... well, just plain evil.

I'd suggest loading up a recordset, slapping it onto a flexgrid, and
then through code, determine what rows are selected and
use .edit and .update methods on those you need to.

my 2 cents.

Without using the ADODC (data control) I am not sure how to load the Flexgrid with information? Also, not sure how to, through code, determine what row(s) the user selected and be able to UPDATE the database using a field in records ("recordid")?

Thanks for the help,
Reaper

I'm with JPicasso... I avoid the ADODC at all costs, and I would suggest the Flexgrid.

To load the grid you will have to establish a data connection and create a recordset (my choice would be ADO) and then looping through the records, you place the values in the appropriate grid cells. You can use the TextMatrix property to populate the individual cells:

VB Code:

MSFlexGrid1.TextMatrix(Row, Col) = "Text"

The row a user cliks will be returned in the MSFlexGrid1.Row property.

Hope that's a leg-up

I would like to test it, but I am not sure that I know how. Database that I will be loading from will be dynamic (there could be 5 columns or 30 columns) so I am not sure of how to load the data into each cell, if they will always be different? Also, how would I make the Flexgrid "editable"? I was under the impression that the Flexgrid is not editable and the user needs to be able to change data in each record.

Thanks,
Reaper

The Flexgrid is not directly editable, but it is not difficult to accomplish.

In short, you need a borderless, invisible textbox on the form with your grid. When the user clicks on a cell, the textbox is moved and sized to cover the cell, made visible and given the focus. All user editting is done in the textbox. When user is done, the text is copied to the cell, the textbox is cleared and made invisible, awaiting its next use. This is all transparent to the user of course.

The attached zip file, contains a small app in VB6 which illustrates this procedure

... of course, you will have to update the database at some point. This is accomplished similarly to populating the grid.

Thanks, I will mess around with this code to see if I can get it to work.

Thanks for the help,
Reaper

This Flexgrid example. Can I use it to select multiple records? If I select multiple records, can I access one of the fields in order to update all of the records at once (for instance, I would be using the "recordid" field to update all of the records selected)?

Thanks,
Reaper

To select multiple rows:

When the user clicks on a row, test to see if it is already selected (by background color), if not change the background color to something to represent 'Selected' (blue for example). If it's already blue, you can change it back to white for 'Deselected'.

To update the records"

You will have to loop through all the rows in the flexgrid, checking to see if the row is selected. If not, skip it. If selected, you can then use your recordid field to choose the record to be updated, then update according the the data connection method you are using.

This is probably going to be a simple question. But I am using the code that you sent, but it keeps saying that I have not initialized a variable yet, txtGridEdit. Where do you initialize it?

This is what I have so far, with some Database text.

I am trying to test the Datagrid approach and the Flexgrid approach so that I can give my boss a choice and let him choose which one he likes (functionality, mainly).

Thanks, again,
Reaper

Quote:

---

Originally posted by reaper1973
This is probably going to be a simple question. But I am using the code that you sent, but it keeps saying that I have not initialized a variable yet, txtGridEdit. Where do you initialize it?

---

txtGridEdit is not a variable, it is the textbox

Two choices:

1. Add a textbox to the form with the grid --
name: txtGridEdit, border: no, visible: no, placement and size don't matter

2. At run-time create a textbox in code, same properties.

In Form_Load event (the form with the grid), it helps to put the following:

VB Code:

txtGridEdit.Text = ""

this ensures the textbox loads at form load.

Thanks for the advice. I have tried using the Datagrid and the Flexgrid, and, actually, I like the Flexgrid, for asthetics.

Just need a little help, though. I put the textbox on the grid. I would assume in order to update the database, after the person leaves the cell, I would put that event in the "Private Sub MSHFlexGrid1_LeaveCell()", right? I would just open the database, and use an UPDATE statement to the database, I think.

Also, in the "Select multiple rows," you said to check the color. Where would I put this event, and what function of the Flexgrid would I use, the "MSHFlexGrid1.BackColor," but that comes up with a number, how would I get it to recognize a color name, for instance, "Blue."

Then, one last question. I got the column to autosize in the Datagrid, no matter how many columns, nor how big the columns. How would you do that in the Flexgrid? I want it to check the column header and the text in the column and use which ever one is bigger.

Thanks for all of the help, I am still learning this stuff, I know alot of the basics of VB, but this project they got me to work on is a little complicated to me (and the other programmers here, for that matter).

Thank you for your patience with this "newbie,"
Reaper

Alright, I have been working on this most of the day. When I do a MSHFlexGrid1.CellBackColor it gives me a number (-2147483643). I am not sure what color this is?

When I run the program, after you highlight the rows to be selected (mouse click on the first row, and then drag the mouse down for the rows that is to be selected), all of the "selected rows" turn blue, but not vbBlue. How can I check the rows that where selected (the blue the computer uses) in my "Update Selected" button?

Here is the code that I have for the "Update Selected" button:

Code:

---

    For iTemp = 0 To MSHFlexGrid1.Rows - 1
        MSHFlexGrid1.Col = 2
        MSHFlexGrid1.Row = iTemp
        If MSHFlexGrid1.CellBackColor = -2147483643 Then
'            MsgBox (MSHFlexGrid1.Text)
        End If
    Next iTemp

---

This will give me the value of the second column in each record (when you select a bunch of records, the very first record that was clicked on to start selecting records turns to a white color, while the rest of the selection turns into the "computer blue"). Can I use this to check each row for the CellBackColor, somehow? And what color do I use? The vbBlue is not the same as the "Computer Blue."

Thanks,
Reaper

I use a datagrid and the following works great..

Private Sub DataGridKicker_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
if BooleanValue
Me.DataGridKicker.Col = 0
If Me.DataGridKicker.Text <> "Y" Then
Me.DataGridKicker.Text = "Y"
Else
Me.DataGridKicker.Text = " "
End If
End If

End Sub

---one of the fields displayed is a field in the table called "special" and I set it as col=0. After making all my selections, I simpley go back through the recordset and execute code for each record with a "Y" in the special field.

Quote:

---

I put the textbox on the grid. I would assume in order to update the database, after the person leaves the cell, I would put that event in the "Private Sub MSHFlexGrid1_LeaveCell()", right? I would just open the database, and use an UPDATE statement to the database, I think.

---

I would probably not put the code to update the database in the LeaveCell event as this will fire everytime the user leaves any cell. I would probably include a button to allow the user to save changes. Then loop through the grid, checking for which records to update and use our update statement.

Quote:

---

I got the column to autosize in the Datagrid, no matter how many columns, nor how big the columns. How would you do that in the Flexgrid? I want it to check the column header and the text in the column and use which ever one is bigger.

---

Don't know of an easy way to do this with a Flexgrid.

Quote:

---

Alright, I have been working on this most of the day. When I do a MSHFlexGrid1.CellBackColor it gives me a number (-2147483643). I am not sure what color this is?

---

It's supposed to be the highlight color (vbHighlight), but honestly, I don't know what color its returning...

What I actually had is mind was: at Form_Load or whenever you populate the grid, loop through all rows initializing the colors...

VB Code:

With MSHFlexGrid1
    For X = 1 to .Cols-1
        .CellBackColor = vbWindowBackground
        .CellForeColor = vbWindowText
    Next X
End With

If you don't initialize the backcolor, it will return the color as 0.

When the user selects a row, if its not selected (CellBackColor = vbWindowBackground)...

VB Code:

With MSHFlexGrid1
    For X = 1 to .Cols-1
        .CellBackColor = vbHighlight
        .CellForeColor = vbHighlightText
    Next X
End With

... if it was selected return the colors to vbWindowBackground and vbWindowText appropriately.

By setting the colors yorself, you can test for color -- vbWindowBackground = not selected, vbHighlight = selected. (I should mention that you should set the FocusRect to 'Light')

If this is the same grid that you will allow the user to edit the cells (it probably is), then you should probably limit the selection of the rows to when the user clicks the Fixed Column to avoid interfering with the textbox, etc.

Hope this makes more sense

The main problem that I have been having, and are still having, is that when the user selects a cell, it will only highlight the first cell. In other words, if the user single clicks on the cell, only the first cell in the record will be highlighted. If you CTRL+click cells, it will only highlight the very first cell. I have been trying to get it to do a for...loop to go through the whole record and highlight every cell, but it will not do it.

BTW, thanks for all of your help Mr. McKernan and everyone else that has contributed. I have been trying everyone's suggestions so that I can get a better understanding of VB. So then, next time, I can answer questions, too. :)

Thanks,
Reaper

With help, of course :), I figured out how to "Autosize" the columns, but, I can not figure out how to get the program to look at the WHOLE Flexgrid. The way I have it set up now is that is looks at just the rows that are visible. But, I want it to look at the ENTIRE Flexgrid and "Autosize" by all of the text. Here is the code that I used for that, but I can not figure out how to "Autosize" the entire Flexgrid, not just the visible rows.

Thanks,
Reaper

Code:

---

Public Sub ChangeColumnWidth()

    'Sets column width to width of longest visible text in column
    Dim Flex As MSHFlexGrid
    Dim lngMaxLength As Long
    Dim intCounter As Integer
    Dim lngLength As Long
    Dim lngWidth As Long
    Dim lngMinWidth As Long
    Dim i As Integer
    Dim Parent As Form
   
    Set Flex = MSHFlexGrid1
    Set Parent = Flex.Parent

    Flex.Redraw = False
    Flex.FocusRect = flexFocusNone
   
    For i = 1 To Flex.Cols - 1
        Flex.Row = 0
        Flex.Col = i
        If (i <> Flex.Cols) Then
                intCounter = Flex.TopRow
                'get minsize of column from column header
                With Parent
                    .Font = Flex.FontFixed
                    .FontSize = Flex.CellFontSize
                    .FontBold = Flex.CellFontBold
                    .FontItalic = Flex.CellFontItalic
                End With
                lngMinWidth = Parent.TextWidth(Flex.TextMatrix(0, i))
                lngMaxLength = 0
   
            Do While Flex.RowIsVisible(intCounter)
                Flex.Row = intCounter
                With Parent
                    .Font = Flex.CellFontName
                    .FontSize = Flex.CellFontSize
                    .FontBold = Flex.CellFontBold
                    .FontItalic = Flex.CellFontItalic
                End With
               
                lngLength = Parent.TextWidth(Flex.TextMatrix(intCounter, i))
                If lngMaxLength < lngLength Then
                    lngMaxLength = lngLength
                   
                End If
           
                intCounter = intCounter + 1
                If Flex.Rows = intCounter Then
                    Exit Do
                End If
            Loop
            lngWidth = lngMaxLength
            If lngWidth < lngMinWidth Then
                lngWidth = lngMinWidth
            End If
           
            Flex.ColWidth(i) = lngWidth + 100
           
        End If
    Next i
   
    Flex.FocusRect = flexFocusLight
    Flex.Redraw = True
   
End Sub

---

Quote:

---

Originally posted by reaper1973
BTW, thanks for all of your help Mr. McKernan ...

Thanks,
Reaper

---

My pleasure

While using the Datagrid, after I select a range of records, it only selects the first record that I clicked on. Why can I not get it to select the WHOLE range of records? Would it be in the MouseMove property of the Datagrid?

Reaper

Also, I have a beginning screen that gives the user a choice of selecting a DSN (already put on the user's computer). The code that I use to fill the DSN combo box fills it with ALL of the DSNs available on the computer.
Is there a way that I can only fill the DSN combo box with USER DSNs, only?

Reaper

frmDSNLogin:

Code:

---

Option Explicit
' These constant variables are used in loading the DSN Names
Const SQL_SUCCESS As Long = 0
Const SQL_FETCH_NEXT As Long = 1

' Load DSN Names from the ODBC.dll file
Private Declare Function SQLDataSources Lib "ODBC32.DLL" (ByVal henv As _
Long, ByVal fDirection As Integer, ByVal szDSN As String, ByVal cbDSNMax As _
Integer, pcbDSN As Integer, ByVal szDescription As String, ByVal _
cbDescriptionMax As Integer, pcbDescription As Integer) As Integer
Private Declare Function SQLAllocEnv Lib "ODBC32.DLL" (env As Long) As _
Integer

Private Sub cboDSN_Click()
    Call LoadCboTables 'Load the Tables to the cboTables control after a DSN is selected
   
End Sub

Private Sub cmdCancel_Click()
    End
   
End Sub

Private Sub cmdOK_Click()
    ' Creating a fMainForm (Form Object) using frmMain
    Dim fMainForm As frmMain
    Set fMainForm = New frmMain
   
    ' Checks to see if the user selected anything, if not, it will prompt the user to make a selection
    If (cboDSN.Text <> "(None)") And (cboTables.Text <> "(None)") Then
        fMainForm.DSN_Name = cboDSN.Text 'Assign cboDSN.Text into a variable (DSN_Name) for frmMain
        fMainForm.Table_Name = cboTables.Text 'Assign cboTables.Text into a variable (Table_Name) for frmMain
        fMainForm.Form_LoadMe
        fMainForm.Show
        frmDSNLogin.Hide
    Else
        MsgBox ("A Data Source Name and a Table MUST be selected.")
    End If
   
End Sub

Private Sub Form_Load()
    Call LoadCboDsn 'Load the cboDSN box with DSN values from the function

End Sub

Private Sub LoadCboDsn() 'Function to retrieve and load DSNs into the cboDSN
    On Error Resume Next

    Const iDsnMaxLen As Integer = 1024
    Const iDrvMaxLen As Integer = 1024

    Dim iRslt As Integer
    Dim sDSNItem As String * iDsnMaxLen
    Dim sDRVItem As String * iDrvMaxLen
    Dim sDSN As String
    Dim iDSNLen As Integer
    Dim iDRVLen As Integer
    Dim lHenv As Long    'handle to the environment

    'get the DSNs
    If (SQLAllocEnv(lHenv) <> -1) Then
        Do
            sDSNItem = Space$(iDsnMaxLen)
            iRslt = SQLDataSources(lHenv, SQL_FETCH_NEXT, _
                  sDSNItem, iDsnMaxLen, iDSNLen, _
                  sDRVItem, iDrvMaxLen, iDRVLen)
            sDSN = Left$(sDSNItem, iDSNLen)
           
            'Check to see if it is a system DSN, if so, do not add to cboDSN
            If (sDSN <> "MS Access Database") And (sDSN <> "dBASE Files") And (sDSN <> "Excel Files") And (sDSN <> "Visual FoxPro Database") And (sDSN <> "Visual FoxPro Tables") And (sDSN <> "dBase Files - Word") And (sDSN <> "FoxPro Files - Word") And (sDSN <> "FoodMart 2000") And (sDSN <> "CROR8V36") And (sDSN <> "CRSS") And (sDSN <> "CRXMLV36") And (sDSN <> "Xtreme Sample Database") And (sDSN <> "CE8") And (sDSN <> "TikkisDb") And (sDSN <> "MQIS") Then
                If (sDSN <> Space$(iDSNLen)) Then
                    cboDSN.AddItem sDSN
                End If
            End If
        Loop Until (iRslt <> SQL_SUCCESS)
    End If

End Sub

Private Sub LoadCboTables() 'Function to retrieve and load tables into the cboTables
    Dim adoConn As ADODB.Connection
    Dim rstSchema As ADODB.Recordset
    Dim strCnn As String
       
    Set adoConn = New ADODB.Connection
    strCnn = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=" & cboDSN.Text & ";"
    adoConn.Open strCnn
       
    Set rstSchema = adoConn.OpenSchema(adSchemaTables)
   
    Do Until rstSchema.EOF
        ' Only load tables, into the cboTables, that have "Processed" within the name
        If (InStr(rstSchema!Table_Name, "Processed")) Then
            cboTables.AddItem (rstSchema!Table_Name)
        End If
       
        rstSchema.MoveNext
    Loop
    rstSchema.Close
   
    adoConn.Close

End Sub

---

Also, Mr. McKernan, you had said that I need to "(I should mention that you should set the FocusRect to 'Light') "

Does you mean like this (code below)? Also, I have read the help files on FocusRect, and still do not understand what is this used for? I thought that I am using the CellBackColor?

Reaper

Quote:

---

Originally posted by reaper1973
When I use this code to select the column(s) to highlight, why does it only highlight the first column selected? I thought I read the code correctly and it will select the entire column.

Code:

---

Private Sub MSHFlexGrid1_Click()
    Dim iTemp As Integer
   
    If MSHFlexGrid1.CellBackColor = vbWindowBackground Then
        With MSHFlexGrid1
            For iTemp = 1 To .Cols - 1
                .CellBackColor = vbHighlight
                .CellForeColor = vbHighlightText
                .FocusRect = flexFocusLight
            Next iTemp
        End With
    ElseIf MSHFlexGrid1.CellBackColor = vbHighlight Then
        With MSHFlexGrid1
            For iTemp = 1 To .Cols - 1
                .CellBackColor = vbWindowBackground
                .CellForeColor = vbWindowText
                .FocusRect = flexFocusNone
            Next iTemp
        End With
    End If

End Sub

---

---

This will highlight an entire row. To highlight a column change
'.Cols' to '.Rows'.

Is that what you mean?

Quote:

---

Originally posted by reaper1973
Also, when the user leaves the cell(s) that they are updating, and I want to update the entire page but using an "Save Changes to Database" button, like John McKernan had suggested, how do I get it to look at the whole flexgrid and UPDATE the database of the changes that the user made?

---

Maybe I misunderstood, but I thought the reason for the user selecting the records (ie: changing cell background) was to choose which records to update. If that is the case, you can simply loop through all the rows, testing for the ones with the highlight color and update those...

VB Code:

With MSHFlexGrid1
    Dim iTemp
 
    .Col = 1    'or any appropriate column
    For iTemp = 1 to .Rows - 1
        If .CellBackColor = vbHighlight then
            'insert code to update record
        End If
    Next iTemp

... if that is not the case than you can add a hidden column (just set MSHFlexGrid1.ColWidth = 0) and place a value in that column to indicate whether or not changes were made. When the user edits a cell, you can update the value in this hidden column appropriately. Then, when ready to save changes, just loop through the rows (similarly to above) and text the value of the hidden column to determine whether the record should be saved.

I tried that. It still only highlights the first column in the row. I want everytime the user selects a row, the whole row will be highlighted.

Code:

---

Private Sub MSHFlexGrid1_Click()
    Dim iTemp As Integer
   
    If MSHFlexGrid1.CellBackColor = vbWindowBackground Then
        With MSHFlexGrid1
            For iTemp = 1 To .Rows - 1
                .CellBackColor = vbHighlight
                .CellForeColor = vbHighlightText
                .FocusRect = flexFocusLight
            Next iTemp
        End With
    ElseIf MSHFlexGrid1.CellBackColor = vbHighlight Then
        With MSHFlexGrid1
            For iTemp = 1 To .Rows - 1
                .CellBackColor = vbWindowBackground
                .CellForeColor = vbWindowText
                .FocusRect = flexFocusNone
            Next iTemp
        End With
    End If

End Sub

---

This code only highlights the first column in the row selected.

Does that make any sense?

(As you can see, I have a few questions before this post, too). I have been extremely busy tinkering around with the program here at work.

Reaper

Quote:

---

Originally posted by reaper1973
Also, Mr. McKernan, you had said that I need to "(I should mention that you should set the FocusRect to 'Light') "... I have read the help files on FocusRect, and still do not understand what is this used for? I thought that I am using the CellBackColor?

Reaper

---

You are using the CellBackColor. FocusRect determines the Focus Rectangle that the flexgrid draws around the selected cell. The choices are 'flexFocusNone', 'felxFocusLight' and 'flexFocusHeavy'.
The obvious choice is flexFocusNone, but if you use this, the flexgrid uses its own highlight color. I've had cases where this highlight color (although it is supposed to be vbHighlight) returns a different color in code. That's why I usually choose flexFocusLight, it puts up the least obtrusive focus rectangle, flexFocusHeavy is just too thick for my tastes.

I fixed it.

VB Code:

Private Sub MSHFlexGrid1_Click()
    Dim iTemp As Integer
    
    If MSHFlexGrid1.CellBackColor = vbWindowBackground Then
        With MSHFlexGrid1
            For iTemp = 1 To .Cols - 1
                .Col = iTemp
                .CellBackColor = vbHighlight
                .CellForeColor = vbHighlightText
            Next iTemp
        End With
    ElseIf MSHFlexGrid1.CellBackColor = vbHighlight Then
        With MSHFlexGrid1
            For iTemp = 1 To .Cols - 1
                .Col = iTemp
                .CellBackColor = vbWindowBackground
                .CellForeColor = vbWindowText
            Next iTemp
        End With
    End If
 
End Sub

BTW, I would just set the FocusRect in the properties window at design time and leave it alone.

I have, I think, a simple question.

Code:

---

Private Sub cmdUpdateSelected_Click()
    Dim iTemp As Integer
    Dim iTemp2 As Integer
    Dim strSQL As String
    Set adoConn = New ADODB.Connection
    adoConn.Open ("Provider=MSDASQL.1;Persist Security Info=False;Data Source=" & DSN_Name & ";")

    With MSHFlexGrid1
        .Col = 1    'or any appropriate column
        For iTemp = 1 To .Rows - 1
            If .CellBackColor = vbHighlight Then
                For iTemp2 = 0 To 2
                    If optQCValue.Item(iTemp2).Value Then
                        strUpdateValue = "UPDATE " & Table_Name & " SET qc = '" & CStr(iTemp2 + 1) & "' WHERE qc IS NULL"
                        adoConn.Execute strUpdateValue
                    End If
                Next iTemp
            End If
        Next iTemp
    End With

    adoConn.Close
    Set adoConn = Nothing
   
    Call TableRefresh

End Sub

---

In the code above, it is my code for the "Update Selected Records" button. When the user clicks on this button, I want it to go through the Flexgrid, find the records that the user "selected," and change them according to the "recordid". In my UPDATE command, how would I specify that not only is it "WHERE qc IS NULL" but also where the "recordid" is the recordid of the selected record?

Reaper

Reaper,

I've never used the DataGrid, so I'm probably not the guy to answer that one.

I don't often use DSN's, but I copied the code and I'll take a look.

Yeah, that fixed it! It finally selects the whole row. But why does it take a while to select the row? It goes through and you can SEE IT selecting each column in the record selected. Is there a way to "speed" it up so that when the user selects the record(s), it automatically selects the WHOLE RECORD??

Also, Is there a way to make it so that the user can go into each individual field in the records and be able to select just a field? Now, when I click on one of the individual fields in the record, it selects the whole row.

Reaper

Mr. McKernan, don't even worry about the Datagrid. If I can fix the Flexgrid, then I will mess with that, later. I am trying to get a working program, soon. My work has not put any time restraints on me, but I am only a temp, and I REALLY NEED A FULL-TIME JOB!! I have been looking every where, and anywhere (state does not matter) and I can not find an entry-level programmer job, anywhere. So I am trying to prove to this company that I am worth their time.

I really appreciate all of the help. I wish I could help you with any programming projects you are working on. I do know other languages (I have not used VB in about 3 years).

Thank you for being so patient with this "Newbie" programmer.

Reaper

Happy to help, and please, the name's John.

To change this to allow the user to select either the whole row or an individual cell... on MSHFlexGrid1_MouseDown event, you could test for which column to user clicked (ie: .Col), only if the user clicks column 0, run the code to select the entire row.

Quote:

---

In my UPDATE command, how would I specify that not only is it "WHERE qc IS NULL" but also where the "recordid" is the recordid of the selected record?

---

Why do you need to specify both criteria? Isn't the recordid field unique?

See if this fixes the time-lag problem

VB Code:

Private Sub MSHFlexGrid1_Click()
    Dim iTemp As Integer
    
    If MSHFlexGrid1.CellBackColor = vbWindowBackground Then
        With MSHFlexGrid1
            .Visible = False
            For iTemp = 1 To .Cols - 1
                .Col = iTemp
                .CellBackColor = vbHighlight
                .CellForeColor = vbHighlightText
            Next iTemp
            .Visible = True
        End With
    ElseIf MSHFlexGrid1.CellBackColor = vbHighlight Then
        With MSHFlexGrid1
            .Visible = False
            For iTemp = 1 To .Cols - 1
                .Col = iTemp
                .CellBackColor = vbWindowBackground
                .CellForeColor = vbWindowText
            Next iTemp
            .Visible = True
        End With
    End If
 
End Sub

I have to say this, "YOU ARE THE VB KING!!!!!!" That fixed it, beautifully.

Now, I am not sure where/how to put the check to see if it is column 0. You said to put it in the MouseDown event. Do you mean like this?

Code:

---

Private Sub MSHFlexGrid1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
    If MSHFlexGrid1.Cols = 0 Then
        Call RowSelection
    End If

End Sub

---

This still selects the whole row when you select on one field.

Reaper

Quote:

---

Originally posted by reaper1973
I have to say this, "YOU ARE THE VB KING!!!!!!" That fixed it, beautifully.

Now, I am not sure where/how to put the check to see if it is column 0. You said to put it in the MouseDown event. Do you mean like this?

Code:

---

Private Sub MSHFlexGrid1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
    If MSHFlexGrid1.Cols = 0 Then
        Call RowSelection
    End If

End Sub

---

This still selects the whole row when you select on one field.


Reaper

---

Well, I don't know about king, but thanx.

change it to this...

VB Code:

Private Sub MSHFlexGrid1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
    If MSHFlexGrid1.Col = 0 Then
        Call RowSelection
    End If
 
End Sub

I have this for the Click and MouseDown event, but it still selects the whole row if I click on one field?

Reaper

Code:

---

Private Sub MSHFlexGrid1_Click()
        Call RowSelection
   
End Sub

Private Sub MSHFlexGrid1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
    If MSHFlexGrid1.Col = 0 Then
        Call RowSelection
    End If

End Sub

---

Thanks for all of the help, and time, John. I hope you have/had a good weekend. I just have a few questions that I need help with.

1) The MouseDown and Click event for the Flexgrid still does not work right. It will select the whole record, no mater where you click.

Code:

---

Private Sub MSHFlexGrid1_Click()
        Call RowSelection
   
End Sub

Private Sub MSHFlexGrid1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
    If MSHFlexGrid1.Col = 0 Then
        Call RowSelection
    End If

End Sub

---

2) I need to figure out how to UPDATE the records selected? (I am just keeping the Where...is null as a precaution, I guess)

Code:

---

Private Sub cmdUpdateSelected_Click()
    Dim iTemp As Integer
    Dim iTemp2 As Integer
    Dim strSQL As String
    Set adoConn = New ADODB.Connection
    adoConn.Open ("Provider=MSDASQL.1;Persist Security Info=False;Data Source=" & DSN_Name & ";")

    With MSHFlexGrid1
        .Col = 1    'or any appropriate column
        For iTemp = 1 To .Rows - 1
            If .CellBackColor = vbHighlight Then
                For iTemp2 = 0 To 2
                    If optQCValue.Item(iTemp2).Value Then
                        strUpdateValue = "UPDATE " & Table_Name & " SET qc = '" & CStr(iTemp2 + 1) & "' WHERE qc IS NULL"
                        adoConn.Execute strUpdateValue
                    End If
                Next iTemp
            End If
        Next iTemp
    End With

    adoConn.Close
    Set adoConn = Nothing
   
    Call TableRefresh

End Sub

---

3) When the user selects rows, by selecting in MSHFlexgrid.Col = 0, I need for the program to select ALL of the records in the range selected. Right now, it individually selects records, it will not select all of the records in the range if the user clicks on the first record in a range and drags the mouse down to highlight more rows.

4) Is there a way to only specify USER DSNs?

5) And, last, but not least, if the user decides to change a few fields in the Flexgrid, and then the user clicks on the "Save Changes" button, how do I make it go through the whole Flexgrid and change whatever the user changed in the database?

Hope you have a good weekend, and thanks for all of your help. I do not know how I could have done this project without you (A MIRACLE, PERHAPS :)

Thanks,
Reaper

Reaper,

#1:
What's happening is when the mouse button is clicked, MouseDown fires. The code in MouseDown checks for the column. Then when the user release the mouse button MouseUp fires and then Click fires. Since there is no testing in the click event, it highlights every row. Try this.

VB Code:

Private Sub MSHFlexGrid1_Click()
    If MSHFlexGrid1.Col = 0 Then
        Call RowSelection
    End If
    
End Sub
 
Private Sub MSHFlexGrid1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
    If MSHFlexGrid1.Col = 0 Then
        Call RowSelection
    End If
 
End Sub

#2:
Personally, I would eliminate the Where...is null if not necessary, but try this..

VB Code:

strUpdateValue = "UPDATE " & Table_Name & " SET qc = '" & CStr(iTemp2 + 1) & "' WHERE (qc IS NULL) AND recordid =" & 'recordid value

#3:
You should be able to user the flexgrids DragOver event to handle row selection as well as the click events...

VB Code:

Private Sub MSHFlexGrid1_DragOver (Source As Control, X As Single, Y As Single, State As Integer)
   Select Case State
      Case vbEnter   
         RowSelection    'NOTE: The Call statement is optional
   End Select
End Sub

...you can test for .Col here also.

#4:
I am sure there is. Unfortunately, DSN's are not my strong suit and I'm not familiar with all the syntax for the provider you are using. I recently read some info about this in ODBC200.chm, a help file on my computer (I don't know what installed it). Search your drive for a similar file. Otherwise, try searching the forum, or msdn for DSN or the Provider name.

#5:
Looping through the grid is, of course necessary (I explained above someplace). To update multiple fields just add the expressions to your sql, as needed to reference the datasource fields...

VB Code:

strUpdateValue = "UPDATE " & Table_Name & " SET qc = '" & CStr(iTemp2 + 1) & "', field1 = " & ***The Value*** & " WHERE (qc IS NULL) AND recordid =" & 'recordid value

Best of luck with the project and with landing the job. Glad to be of help, I'd be interested to know how you make out.

John

Reaper,

I looked over your DSN code and I took the liberty of a few suggestions...

IMPORTANT: in your cmdCancel_Click event, you call the End Statement. This is generally a bad idea. End causes an abrupt termination of your program. It is MUCH better to systematically unload all of your forms, and take care of any cleanup operations in the forms' QueryUnload or Form_Unload events. You can create a public sub to handle this and call the sub in place of the End statement.

In the cmdOK_Click event, you check to see if the user selected anything. A more polished approach would be to (at design-time) set cmdOK.Enabled = False, then in the Click events for cboDSN and cboTables, do...

VB Code:

If (cboDSN.Text <> "(None)") And (cboTables.Text <> "(None)") Then
    cmdOK.Enabled = True
Else
    cmdOK.Enabled = False
End If

... you can then even eliminate the message box, the user won't be able to click cmdOK unless both cbo's have values.

In LoadCboDSN under 'get the DSNs, this may or may not be a little more readable (my personal preference)...

VB Code:

'Check to see if it is a system DSN, if so, do not add to cboDSN
If (sDSN <> Space#(iDSNLen)) Then
    Select Case sDSN
        Case Is Not "MS Access Database", "dBASE Files", "Excel Files", "Visual FoxPro Database", _
            "Visual FoxPro Tables", "dBase Files - Word", "FoxPro Files - Word", _
            "FoodMart 2000", "CROR8V36", "CRSS", "CRXMLV36", "Xtreme Sample Database", _
            "CE8", "TikkisDb", "MQIS")
            cboDSN.AddItem sDSN
    End Select
End If

Again, good luck.

Hello, John, hope your weekend was good. Guess it is back to work, again.

Still not having much luck with this program. Remember the problem I was having with selection, now nothing is being selected, at all. I have been beating my head against the computer because the code looks good. I have been placing MsgBoxed everywhere to find out what is going on, but it is not selecting anything, now.

Here is some of the VB code I am using, just as an example.
[visual basic code]
Private Sub MSHFlexGrid1_Click()
If MSHFlexGrid1.Col = 0 Then
Call RowSelection
End If

End Sub

Private Sub MSHFlexGrid1_DragOver(Source As Control, x As Single, y As Single, State As Integer)
If MSHFlexGrid1.Col = 0 Then
Select Case State
Case vbEnter
Call RowSelection
End Select
End If

End Sub

Private Sub MSHFlexGrid1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
If MSHFlexGrid1.Col = 0 Then
Call RowSelection
End If

End Sub

Private Sub RowSelection()
Dim iTemp As Integer

If MSHFlexGrid1.Col = 0 Then
If MSHFlexGrid1.CellBackColor = vbWindowBackground Then
With MSHFlexGrid1
.Visible = False
For iTemp = 1 To .Cols - 1
.Col = iTemp
.CellBackColor = vbHighlight
.CellForeColor = vbHighlightText
Next iTemp
.Visible = True
End With
ElseIf MSHFlexGrid1.CellBackColor = vbHighlight Then
With MSHFlexGrid1
For iTemp = 1 To .Cols - 1
.Visible = False
.Col = iTemp
.CellBackColor = vbWindowBackground
.CellForeColor = vbWindowText
Next iTemp
.Visible = True
End With
End If
End If

End Sub
[/visual basic code]

I have another question, in the UPDATE statement, how would I specify by a particular column. For example, in my "UpdateSelected" button, I want it to look through the "selected" records and update it by the "recordid" in that record. I am still confused how I would tell the program which value? (I am not sure if I am asking this question, correctly.
How would I tell the program that I want the particular "recordid" of the row selected? Use a variable?

Also, I am not sure if I have asked this question (probably not) but in my "ChangeColumnWidth" function, it does everything perfect but one thing. In a column that is the same size as the grid, or longer, it deletes the grid lines from that column, why? Is there a way to fix this?

Code:

---

Public Sub ChangeColumnWidth()
    'Sets column width to width of longest visible text in column
    Dim Flex As MSHFlexGrid
    Dim lngMaxLength As Long
    Dim intCounter As Integer
    Dim lngLength As Long
    Dim lngWidth As Long
    Dim lngMinWidth As Long
    Dim i As Integer
    Dim Parent As Form
   
    Set Flex = MSHFlexGrid1
    Set Parent = Flex.Parent

    Flex.Redraw = False
    Flex.FocusRect = flexFocusNone
   
    For i = 1 To Flex.Cols - 1
        Flex.Row = 0
        Flex.Col = i
        If (i <> Flex.Cols) Then
                intCounter = Flex.TopRow
                'get minsize of column from column header
                With Parent
                    .Font = Flex.FontFixed
                    .FontSize = Flex.CellFontSize
                    .FontBold = Flex.CellFontBold
                    .FontItalic = Flex.CellFontItalic
                End With
               
                lngMinWidth = Parent.TextWidth(Flex.TextMatrix(0, i))
                lngMaxLength = 0
                intCounter = 0
               
            While intCounter < Flex.Rows - 1
                Flex.Row = intCounter
                With Parent
                    .Font = Flex.CellFontName
                    .FontSize = Flex.CellFontSize
                    .FontBold = Flex.CellFontBold
                    .FontItalic = Flex.CellFontItalic
                End With
               
                lngLength = Parent.TextWidth(Flex.TextMatrix(intCounter, i))
               
                If lngMaxLength < lngLength Then
                    lngMaxLength = lngLength
                End If

                intCounter = intCounter + 1
            Wend

            lngWidth = lngMaxLength
            If lngWidth < lngMinWidth Then
                lngWidth = lngMinWidth
            End If
           
            Flex.ColWidth(i) = lngWidth + 100
           
        End If
    Next i
   
    Flex.FocusRect = flexFocusLight
    Flex.Redraw = True
   
End Sub

---

And, just as a side-note, I think the better I do on this program (and any others, if they come along) the more of a chance that I have on getting a full-time job. If not, then I will be back at home, unemployed, again. I thought getting a Bachelor's Degree would solve this? Also, I have been telling employers that I will move ANYWHERE and I thought that would help, but, so far, it does not. Go figure, huh?

Thanks,
Reaper

BTW, so as to not confuse things. I am going to attach a copy of the code that I am using so that it is a little easier to follow progress. :)

I found one more thing. I have been messing with it, trying to get it to work, but it keeps kicking me out of the program. When the user is using the arrows keys to move around the Flexgrid, I want it so that the user can hit the 'Enter' key and then be able to edit the cell that they are on.

Reaper

Hey, John, one more thing. I just found out something that will, hopefully, make this easier. In order for the user to edit data within the grid, they want a checkbox, only. They don't want the user to be able to edit anything within the grid, unless the checkbox is checked. Hopefully this should make this easier. Hopefully. I am looking into fixing it, now.

Reaper

Quote:

---

Originally posted by reaper1973
Hey, John, one more thing. I just found out something that will, hopefully, make this easier. In order for the user to edit data within the grid, they want a checkbox, only. They don't want the user to be able to edit anything within the grid, unless the checkbox is checked. Hopefully this should make this easier. Hopefully. I am looking into fixing it, now.

Reaper

---

Yeah, that definitely simplifies things a little. I've found the easiest way to do this is to use the fixed column as the checkbox (add another fixed column if necessary). You can use an image to represent the checkmark or use one of the characters from Wingdings font, etc to represent it.

When the user clicks the cell, test to see if the 'checkmark' is present, if so you can turn it off to represent a deselected row, if its not present, you can place it there to represent a selected row. You could also make use of this in decided whether to allow the user to edit the row or not, and in updating the database.

I've downloaded your code, and will take a look a lttle later this evening.

John

(BTW the formatting codes are vbcode and /vbcode enclosed in square brackets)

Cool, thanks. I need help, bad. Every time I think I fixed it, I screw it up even more. I have been trying to take one problem at a time, but I can't even fix one of them. It is frustrating. This seems like it should have been easy, but it is taking me awhile. I work on it all day and get no where. You work on it for a few minutes and fix it. I wish I was at that level.

I will be here until 7 pm. Whenever you can get around to it, I would appreciate it.

Reaper

Hey, John. Do you of anyone i can talk to during the day to help, too? I really want to finish this, soon. I can use ICQ, e-mail, etc. or anything else to help me fix this. I have been using Newsgroups, etc. but alot of times, no one will help. If so, you can send me a Private Message. I will be in to work tomorrow at 11 am. Also, I am going to try and see if I can get on this site, tonite. And look for you.

Later,
Reaper

Reaper,

You can try this

Quote:

---

Thanks to geoff_xrx

VB Code:

Private Sub MSFlexGrid1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
    If Button = 2 Then
        MSFlexGrid1.Row = MSFlexGrid1.MouseRow
    End If
End Sub

---

Returns the mouse position in row, col coordinates

I have been testing the MouseMove theory. I have been using the x- and y- coordinates. The problem I am coming up with is if the user selects a range of cells that go outside of the screen, the x- and y- coordinates are screwed up. It uses the coordinates according to the top left of the screen. So if it goes outside, the coordinates do not register correctly.

Also, I have gotten a few more things to work. I will send you the updated code and a few questions left. (Mainly the multiple selection, but there are a few others.)

Thanks,
Reaper

Did you try this?

VB Code:

Private Sub MSFlexGrid1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
    If Button = 2 Then
        MSFlexGrid1.Row = MSFlexGrid1.MouseRow
    End If
End Sub

I finally got some of my program to work! It must be some kind of miracle! Here is the latest code.

Questions:
1) How do I get the multiple selection to work? (Still could not get that to work)
2) How do I get the "Save Changes" button to work? (Supposed to update the WHOLE page)
3) I got the "Checkbox" to work. But, if the user types a number/letter, it changes the cell. I do not want the user to be able to change anything unless the Checkbox is selected. Also, the 'Escape' key and the 'Enter' key work wierd. The 'Escape' key allow the user to change data and the 'Enter' exits the program. I could not get this to stop.

Thanks,
Reaper

I downloaded it, I'll take a look and get back to you, but it might not be till later this evening.

I made a couple of changes in cmdSaveChanges_Click. Try this...

VB Code:

With MSHFlexGrid1
    .Row = 0
    .Col = .Cols - 1
    For iTemp = 1 To .Rows - 1
            .Row = iTemp
        For iTemp2 = 0 To 2
            If optQCValue(iTemp2).Value Then
                strUpdateValue = "UPDATE " & Table_Name & " SET qc = '" & CStr(iTemp2 + 1) & "' WHERE qc IS NULL AND recordid = '" & .Text & "'"
                adoConn.Execute strUpdateValue
            End If
        Next iTemp2
    Next iTemp
End With

everthing else in the procedure should stay the same.

I haven't gone through all of the Checkbox problem yet, but this did jump out at me...

Change MSHFlexGrid1_DblClick() to:

VB Code:

Private Sub MSHFlexGrid1_DblClick()
    If chkEdit.Value = vbChecked Then
        GridEdit MSHFlexGrid1, txtGridEdit, 32   'simulate space
    End If
    
End Sub

Hint: for vb constants etc, you can use intellisense (that's the scroll box that pops up in the code window and gives you control properties, etc). Just use 'Ctrl+space' anywhere in the code window and intellisense will popup.

I didn't see any other problems with the Checkbox, but I'm going to be tied up for the next couple of hours.

You will have to put this code anywhere you call GridEdit:

VB Code:

If chkEdit.Value = vbChecked Then
    GridEdit MSHFlexGrid1, txtGridEdit, 32   'simulate space
End If

Check out these last few fixes and let me know what it still solves.

In the recent code that I sent, I already got the chkEdit.Value = vcChecked... in the DblClick. That worked.

In the "Save Changes" button, I need it to go through every record and save it if it has been updated. This is the problem I am having, how do you check to see if it has been updated or not. Or, would it just be easier for the "Save Changes" button to just save the WHOLE Flexgrid (seems like this way would take along time)?

Reaper

Actually, I gave you the wrong file. This file is more up-to-date than the previous file.

Sorry bout that,
Reaper

OK, the additional info you sent helped alot.

Your code is fine. The problem lies in the size of the grid. As you recall, we had to make the grid invisible when selecting the row to prevent seeing the cells highlight. What's happening is the user is moving the mouse faster than the grid can keep up with everything. About the best thing I can think of off hand is abandoning the drag method of selecting and use a Shift-Click method instead.

To select rows 3 to 7, the user clicks on row 3 and shift-clicks on row 7. Programmatically you select rows 3 to 7. You could extend this to ctrl-click to deselect a row. You will need a couple of variables to keep track of what rows were clicked and shift-clicked, and just loop from one to the other highlighting them.

No, I would not suggest saving unchanged records. Two options come to mind:

1. Add a hidden column to the grid (.ColWidth = 0), when a cell is editted, place a value (ie: 1) in the hidden cell. When saving changes, test for a value in the hidden cell, if none - skip the record.

2. Before saving changes, create an array and loop through the grid rows adding the row number of any highlighted rows to the array. Then loop through the array to determine which rows to save.

(I would opt for the first.)

You commented on the behaivor of the Enter and Esc keys. Enter is exiting the program because on frmMain, you have the 'Exit' button set as default. When a button is set as the default, its click event is fired when the user presses Enter. If you set the 'Exit' button .Cancel property as True, will solve this. Then when the user presses Esc, the 'Exit' button will fire.