Trivial

I want my search results from my SQL query to appear in a datagrid, why is it that I can find no examples? I dont want to use a flexgrid because the rest of my program is in datagrids and it will look out of place.
The code below is what im using and my grid is still empty:

frmSearch
Public strCriteria As String 'read in from input box on different form
Public strField As String 'the column name selected by the user

Private Sub Form_Load()

Dim srs As Recordset
Dim ssql As String
Set dgSearch.DataSource = srs 'dgSearch is the grid
ssql = "SELECT * FROM Members WHERE " + Format(strField) + " = '" + Format(strCriteria) + "'"
Set srs = frmMainForm.db.OpenRecordset(ssql, dbOpenDynaset)
srs.FindFirst (strField + " = '" + strCriteria + "'")
If srs.NoMatch Then

MsgBox "There are no " + strField + "s that match " & "'" + strCriteria + "'", vbOKOnly, "No Match"

Else
MsgBox frmMainForm.rs(strField).Value, vbOKOnly, "Woohoo"

End If

End Sub

It works if the value is not in the database, but if it is it just states the first value in the database and not the one that was actually searched for.
I basically want it so that if there are 3 smiths they all appear in the grid and the user can determine which one they want based on the other columns ie. first name, address.

Any help would be great, I'm sure its simple but I'm tired.

BostjanK

First of all, why are you using Format(strField) for the field name in WHERE...?

Second, if more than one record matches your condition, you must loop throught them to retrieve them all:

joe77s

try this....

Set srs = frmMainForm.db.OpenRecordset(ssql, dbOpenDynaset)
Set dgSearch.DataSource = srs 'dgSearch is the grid

set srs to dgSearch after srs open.....

use ADO...it's more easier for me

Trivial

Thanks for the help, didn't notice the Format(strField) in the WHERE statement, a friends idea of helping . The loop helped and now it displays the searched name, but unfortunately my datagrid is still empty.
I tried that Joe77s but no matter where I put that line (Set dgSearch.DataSource = srs) it doesnt like it, I get a runtime error, 'type mismatch'.
Now if I get get the grid to show the values I'll be very happy indeed.

joe77s

at which line you get this error ??

i try to edit your code.....



Public strCriteria As String 'read in from input box on different form
Public strField As String 'the column name selected by the user

Private Sub Form_Load()

Dim srs As Recordset
Dim ssql As String
ssql = "SELECT * FROM Members WHERE '" & strField & "' = '" & strCriteria & "' "
Set srs = frmMainForm.db.OpenRecordset(ssql, dbOpenDynaset)
Set dgSearch.DataSource = srs 'dgSearch is the grid
sssql = strField & " = " & strCriteria
srs.movelast
srs.FindFirst sssql
If srs.NoMatch Then

MsgBox "There are no " & strField & "s that match " & strCriteria , vbOKOnly, "No Match"

Else
MsgBox frmMainForm.rs(strField).Value, vbOKOnly, "Woohoo"

End If

End Sub

BostjanK

Quote:

---

Originally Posted by joe77s
at which line you get this error ??

i try to edit your code.....



Public strCriteria As String 'read in from input box on different form
Public strField As String 'the column name selected by the user

Private Sub Form_Load()

Dim srs As Recordset
Dim ssql As String
ssql = "SELECT * FROM Members WHERE '" & strField & "' = '" & strCriteria & "' "
Set srs = frmMainForm.db.OpenRecordset(ssql, dbOpenDynaset)
Set dgSearch.DataSource = srs 'dgSearch is the grid
sssql = strField & " = " & strCriteria
srs.movelast
srs.FindFirst sssql
If srs.NoMatch Then

MsgBox "There are no " & strField & "s that match " & strCriteria , vbOKOnly, "No Match"

Else
MsgBox frmMainForm.rs(strField).Value, vbOKOnly, "Woohoo"

End If

End Sub

---

is not a valid SQL statement - field names must not be enclosed in '

Try this

Trivial

Ok Thanks heaps for the help, my code now looks like this:

Public strCriteria As String
Public strField As String
Public ssql As String


Private Sub Form_Load()

Dim ssql As String
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Documents and Settings\Administrator\Desktop\Mydatabase.mdb"

Dim srs As ADODB.Recordset
Set srs = New ADODB.Recordset

ssql = "SELECT * FROM Members WHERE " & strField & " = '" & strCriteria & "'"
Set srs = conn.Execute(ssql)


If srs.EOF Then
MsgBox "There are no " + strField + "s that match " & "'" + strCriteria + "'", vbOKOnly, "No Match"
Else
Do While Not srs.EOF

MsgBox srs(strField) & " was found" ' this could be a lot of boxes
srs.MoveNext
Loop
End If

End Sub

This is looking much better, can anyone suggest where I place the (Set dgSearch.DataSource = srs) like statement, or whatever else will display it to the grid. You have all been very helpful, you dont know how much this helps .

joe77s

after this code.....
Set srs = conn.Execute(ssql)
Set dgSearch.DataSource = srs


....nice can help.....

Trivial

It doesnt like that line grrr. I get run-time error '7004' The rowset is not bookmarkable. Am I forgetting to set something somewhere else?

BostjanK

Actually, when I wrote Do While...Loop that was meant as an alternative to Set dgSearch.DataSource = srs.
Displaying a MsgBox with each record is of course annoying, but Do While...Loop is great if you need to cotrol the population of a DataGrid (or any other control).
Let's say you have DBGrid with two columns - 1st is a number from DB (field1), while 2nd is field1*2. This would look like:

joe77s

ups.......

hehehe....don't be angry....

what grid are you using......

if you use Microsoft DataGrid Control (msDatGrd.Ocx)
you better use DataControl....

Trivial

Ok yea I got the idea that you meant do one or the other but neither of them filled the grid. I've put in the new loop you gave me and I get an error saying rowset not available, any ideas?

Im using an adoc and datagrid, at this stage Im willing to sacrifice looks for functionality so any ideas to get the results in the form of a list or grid would be great.

Trivial

Ok I put in this line like I saw on lots of other posts and it still didnt work:
conn.CursorLocation = adUseClient
But then I went back to my old loop without the .row lines in it and it worked!!!
Thank you so much everyone for all your help! Look out for my name because I may be asking a lot more questions, and I look forward to helping other people when I get more confident with VB.