R U a datagrid (ADO) Pro?

[vbuser1976]

Hi Friends!

Here's my situation:

I have a text box, 3 radio buttons , and a datagrid. I am trying to do a search of a database with the ability to do it three separate ways. The user would type in keywords in the textbox and then select one of the three radio buttons (let's call them title, subject, IDnumber) I want to run the search based on the text and the selected radio button, so how do I go about doing this. I know that I need to change the recordsource at runtime but I need to get it working. I would also like sample code or links to resources, if possible. Thanks in advance.

[KMcKenzie]

You have to build your SQL statement for your recordset dynamically.

Dim sSQLSelect as string
Dim sSQLWhere as String

SSQL = “Select fieldname from tablename ”
If radioButton1 = true then sSQLWhere = “where fieldname = ‘“ & text1.text & “’”
Else if radiobutton2 = true then sSQLWhere = “where fieldname = ‘”& txt1.text & “’”
Else if radiobutton3 = true then sSQLWhere = “where fieldname = ‘“ &txt1.text & “’”

sSQLSelect = sSQLSelect & SSQLWhere

rs.open sSQLSelect and all the options including adcmdtext

Good Luck

[vbuser1976]

Thanks for the help. What about the datagrid? How do I set it up where it will not populate it until the recordset is created? In Other words, how do I change the properties of the datagrid in such a way that it does what I need it to do?

[KMcKenzie]

Set the datasource of the grid to the resordset and when you change the query then requery the recordset

Datagrid.recordsource = rs

On button click or when the focus leaves the text box that has your criteria in it then open then requery the recordset

recordset.requery and refresh the datatgrid
datagrid.refresh to diplay the new query results.

[vbuser1976]

I am so sorry, but could you please show me some code or send me a link that has some code? I just want to see an example. Thanks.

[KMcKenzie]

If you just wan to display data then the MSHierarchalFlexGrid is very easy to use and what I have used in this code.
This code is at the most simple but works fine and should produce no errors.



Private Sub Command1_Click()


Dim sSQLSelect As String
Dim sSQLWhere As String
Dim rs As ADODB.Recordset


Set rs = New ADODB.Recordset

If gobjConnect.State = adStateClosed Then
gobjConnect.CursorLocation = adUseClient
gobjConnect.Open
End If

sSQLSelect = "Select * from EMPLOYEE "
If Option1.Value = True Then
sSQLWhere = "where LAST_NAME = '" & Text1.Text & "'"
ElseIf Option2.Value = True Then
sSQLWhere = "where ID ='" & Text1.Text & "'"
ElseIf Option3.Value = True Then
sSQLWhere = "where DEPARTMENT_ID ='" & Text1.Text & "'"
End If

sSQLSelect = sSQLSelect & sSQLWhere

rs.Open sSQLSelect, gobjConnect, adOpenStatic, adLockBatchOptimistic, adCmdText
If Not rs.EOF And Not rs.BOF Then
rs.MoveFirst
End If

rs.ActiveConnection = Nothing

Set MSHFlexGrid1.DataSource = rs



End Sub

[KMcKenzie]

I'll also add that if you expect a value in text1 then you should the single quotes out.

[vbuser1976]

Thanks so much, it worked. Just had to switch the last two lines of code in your example. One more thing that maybe you or someone else in the forum can answer: How can I open the recordset in the flexgrid in another form? Let me explain: Now that the 1st form has run the search, I want to highlight the record that I want, click an Open button, open another form and show the record/field values there in textboxes. I hope this isn't confusing. Any ideas?

Thanks in advance.

[KMcKenzie]

In the second form put a procedure with parameters that will accept the values from the grid. When the user selects a value in the grid call that procedure passing it values from the grid. You may also use that procedure to hide and show the forms.

You might also think about keeping the text boxes on the same form and just hiding them asn showing them as well as the grid at the appropiate times, using the visible properties.

[vbuser1976]

how do you pass the parameter of a record that is in a flexgrid when there is more than one record showing? How do you tell it to look at the record that is highlighted?

[KMcKenzie]

Check help for the grid you are using look at mouserow, rowsel, text, field, col etc.

you'll have to pass the values of each column from the selected row.

[vbuser1976]

Thanks so much. Enjoy your weekend!