Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


MsFlexGrid Scrolling feature

Posted on 2004-08-18
Medium Priority
Last Modified: 2013-12-25
I have created a MSFlexGrid, that reads a database and displays the information. This is a 4 part question

1.  The MSFlexGrid displays 8 rows of a possible 23 rows and  I cannot scroll down to see the rest of the rows( 9-23). Can you tell me how to achieve this.

2.   I am displaying 5 columns and possibly 23 rows. I want to be able to protect all the data of 4 of the  5 columns depending on a global variable( allow the user to change data from only 1 column)

3.  If and when the user makes any changes, I want to save the MsFlexGrid and on the next form  I would like to display the MSFlexGrid from the saved grid. However this time the entire MSFlexGrid must be protected.

4.  When applying the changes to the database, is there a way to just get only the changed rows, or should I update the database, using the entire MSFlexGrid
Question by:rbaliga
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
  • 7
  • 4

Expert Comment

ID: 11836278
1. set the ScrollBars property of the flexgrid to 2

2. On the FlexGrid Click Event

    If FlexGrid.MouseCol = 1 And FlexGrid.MouseRow > 0 And globalAllow = True Then
        'your code that allows the user to change data for Col 1
    End If

3. Do you mean you make another copy of the previous FlexGrid & place it on the 2nd Form? If this is what you want then:

   On Form2's Load Event:

    Dim i As Integer, c As Integer
    With Form1.FlexGrid
        Form2.FlexGrid.Rows = .Rows        'this will setup the number of rows
        Form2.FlexGrid.Cols = .Cols            'this will setup the number of columns
        For i = 0 To .Rows -1
            For c = 0 To .Cols -1
                Form2.FlexGrid.TextMatrix(i, c) = .TextMatrix(i, c)
            Next c
        Next i
    End With

   With regards to "protecting the entire FlexGrid". you don't have to do anything (see P.S.)

4. If the FlexGrid is bound to your recordset or data. I believe it will update automatically. Although I'm not a 100% sure on this.

P.S. MSFlexGrid does not allow you to edit a cell. maybe you should look at DataGrid instead of MSFlexGrid

hope this helps. good luck!


Author Comment

ID: 11836586
My orginal thought was to use a DataGrid, but since I do not want to update the database every time the user makes any changes within a cell I went with the Flexgrid. Form1's grid is where the user can make any changes. Form2 is where I display the grid from form1 and have to write the code(for MSFlevGrid)  to update the database when the user clicks "Finish"  At this point the user also has the option to click "Cancel" at which time I take them back to Form1 without updating the database.

From what I have read, DataGrid  automatically updates the Database when the user makes any changes within a cell and tabs over to the next cell but,  FlexGrid you have to do it manually.

 FYI.(your P.S)   I have been able to make changes to individual cells using MSFlexGrid

I will try the code you suggested and let you know tomorrow how it is working.
Thanks for your help.

Expert Comment

ID: 11842298
Re: FYI.(your P.S)   I have been able to make changes to individual cells using MSFlexGrid

I have done the same thing. but you have to write additional code for this to happen. Unlike the DataGrid where the editing of a cell is incorporated to the control, MSFlexGrid does not have this functionality built in.

Let me know how it went.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 11842797
Ques 1. your answer works like a charm.
Ques 2.  Not been succesful. But still trying. (not important at this stage)
Ques 3,4:  had success displaying the grid on form2 and also when coming back from form2 to form1

major problem:  saving the changes back to the grid on form1 when user clicks "Continue"
Used the following code to allow changes to cells ( created a txtdataEntry to hold the values that the user has entered)

Private Sub MSFlexGrid1_Click()
    txtDataEntry.Text = MSFlexGrid1.Text 'Retrieve contents from grid
    MSFlexGrid1.col = MSFlexGrid1.MouseCol 'Lines you up with where was clicked
    MSFlexGrid1.row = MSFlexGrid1.MouseRow
    txtDataEntry.Left = MSFlexGrid1.Left + MSFlexGrid1.CellLeft 'Lines up the textbox
    txtDataEntry.Visible = True
    txtDataEntry.SetFocus 'Allow the user to type in the textbox
End Sub

Private Sub txtDataEntry_Change()
    MSFlexGrid1.Text = txtDataEntry.Text 'Store the contents to the grid
End Sub

Private Sub txtDataEntry_KeyPress(KeyAscii As Integer)
    'Check for Enter/Return, and don't go off the bottom of the grid!
    If (KeyAscii = 13) And (MSFlexGrid1.row + 1 < MSFlexGrid1.Rows) Then
        MSFlexGrid1.Text = txtDataEntry.Text
        MSFlexGrid1.row = MSFlexGrid1.row + 1
    End If
End Sub

DataGrid is not an option, because I do not want to update the database in form1. Looking at serialization. But don't fully understand how to set it up.

Thanks for your feedback.

Expert Comment

ID: 11843045
side note:

i wrote a function where you can allow cell editing in MSFlexGrid. you pass it the control (usually a textbox, but sometimes i use date picker or a combo box), the flexgrid, the row, the column & an optional parameter for setting the alignment of the control (usually useful when the data on the flex grid is a text (left aligned) or numeric (right aligned).

Re: major problem

what do you mean "saving the changes back to grid1 on form1"? isn't this the form where you enter data?

Author Comment

ID: 11843472
Yes it is, but when I go to form2 and display the grid from Form1, it does not show the changed values, it reverts to the original values.

I used the code you had suggested(ans:3)  on the Form Load event of form2.
I am sure I have to save the grid in Form1, before passing control to form2 and that my friend is my "major problem"


Expert Comment

ID: 11844013
Ok. question.
Do you unload form1 when you display form2 or do you just hide form1?

I'm baffled why it is reverting back to its original values. you don't have to "save" the grid in form1. when you change a value in a cell, it will stay that way until you overwrite that cell with another value or until that object is destroyed. I'm guessing that it is re-populating the the grid with your initial records (from a recordset?) before it loads form2. so when form2 reads form1's flexgrid, it reads your un-updated values.

you can easily check if the flexgrid in form1 has changed back to its original value. pause it when executing the code (ans 3). form1 should be hidden NOT unloaded. now if you edited let's say, column 1; row 1 of form1's flexgrid, go to the Immediate Window and type: ?form1.flexgrid.textmatrix(1,1). this will display the current value of the cell you edited previously. if it shows the original value then we are sure that the flexgrid is being re-populated with the original records.

Accepted Solution

PePi earned 2000 total points
ID: 11844273
ok... i think i know what you are saying.

you go and edit some cells in form1's flexgrid. you go to form2. you unload form1. you go back to form1 without saving the edited values. form2 is unloaded. form1 is reloaded. form1's flexgrid has original values and edited values are lost.

if this is the case, the solution is very simple. DO NOT unload form1 prior to loading form2. hide it (form1.hide). this will ensure you that the edited cells STILL have edited values. you won't need to reload the data into your flexgrid.

let me know if this is the scenario you are facing.

Expert Comment

ID: 11844289
oh, i forgot. to show form1 again (returning from form2) just go: from1.show. then you can unload form2

Author Comment

ID: 11844474

You are a genius. Thanks for working with me on this problem. This is exactly the way I wanted my form to work.

You truly deserved the 500 points

Expert Comment

ID: 11844609
anytime buddy. enjoy!

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

609 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