Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How To Pull Data From MSFLEXGRID Row and display in text box for updating

Posted on 2004-09-29
Medium Priority
Last Modified: 2008-01-09
I have an MSFLEXGRID that displays data from a recordset. The flexgrid can have many rows and I need to be able to pull the data from each row independently.
When the user clicks on a new row I need the data to change and be displayed in the following text boxes and data combos (txtItemDescription, txtLocation, txtMOBOID, dcbOwner, dcbStatus, txtItemRemovedDate). I will then update the changed data (if the user modifies anything) with cmdUpdate and rewrite the data to the database. At that point I want the MSFLEXGRID to refresh and display the newly added item (basically requery with the same SQL).

Here's the code I have that probably needs revised in order for this to work.

Private Sub MSFlexGrid2_Click()
    Me.txtMoboID.Enabled = False
    mode = adModeReadWrite
    loc = adUseClient
       ConnectToDB mode, loc, dbConString
            Set rs = New ADODB.Recordset
            rs.Open "select * from tblDataChild", DbCon, adOpenForwardOnly, adLockOptimistic
            Me.txtItemDescription.Text = rs.Fields("ItemDescription")
            Me.txtLocation.Text = rs.Fields("ItemLocation")
            Me.txtMoboID.Text = rs.Fields("ItemMOBOID")
            Me.dcbOwner.Text = rs.Fields("ItemOwner")
            Me.dcbStatus.Text = rs.Fields("ItemStatus")
            Me.txtItemRemovedDate.Text = rs.Fields("ItemRemovedDate")
End Sub

Any Help Would Appreciated!!
Question by:Yeavis
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
  • 6
LVL 19

Expert Comment

ID: 12186930
I think there's no need to update the database on every row update of the FlexGrid, What I always do is:

On Form_Load I populate the Grid with data from a Recordset
On click of the FlexGrid I push data in the appropriate Combo & Text Boxes, User changes the data and then on Save I update the recordset and populate the flexGrid again from the recordset as I did on Form_Load.

The Click Event of FlexGrid will have the following code

CurrRow=MSFlexGrid2.Row 'CurrRow is a Form Level variable
Me.txtItemDescription.Text = MSFlexGrid2.Text
Me.txtLocation.Text = MSFlexGrid2.Text
Me.txtMoboID.Text = MSFlexGrid2.Text
Me.dcbOwner.Text = MSFlexGrid2.Text
Me.dcbStatus.Text = MSFlexGrid2.Text
Me.txtItemRemovedDate.Text = MSFlexGrid2.Text

Now the User will change the Data and say will hit Save then on Save Click
There Update the Recordset and Call a Function to Re-Populate the flexGrid

In case you are not using a connected environment, i.e. you do not have a live recordset then on Save Click
Use the CurrRow value to locate the FlexGrid Row and update the values manually in the FlexGrid eg.
MSFlexGrid2.Row =CurrRow
..... So On

Then use Update Query to save changes back to the Database...


Expert Comment

by:Abhigyan Srivastava
ID: 12187472
You can also rebind the flexigrid to your recordset after updating the row. That will refresh the grid data.



Author Comment

ID: 12188833
Okay. This worked, however I hesitated to mention that there are hidden columns in my MSFLEXGRID. I have a basic ID field that is the autonumber for the database, and the Status and ItemRemovedDate are also hidden until you click on an inventory item from the MSFLEXGRID. How can I get the data from the hidden columns and populate the textboxes without having to make the columns visible? Thanks in advance...
Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

LVL 19

Expert Comment

ID: 12188847
Doesn't matter if the columns are visible or not you can still access them using the Column Number

However What I usually do is set the width of such hidden columns to 0 rather than making it invisible.


Author Comment

ID: 12188875
Also, the data for MSFLEXGRID2 is pulled from a query off of MSFLEXGRID1. When the user double clicks a category from MSFLEXGRID1, I want the DESCRIPTION to be included in the SQL query to get all items of the same description and populate MSFLEXGRID2 based on that. The program I am making is an inventory program, so there needs to be a parent/child type relationship between the MSFLEXGRID1 and MSFLEXGRID2. Can someone look at my code and see if I'm doing this correctly?? Here's the code:

Private Sub MSFlexGrid1_dblClick()
       mode = adModeReadWrite
       loc = adUseClient
       ConnectToDB mode, loc, dbConString
            Set rs = New ADODB.Recordset
            rs.Open "Select tblDataRoot.Description,tblDataChild.* From tblDataRoot,tblDataChild Where tblDataRoot.Description=tblDataChild.ItemDescription", DbCon, adOpenForwardOnly, adLockOptimistic
            SSTab1.Tab = 1
            MSFlexGrid2.FormatString = mystring
            MSFlexGrid2.ColWidth(0) = 0
            MSFlexGrid2.ColWidth(1) = 775
            MSFlexGrid2.ColWidth(2) = 3040
            MSFlexGrid2.ColWidth(3) = 2175
            MSFlexGrid2.ColWidth(4) = 1000
            MSFlexGrid2.ColWidth(5) = 0
            MSFlexGrid2.ColWidth(6) = 0
            MSFlexGrid2.ColWidth(7) = 0
            MSFlexGrid2.Rows = 1
            If rs.RecordCount > 0 Then
                If Not rs.EOF Then
                    Do While Not rs.EOF
                        MSFlexGrid2.Rows = MSFlexGrid2.Rows + 1
                        MSFlexGrid2.TextMatrix(MSFlexGrid2.Rows - 1, 0) = rs.Fields(0)
                        MSFlexGrid2.TextMatrix(MSFlexGrid2.Rows - 1, 1) = rs.Fields(3)
                        MSFlexGrid2.TextMatrix(MSFlexGrid2.Rows - 1, 2) = rs.Fields(2)
                        MSFlexGrid2.TextMatrix(MSFlexGrid2.Rows - 1, 3) = rs.Fields(4)
                        MSFlexGrid2.TextMatrix(MSFlexGrid2.Rows - 1, 4) = rs.Fields(5)
                End If
            End If
End Sub

Author Comment

ID: 12188881
And when the user updates info from the textboxes and I go to save it using CMDSAVE, what is the code I use to requery the database and refresh the data on MSFLEXGRID2?
LVL 19

Expert Comment

ID: 12188913
Well your code looks fine

As for the CMDSAVE if you need to update only one table i.e. tblDataChild that of the MSFlexGrid2 then you can use a command object to update the data base (Or Use a simple Update Query on Connection object)
And Reflect the changes manually in the FlexGrid2 i.e

MSFlexGrid2.TextMatrix(StoredRow, 0)=textBox1.Text
and so on...
(You need to store the Row Id being updated though i.e. the variable StoredRow)

This will work since anyway on your MSFlexGrid1_dblClick you are querying the database.


Author Comment

ID: 12188941
The problem I was having with the code is it's not pulling the right data. Say I had a category name KEYBOARD and I chose it from MSFLEXGRID1. Instead of pulling data with the description KEYBOARD it is pulling all data from the database. I tried to use an JOIN statement in my SQL query but didn't have any luck because I needed more data to be pulled for the second MSFLEXGRID. Could you please re-evaluate my SQL and see where I went wrong? Granted it does work now, but not correctly.
LVL 19

Accepted Solution

arif_eqbal earned 2000 total points
ID: 12189032
So you have some problem with this Query

 "Select tblDataRoot.Description,tblDataChild.* From tblDataRoot,tblDataChild Where tblDataRoot.Description=tblDataChild.ItemDescription"

I didn't checked the query properly, actually It should contain one more Where Clause i.e. Where tblDataRoot.Description= FlexGrid1.text

When the User double Clicks on a row in FlexGrid1 take the Description value from that row say
FlexGrid1.Col=2 'Or whatever Column the Description is in the first FlexGrid

then your Query will be

"Select tblDataRoot.Description,tblDataChild.* From tblDataRoot,tblDataChild Where tblDataRoot.Description=tblDataChild.ItemDescription AND tblDataRoot.Description='" & Desc & "'"


Author Comment

ID: 12189087
Awesome!! Thank you so much for your help it worked like a charm!! Is there anyway that I can make the description on the MSFLEXGRID1 not be highlighted? When I double click on one of the categories it pulls data and then highlights the description on MSFLEXGRID1. If not it's not a big deal, just looks wierd I guess.

Author Comment

ID: 12189133
One last question related to this before I award the points...  how can I replace NULL or Empty values in an MSFLEXGRID with a blank string such as "  "? There maybe items added to this inventory program that don't have all the same information, or it might not have a txtMOBOID for example. How could I replaced that data on the MSFLEXGRID so it displays an empty cell instead of erroring out? Thanks in advance..
LVL 19

Expert Comment

ID: 12189146
Set the Selection Color to White or whatever the row backcolor is
But it will not show the user which row he has selected!!!!

Author Comment

ID: 12189180
Okay you have a point.. how would I do this exactly. What is the code to change the row background color. I want it to highlight the entire row instead of just the description. Also, I am upping the point value of this question. You have helped me get this working and this is something I have really been struggling with! You are awesome man.. and I appreciate your help very much.
LVL 19

Expert Comment

ID: 12197340
Well thanks, anyway
and as for setting the selected row i.e. highlighting the entire row
Right Click on the FlexGrid and open the Property Pages
On the General Tab set
a) FocusRect -> 0-None
b)SelectionMode -> 1-ByRow

that's it

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

670 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