Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Do some query with the table (displayed by FlexGrid and Data control) 1

Status
Not open for further replies.

chuanwang

Technical User
Jul 24, 2002
55
SE
I have one Access database. I am going to use Visual Basic to display the information in Access database. I can use FlexGrid and Data control to display the table stored in Access. Below is illustration of my table in Access or table already in VB

id contents Field1 Field2 Field3

1 C1 A1 B1 D1
2 C2 A2 B2 D2
3 C3 A1 B2 D1
4 C4 A3 B1 D3
5 C5 A2 B3 D2
6 C6 A3 B3 D3
7 C7 A1 B2 D4

I want to do the following tasks:

Step1: select criteria = A1 in Field1 then display the table, so the table should be like this:

id contents Field1 Field2 Field3

1 C1 A1 B1 D1
3 C3 A1 B2 D1
7 C7 A1 B2 D4

Then select criteria = B2 in Field2 based on Step 1, then the result should be like this:

id contents Field1 Field2 Field3

3 C3 A1 B2 D1
7 C7 A1 B2 D4

Of course the criteria can be chosen randomly.
Does anyone know how to realize this goal?

Thank you very much for any help!
Chuan Wang
Royal Institute of Technology
Stockholm, Sweden
Homepage:
 
In view of the nature and level of your recent posts, it may be worth your while doing a little background reading on VB and ADO.

1. VB help has some good articles on the general subject.

2. If you do a GOOgle search on ADO you will find numerous references to good textbooks.

3. There are lots of code samples and snippets for ADO and VB on the Microsoft website: Try this one:

4. See my FAQ as referenced below! Let me know if this helps

Check out FAQ222-2244
'There are 10 kinds of people in the world: those who understand binary, and those who don't.'
 
Hi Johnwm,

Do you mean that I must use ADO, no other choice, right? But regarding to my case, I did not use ADO. What I have done is to show one table like in Step 1 above by using Data and FlexGrid control. I want to know whether I can go to Step 2 and Step 3 based on this.

Thanks, Chuan Wang
Royal Institute of Technology
Stockholm, Sweden
Homepage:
 
There are several possible answers to what you ask. The best answer will depend on how you want users to select the criteria, and how you want to display the data.

For a simple case you can loop through the rows of the Flexgrid to match your criteria.

This would be a poor choice for a 'proper' program on a real-sized database, and I would have preferred using ADO and doing the search on the database.

Did you read the faq referred? Let me know if this helps

Check out FAQ222-2244
'There are 10 kinds of people in the world: those who understand binary, and those who don't.'
 
Yes, I have looked through your FAQ, also gave some comments to it.

My problem is that I am begineer with VB and Access, I am not familiar with ADO. so I prefer to use FlexGrid and Data control. If you can give me some further suggestion how I can loop through the rows of the Flexgrid to match my criteria.

As I metioned with this thread, I want to realize the following goal:

Step1: select criteria = A1 in Field1 then display the table, so the table should be like this:

id contents Field1 Field2 Field3

1 C1 A1 B1 D1
3 C3 A1 B2 D1
7 C7 A1 B2 D4

Then select criteria = B2 in Field2 based on Step 1, then the result should be like this:

id contents Field1 Field2 Field3

3 C3 A1 B2 D1
7 C7 A1 B2 D4

Of course the criteria can be chosen randomly.

Thanks a lot!


Chuan Wang
Royal Institute of Technology
Stockholm, Sweden
Homepage:
 
If your Flexgrid is bound to a data control, the data is read only, so you would need to extract the answers to another control.

If you really need to use bound controls, have you thought about binding to a query rather than a table?

You could then dynamically change the query and refresh the datacontrol and the Flexgrid.

VB help has some good stuff on bound controls. Let me know if this helps

Check out FAQ222-2244
'There are 10 kinds of people in the world: those who understand binary, and those who don't.'
 
I think that this question is not difficult to you, but for me it is. In view of my poor knowledge, I can not understand or how to do with your writing:

"binding to a query rather than a table?

Then dynamically change the query and refresh the datacontrol and the Flexgrid."

Tack!
Chuan Wang
Royal Institute of Technology
Stockholm, Sweden
Homepage:
 
In your database you have a table (tblResults) with the following fields

ID, contents, field1, field2, field3

On your form you have

A DataControl (Data1) with its database property set to your database and its recordsource left blank

A Flexgrid (Flex1) with its datasource set to the datacontrol

2 textboxes (Text1 and Text2) to get the criteria

A command button (Command1) to do the search and display results

Put the following code in the form

Code:
Private Sub Command1_Click()
Dim mySQL As String
mySQL = "SELECT * From tblResults WHERE ((tblResults.Field1) Like '" & Text1.Text & "*') AND ((tblResults.Field2) Like '" & Text2.Text & "*');"
Data1.RecordSource = mySQL
Data1.Refresh
End Sub

Make sure that all the code from 'mySQL = ...' through to '...Text2.Text & "*');" is all on one line in your code

Let me know if this helps

Check out FAQ222-2244
'There are 10 kinds of people in the world: those who understand binary, and those who don't.'
 
I got to go home today. Thank you very much for whatever you have done. Good luck! And see you tommorrow! Chuan Wang
Royal Institute of Technology
Stockholm, Sweden
Homepage:
 
Given the fact that you have the data already in the FlexGrid, and you want to alter the display, based on varying criteria, then you could effectively accomplish this play scanning the grid, and hiding the rows that do not meet the criteria, and providing a search within search criteria. The hiding can be accomplished by setting the row height to 0, and the search within search by checking the RowHeight of an existing row before checking the data.

I am making one assumption - that being that you have a header row (row 0) which will always be displayed, and the height of that row will be the height of all rows.

The General Function would be something like the following:

'=======================================================

Private Sub QueryGrid (ColID as Integer, ColVal as String, SearchWithin as Boolean)

Dim RowID as Integer
Dim CheckRow as Boolean

grdGrid.Redraw = False ' Turn Redraw Off for Speed
For RowID = grdGrid.FixedRows to grdGrid.Rows - 1

' If we're doing a search within, then we only want to
' check this row if that row is already visible - the
' row height is greater than 0.

If (SearchWithin = True) Then
CheckRow = (grdGrid.RowHeight(RowID) > 0)
Else
CheckRow = True
End If

' If we need to check this row, then compare the the value
' in the grid against the input parameter value for the
' column identified as the input parameter

If (CheckRow = True) Then
If (grdGrid.TextMatrix(RowID, ColID) = ColVal) Then
' It matches so set row height to same as header
grdGrid.RowHeight(RowID) = grdGrid.RowHeight(0)
Else
' match failed - hide the row
grdGrid.RowHeight(RowID) = 0
End If
End If
Next RowID

grdGrid.Redraw = True ' Redraw the Grid

End Sub


In your code, after you've loaded the grid, based on your stated requirements,

Step1: select criteria = A1 in Field1 then display the table
Since, Field1 is in column 2 (from your chart)

QueryGrid (2, "A1", False)

Search the entire grid, and show only rows which have an "A1" in column 2

Then select criteria = B2 in Field2 based on Step 1, then the call would look like this

QueryGrid (3, "B2", True)

Search within only the currently displayed rows for a value of "B2" in column three.

You will need another routine to "refresh" the entire grid, so that all rows are visible

For RowID = grdGrid.FixedRows to grdGrid.Rows - 1
grdGrid.RowHeight(RowID) = grdGrid.RowHeight(0)
Next RowID
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top