Solved

Looking for Easy way to search in a DataGrid or Grid?

Posted on 2003-11-14
14
637 Views
Last Modified: 2010-05-01
I am looking to implement a search and goto in a DataGrid. If any of you have used Lotus Notes 5, when you select a row heading and start typing it throws up a search box that automatically searches that column and goes to the row that matches.
I am considering implementing something similar in my VB app.

flow:
user clicks on a column header in the grid.
user starts typing
search dialog comes up and the char the user typed is entered along with all other char they type.
upon hiting return the column that was highilghted in the grid is searched and the 1st match is gone to.
If no match found then nothing happens, statusbar displays message.

I am looking for an easy way to search the specific column in the grid for a value??
I am currently using the DataGrid (MSDATGRD.OCX)
0
Comment
Question by:diarmaid
  • 7
  • 4
  • 2
  • +1
14 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 150 total points
ID: 9747930
Hi diarmaid,

Something like this simplified example:

In main form with datagrid:

Public intCurCol

Private Sub DataGrid1_HeadClick(ByVal ColIndex As Integer)
    intCurCol = ColIndex
End Sub

Private Sub DataGrid1_KeyPress(KeyAscii As Integer)
    Dialog.Show vbModal,Me
    Dialog.Text1.SetFocus
    Dialog.Text1.Text = Chr(KeyAscii)
    Dialog.Text1.SelStart = 2
    KeyCode = 0
End Sub

This just sets the current column and opens a dialog form with the first keypress showing.

For the dialog form:


Option Explicit

Private Sub OKButton_Click()
    With Form1.Adodc1.Recordset
        .Find .Fields(Form1.intCurCol).Name & " Like '" & Text1.Text & "%'"
        If .EOF Then
            MsgBox "Not Found"
        End If
    End With
End Sub

Which just finds the first partial match of the entered text.

Tim Cottee MCSD, MCDBA, CPIM
Brainbench MVP for Visual Basic
http://www.brainbench.com
0
 
LVL 2

Author Comment

by:diarmaid
ID: 9748066
Thanks Tim. Not really what I was looking for though. This would search adodc whereas I want to search the contents of the grid itself.
I already have textboxes for filtering the grid via the adodc so the user ends up with x amount of records.

What I then want to do is allow a search of the contents/data in the grid without having to use the adodc at all, kinda just a static search of the data. I havent come across a way to do this with the datagrid or the grid and am unsure if it can easily be done.

Filtering via the adodc isnt really an option as this will probably be removed and replaced by code at a later stage. The grid is only being used to display data, and select single record at a time for deletion. there will be absolutely no edit capabilities in the grid, so I dont need it to be bound.

0
 
LVL 2

Author Comment

by:diarmaid
ID: 9748093
Pity there isnt a row = datagrid.find("text", column) function or method
or row = datagrid.match("text", column)
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 9748135
If you want to run this unbound then perhaps a flexgrid is a better solution than the datagrid. The problem with the bound grid is that you have to search the underlying recordset because the grid itself doesn't contain all the values all the time, they are loaded into the grid when they need to be displayed. With the flexgrid, you have a .TextMatrix(Row,Col) property which you can easily iterate through to find a match:

For intRow = .FixedRows To .Rows - 1
  If .TextMatrix(intRow,CurrentColumn) Like txtToMatch & "*" Then
     'Display it and finish
  End If
Next

Or something along these lines.
0
 
LVL 2

Author Comment

by:diarmaid
ID: 9748417
Im really at investigation - prototype stage.
Sounds like a possible option. Data in the grid will be comming from a SQL 6.5 DB.

Flexgrid has to use the Ordinary Data control. I was pushed toward the adodc and DataGrid because of OLEDB supposed to be faster.
 My connection to the db is through ODBC DSN.
I will investigate + get back.

Insight. Basically my app is for business end users to maintain their own lookup/info tables, that all their other tools rely on. The idea is that when new records need to be added or existing records need to be changed in the tables they will be able to do it themselves. This will take away the need for the DBA or Systems staff to do DB updates.
Since they will be working on production tables that all their other tools/utilities rely on, they are only getting very limited edit capabilities ie. grid only being used for display. If they wish to edit a record they must select it and edit it in textboxes.

If you want to give some insight etc. please do, I can throw more points at this as we are now getting a bit off the original Q. tks.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 9748460
If you use the MSHFlexgrid it will accept an ADO Data control or ADO recordset through code. It has a few extra features but is otherwise compatible with the flexgrid. If you are not interested in editing then this is a perfectly good solution. I can demonstrate with a bit of code shortly:
0
 
LVL 2

Author Comment

by:diarmaid
ID: 9748461
Oh, the reasoning behind applying the type of search specified is that they extensively use notes DBs as well to lookup documents, rates, etc. etc. so they are well used to clicking a column and just typing away to search, thus there would be no learning curve.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Author Comment

by:diarmaid
ID: 9748528
Excellent, I was trying the MSFlexgrid.ocx.
I thought I tried the FlexGrid before, one of the columns displays rates but sometimes they are not correctly displayed. eg. 0.0865 is being displayed as 9999994E-2 whereas 0.1215 displays correctly.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 9748558
For the search dialog form:

Private Sub OKButton_Click()
    Dim i As Integer
    With Form1.MSHFlexGrid1
        For i = .FixedRows To .Rows - 1
            If .TextMatrix(i, .Col) Like Text1.Text & "*" Then
                .Row = i
                .RowSel = i
                .Col = 0
                .ColSel = .Cols - 1
                Exit For
            End If
        Next
    End With
    Unload Me
End Sub

For the main form:

Private Sub Form_Load()
    Adodc1.Refresh
    Set MSHFlexGrid1.DataSource = Adodc1.Recordset
End Sub

Private Sub MSHFlexGrid1_KeyPress(KeyAscii As Integer)
    Load Dialog
    Dialog.Text1.Text = Chr(KeyAscii)
    Dialog.Text1.SelStart = 2
    Dialog.Show vbModal, Me
End Sub

This mirrors the earlier example but using the MSHFlexgrid and highlights the matching row
0
 
LVL 2

Author Comment

by:diarmaid
ID: 9749221
I dont think im going to use the MSHFlexGrid, I still apears to have trouble with small decimals as specified above. From what Ive tried so far it seems a bit ackward/combersome to use.

I will have to experiment a little more but I will probably take this functionality out as i havent made specific design commitments yet.
0
 
LVL 9

Assisted Solution

by:Valliappan AN
Valliappan AN earned 20 total points
ID: 9751570
You might have a look at:
http://www.componentone.com/products.aspx?ProductCode=1&ProductID=18&cmd=1

http://www.infragistics.com/products/activex/ultragrid.asp

These 3rd party tools come with a lot of features including search/sort, if you are serious about it.

Hope it helps.
0
 
LVL 3

Assisted Solution

by:Ficus
Ficus earned 30 total points
ID: 9751641
I would use the HFG.  Unless your grid is holding a huge ammount of data the you can perform many formatting functions on bound or unbound grid with little noticible pause.  You can also use the .TextMatrix loop shown to you by TimCottee (which is the best wat to search the grid for a srtring).

If for example you don't like the format of your numbers (this is effected by the field type of you data source) you can perform a loop at the time loading the grids contents to format certain cols or rows.

   With Form1.MSHFlexGrid1
        For i = .FixedRows To .Rows - 1
             .TextMatrix(i, .Col) = format$(.TextMatrix(i, .Col),"###,###.##")
             'you can even add evaluations to format cells a certain color etc...
            End If
        Next
    End With

Turn off the .Redraw Proprerty of the grid to speed things up!

This is a feasible method for reasonable amounts of data.

You should give the points to TimCottee.

Regards,
Tom
0
 
LVL 2

Author Comment

by:diarmaid
ID: 9786803
I am using the datagrid along with textboxes and comboboxes to apply a filter using the adodc. I am not currently going to implement the notes type search on the grid. I may at a later stage by writing a class/function that I can hand the datagrid into that will do it for me.

I have implemented a sort function using the adodc when the user clicks on a column header in the grid. Currently this only sorts assending and there is no icon in the grid column header, im not sure if this will make it further than the prototype either.

I have a licensed TDBGrid 7 control and was going to use it, however I made a consious decision not to use it as the tool im developing is lightweight and the grid is only for display purposes.

However if the client wants enhanced functionality after the prototype I will be using it, but time constraints would need to be considered as well.

Again, Thanks for all your time.
0
 
LVL 3

Expert Comment

by:Ficus
ID: 9797902
I usually sort on the grid, not the RS like you are doing.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
VB6 convert MSHFlexgrid1 cells 7 52
z = x + y – 1 6 59
VBA saving file message display 5 47
SSRS expression Issue finding a string 10 51
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 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

13 Experts available now in Live!

Get 1:1 Help Now