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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline


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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
word0 challenge 4 53
firstChar challenge 13 84
PHP question(s) about order of output 9 42
How can i compile this github project?? 2 41
A short article about a problem I had getting the GPS LocationListener working.
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now