DataGrid or Flexgrid for Multiple Selections?

[reaper1973]

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

[JPicasso]

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.

[reaper1973]

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

[John McKernan]

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:

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

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

Hope that's a leg-up

[reaper1973]

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

[John McKernan]

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

[John McKernan]

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

[reaper1973]

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

Thanks for the help,
Reaper

[reaper1973]

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

[John McKernan]

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.

[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?

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

[John McKernan]

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

[John McKernan]

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:

txtGridEdit.Text = ""

this ensures the textbox loads at form load.

[reaper1973]

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

[reaper1973]

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:

    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

[CliffPhilip]

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.

[John McKernan]

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.

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.

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

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

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

[reaper1973]

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

[reaper1973]

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

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

[John McKernan]

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

Thanks,
Reaper

My pleasure

[reaper1973]

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

[reaper1973]

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:

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

[reaper1973]

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

[John McKernan]

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.

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?

[John McKernan]

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

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.

[reaper1973]

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.

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

[John McKernan]

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.

[John McKernan]

I fixed it.

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.

[reaper1973]

I have, I think, a simple question.

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

[John McKernan]

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.

[reaper1973]

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

[reaper1973]

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

[John McKernan]

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.

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?

[John McKernan]

See if this fixes the time-lag problem

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

[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?

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

[John McKernan]

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?

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

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

[reaper1973]

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

Reaper

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

[reaper1973]

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.

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)

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

[John McKernan]

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.

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

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

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

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

[John McKernan]

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

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

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