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

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!!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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


YeavisAuthor Commented:
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...
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.

YeavisAuthor Commented:
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
YeavisAuthor Commented:
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?
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.

YeavisAuthor Commented:
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.
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 & "'"


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
YeavisAuthor Commented:
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.
YeavisAuthor Commented:
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..
Set the Selection Color to White or whatever the row backcolor is
But it will not show the user which row he has selected!!!!
YeavisAuthor Commented:
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.
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.