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

Posted on 2004-09-29
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...
Independent Software Vendors: We Want Your Opinion

We value your feedback.

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

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

751 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