Database record search using ADO [RESOLVED]

[hscott]

Hi,
I need a form to allow users to type a search query into any textbox linked to a record. I need the search results to be displayed in an MSFlexGrid. The user needs to be able to search in a single record or in multiple records. If someone could point me in the right direction or better still show me some example code it would be greatly appreciated. Also could some tell me how to populate the FlexGrid with the results once the search has finished.
I hope what I am trying to do makes sense.
Thanks in advance.

[PilgrimPete]

Well...
You could build a dynamic query on the fly:

Dim strSQL as string, strWhere as string
 
'basic SQL
strSQL = "SELECT * FROM myTable"
 
'now create a WHERE clause
If txtSampleNo.Text <> vbNullstring then
    strWhere = strWhere & " AND SampleNum = '" & txtSampleNo.Text & "'"
End If
If txtLocation.Text <> vbNullstring then
    strWhere = strWhere & " AND Location = '" & txtLocation.Text & "'"
End If
'etc. etc.
 
'append the WHERE clause (if any) to the basic SQL
if strWhere <> vbNullString then
    strSQL = strSQL & " WHERE " & Mid$(strWhere, 4)
End If
 
'fire your query here...

[hscott]

Thanks for the suggestion. I now have the following code, but have no idea what to do next. I am new to VB and SQL, I have just learnt the basics and things I need for this project, so please explain thinks as simply as possible. Thanks very much.

Private Sub CmdSearch_Click()
Dim strSQL As String, strWhere As String
 
strSQL = "SELECT * FROM Asbestos"
 
If Text1.Text <> vbNullString Then
    strWhere = strWhere & " AND SampleNumber = '" & Text1.Text & "*"
End If
If Text2.Text <> vbNullString Then
    strWhere = strWhere & " AND Location = '" & Text2.Text & "*"
End If
If Text3.Text <> vbNullString Then
    strWhere = strWhere & " AND ProductType = '" & Text3.Text & "*"
End If
If Text4.Text <> vbNullString Then
    strWhere = strWhere & " AND AsbestosType = '" & Text4.Text & "*"
End If
If Text5.Text <> vbNullString Then
    strWhere = strWhere & " AND LocationInUnit = '" & Text5.Text & "*"
End If
If Text6.Text <> vbNullString Then
    strWhere = strWhere & " AND PBCJobNumber = '" & Text6.Text & "*"
End If
If Text7.Text <> vbNullString Then
    strWhere = strWhere & " AND WorkDone = '" & Text7.Text & "*"
End If
If Text8.Text <> vbNullString Then
    strWhere = strWhere & " AND DateOfCompletion = '" & Text8.Text & "*"
End If
If Text9.Text <> vbNullString Then
    strWhere = strWhere & " AND AirTestCertificateNumber = '" & Text9.Text & "*"
End If
If Text10.Text <> vbNullString Then
    strWhere = strWhere & " AND AssessmentScoreMaterial = '" & Text10.Text & "*"
End If
If Text11.Text <> vbNullString Then
    strWhere = strWhere & " AND AssessmentScorePriority = '" & Text11.Text & "*"
End If
If Text12.Text <> vbNullString Then
    strWhere = strWhere & " AND AssessmentScoreTotal = '" & Text12.Text & "*"
End If
 
If strWhere <> vbNullString Then
    strSQL = strSQL & " WHERE " & Mid$(strWhere, 4)
 
End If
End Sub

[PilgrimPete]

Something like this will get you started:

Dim cnnExample As ADODB.Connection
    Dim rstExample As ADODB.Recordset
    
    'create and open the connection
    Set cnnExample = New ADODB.Connection
    With cnnExample
        .ConnectionString = "blah"
        .Open
        'get the recordset
        Set rstExample = New ADODB.Recordset
        Set rstExample = cnnExample.Execute(strSQL)
        'disconnect the recordset
        Set rstExample.ActiveConnection = Nothing
        'drop the connection
        .Close
    End With
    
    Me.MSFlexGrid1.DataSource = rstExample

I'm just going into a meeting right now... I'll come back and check how you're getting on in a bit..!

[hscott]

Thanks very much for your time and patience. I now have the following code.

Option Explicit
Private rsSearch As ADODB.Recordset
Private cn As ADODB.Connection
 
Private Sub Form5_Load()
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\Documents and Settings\Harry Scott\My Documents\Homework\Computing\Asbestos Register\Database.mdb"
cn.Open
Set rsSearch = New ADODB.Recordset
Set rsSearch = cn.Execute(strSQL)
Set rsSearch.ActiveConnection = Nothing
cn.Close
Me.MSFlexGrid1.DataSource = rsSearch
End Sub
 
Private Sub CmdSearch_Click()
Dim strSQL As String, strWhere As String
 
strSQL = "SELECT * FROM Asbestos"
 
If Text1.Text <> vbNullString Then
    strWhere = strWhere & " AND SampleNumber = '" & Text1.Text & "*"
End If
If Text2.Text <> vbNullString Then
    strWhere = strWhere & " AND Location = '" & Text2.Text & "*"
End If
If Text3.Text <> vbNullString Then
    strWhere = strWhere & " AND ProductType = '" & Text3.Text & "*"
End If
If Text4.Text <> vbNullString Then
    strWhere = strWhere & " AND AsbestosType = '" & Text4.Text & "*"
End If
If Text5.Text <> vbNullString Then
    strWhere = strWhere & " AND LocationInUnit = '" & Text5.Text & "*"
End If
If Text6.Text <> vbNullString Then
    strWhere = strWhere & " AND PBCJobNumber = '" & Text6.Text & "*"
End If
If Text7.Text <> vbNullString Then
    strWhere = strWhere & " AND WorkDone = '" & Text7.Text & "*"
End If
If Text8.Text <> vbNullString Then
    strWhere = strWhere & " AND DateOfCompletion = '" & Text8.Text & "*"
End If
If Text9.Text <> vbNullString Then
    strWhere = strWhere & " AND AirTestCertificateNumber = '" & Text9.Text & "*"
End If
If Text10.Text <> vbNullString Then
    strWhere = strWhere & " AND AssessmentScoreMaterial = '" & Text10.Text & "*"
End If
If Text11.Text <> vbNullString Then
    strWhere = strWhere & " AND AssessmentScorePriority = '" & Text11.Text & "*"
End If
If Text12.Text <> vbNullString Then
    strWhere = strWhere & " AND AssessmentScoreTotal = '" & Text12.Text & "*"
End If
 
If strWhere <> vbNullString Then
    strSQL = strSQL & " WHERE " & Mid$(strWhere, 4)
End If
End Sub
 
Private Sub Form5_Unload(Cancel As Integer)
rsSearch.Close
cn.Close
Set rsSearch = Nothing
Set cn = Nothing
End Sub

When I type a query into the box and click on the search button, nothing happens. Can anyone see any errors in my code?

Also, I am not sure what this does:

If strWhere <> vbNullString Then
    strSQL = strSQL & " WHERE " & Mid$(strWhere, 4)
End If

Can anyone explain this?
Thanks very much.

[PilgrimPete]

No problem. Sorry I rushed the last post a bit..!

The reason that nothing happens is that you need to do the recordset opening bit after building your strSQL string - your snippet just does it on the loading of the form.

The

If strWhere <> vbNullString Then
    strSQL = strSQL & " WHERE " & Mid$(strWhere, 4)
End If

bit checks to see if there is a WHERE clause and if so, adds it to the SELECT... string to create your customised query. (Oh, and the Mid$ is to chop off the first AND as we don't need it.)

[PilgrimPete]

Oh. And I just noticed you are using Access. You'll need to tweak your code a bit. Sorry.

If Text11.Text <> vbNullString Then
    strWhere = strWhere & " AND AssessmentScorePriority [B][COLOR=Red]LIKE[/COLOR][/B] [COLOR=Red]""[/COLOR]" & Text11.Text & "*[COLOR=Red]""[/COLOR]"
End If

- Access uses double quotes as string delimiters.
- You'll need LIKE if you want to use wildcards

[hscott]

I am now getting the following error when I click search:
The error is on line:

Set rsSearch = cn.Execute(strSQL)

This happens when I enter something in any of the boxes and click search.

[PilgrimPete]

Sorry I messed up. 1 character out.
Try

strSQL = strSQL & " WHERE " & Mid$(strWhere, [COLOR=Red]5[/COLOR])

[hscott]

I tried that. I now get a different error message on the same line.

[PilgrimPete]

Hmmmm. I'm not doing so well here.
Can you do a

Debug.Print strSQL

and post the SQL string?

[hscott]

SELECT * FROM Asbestos WHERE AsbestosType LIKE "Amosite*"

[PilgrimPete]

Looks good to me. I've created a dummy database and it runs here OK.
Have you tried pasting the SQL into an Access query and running it there?

Sorry in advance for this question... but you do have a column called AsbestosType in your table don't you?

[hscott]

Now I feel stupid, the AsbestosType has a space in my database.
I have changed my code to match the table but I am now getting the original error message from above.

[PilgrimPete]

No probs. We've all been there
You'll need to either remove the spaces in your column names, or enclose them in square brackets thus: [Asbestos type], otherwise Access (or most other DBs) won't which is the column name. You need to do the same if you use any 'reserved' words (i.e. words that Access - or SQL - has a different meaning for). In the long run it is best not to use spaces or reserved words in column names.

[hscott]

I am now getting a different error message on line:

Set rsSearch.ActiveConnection = Nothing

[PilgrimPete]

Sorry - juts me trying to be clever (always a bad idea) and use disconnected recordsets (you can read about those here) without doing all the necessary things like using client-side cursors etc.
You can safely comment out/delete that line.

[hscott]

I deleted the line, it fixed that error. I now have a new error when I click search. The error is on the following line:

Me.MSFlexGrid1.DataSource = rsSearch

[PilgrimPete]

I'm really not doing very well here am I?
I'll look into how you bind a flex grid to a recordset and get back to you...
PP

[PilgrimPete]

OK. Looks like you can't do it.
You can with a hierarchical flex grid, but not a vanilla flex grid.

Set Me.MSHFlexGrid1.Recordset = rsSearch

You can do it with the Flex Grid if you loop through the recordset like this:

Option Explicit
Private rsSearch As ADODB.Recordset
Private cn As ADODB.Connection
 
Private Function GetRecordset(ByVal strSQL As String) As ADODB.Recordset
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\Documents and Settings\Harry Scott\My Documents\Homework\Computing\Asbestos Register\Database.mdb"
    cn.CursorLocation = adUseClient
    cn.Open
    Set rsSearch = New ADODB.Recordset
    rsSearch.CursorLocation = adUseClient
    Set rsSearch = cn.Execute(strSQL)
    Set rsSearch.ActiveConnection = Nothing
    cn.Close
    Set GetRecordset = rsSearch
End Function
 
Private Sub LoadTheGrid(ByVal strSQL As String)
    Dim rs As ADODB.Recordset
    Dim iCol As Integer, iRow As Integer
    Dim fld As ADODB.Field
    
    Set rs = GetRecordset(strSQL)
    iRow = 0
    With rs
        Me.MSFlexGrid1.Rows = rs.RecordCount
        Me.MSFlexGrid1.Cols = rs.Fields.Count
        Do Until .EOF
            iCol = 0
            For Each fld In .Fields
                Me.MSFlexGrid1.TextMatrix(iRow, iCol) = fld.Value
                iCol = iCol + 1
            Next fld
            iRow = iRow + 1
            .MoveNext
        Loop
    End With
End Sub
 
Private Sub Form_Load()
    LoadTheGrid "SELECT * FROM Asbestos"
End Sub

[hscott]

I have entered the above code. And my complete code for this form is now:

Option Explicit
Private rsSearch As ADODB.Recordset
Private cn As ADODB.Connection
 
Private Sub Form5_Load()
LoadTheGrid "SELECT * FROM Asbestos"
End Sub
 
Private Function GetRecordset(ByVal strSQL As String) As ADODB.Recordset
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\Documents and Settings\Harry Scott\My Documents\Homework\Computing\Asbestos Register\Database.mdb"
    cn.CursorLocation = adUseClient
    cn.Open
    Set rsSearch = New ADODB.Recordset
    rsSearch.CursorLocation = adUseClient
    Set rsSearch = cn.Execute(strSQL)
    Set rsSearch.ActiveConnection = Nothing
    cn.Close
    Set GetRecordset = rsSearch
End Function
 
Private Sub LoadTheGrid(ByVal strSQL As String)
    Dim rs As ADODB.Recordset
    Dim iCol As Integer, iRow As Integer
    Dim fld As ADODB.Field
    
    Set rs = GetRecordset(strSQL)
    iRow = 0
    With rs
        Me.MSFlexGrid1.Rows = rs.RecordCount
        Me.MSFlexGrid1.Cols = rs.Fields.Count
        Do Until .EOF
            iCol = 0
            For Each fld In .Fields
                Me.MSFlexGrid1.TextMatrix(iRow, iCol) = fld.Value
                iCol = iCol + 1
            Next fld
            iRow = iRow + 1
            .MoveNext
        Loop
    End With
End Sub
 
 
Private Sub CmdSearch_Click()
Dim strSQL As String, strWhere As String
 
strSQL = "SELECT * FROM Asbestos"
 
If Text1.Text <> vbNullString Then
    strWhere = strWhere & " AND [Sample Number] LIKE """ & Text1.Text & "*"""
End If
If Text2.Text <> vbNullString Then
    strWhere = strWhere & " AND [Location] LIKE """ & Text2.Text & "*"""
End If
If Text3.Text <> vbNullString Then
    strWhere = strWhere & " AND [Product Type] LIKE """ & Text3.Text & "*"""
End If
If Text4.Text <> vbNullString Then
    strWhere = strWhere & " AND [Asbestos Type] LIKE """ & Text4.Text & "*"""
End If
If Text5.Text <> vbNullString Then
    strWhere = strWhere & " AND [Location In Unit] LIKE """ & Text5.Text & "*"""
End If
If Text6.Text <> vbNullString Then
    strWhere = strWhere & " AND [PBC Job Number] =LIKE """ & Text6.Text & "*"""
End If
If Text7.Text <> vbNullString Then
    strWhere = strWhere & " AND [Work Done] LIKE """ & Text7.Text & "*"""
End If
If Text8.Text <> vbNullString Then
    strWhere = strWhere & " AND [Date Of Completion] LIKE """ & Text8.Text & "*"""
End If
If Text9.Text <> vbNullString Then
    strWhere = strWhere & " AND [Air Test Certificate Number] LIKE """ & Text9.Text & "*"""
End If
If Text10.Text <> vbNullString Then
    strWhere = strWhere & " AND [Assessment Score - Material] LIKE """ & Text10.Text & "*"""
End If
If Text11.Text <> vbNullString Then
    strWhere = strWhere & " AND [Assessment Score - Priority] LIKE """ & Text11.Text & "*"""
End If
If Text12.Text <> vbNullString Then
    strWhere = strWhere & " AND [Assessment Score - Total] LIKE """ & Text12.Text & "*"""
End If
If strWhere <> vbNullString Then
    strSQL = strSQL & " WHERE " & Mid$(strWhere, 5)
End If
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\Documents and Settings\Harry Scott\My Documents\Homework\Computing\Asbestos Register\Database.mdb"
cn.Open
Set rsSearch = New ADODB.Recordset
Set rsSearch = cn.Execute(strSQL)
Set Me.MSFlexGrid1.DataSource = rsSearch
cn.Close
End Sub
 
Private Sub Form5_Unload(Cancel As Integer)
rsSearch.Close
cn.Close
Set rsSearch = Nothing
Set cn = Nothing
End Sub

I now get a different error message when I click search.
The error is on the following line:

Set Me.MSFlexGrid1.DataSource = rsSearch

Sorry about all the questions. I really appreciate you taking the time to help me.

[PilgrimPete]

Sorry - my code was just an example. Try this:

Private Sub CmdSearch_Click()
    Dim strSQL As String, strWhere As String
    
    strSQL = "SELECT * FROM Asbestos"
    
    If Text1.Text <> vbNullString Then
        strWhere = strWhere & " AND [Sample Number] LIKE """ & Text1.Text & "*"""
    End If
    If Text2.Text <> vbNullString Then
        strWhere = strWhere & " AND [Location] LIKE """ & Text2.Text & "*"""
    End If
    If Text3.Text <> vbNullString Then
        strWhere = strWhere & " AND [Product Type] LIKE """ & Text3.Text & "*"""
    End If
    If Text4.Text <> vbNullString Then
        strWhere = strWhere & " AND [Asbestos Type] LIKE """ & Text4.Text & "*"""
    End If
    If Text5.Text <> vbNullString Then
        strWhere = strWhere & " AND [Location In Unit] LIKE """ & Text5.Text & "*"""
    End If
    If Text6.Text <> vbNullString Then
        strWhere = strWhere & " AND [PBC Job Number] =LIKE """ & Text6.Text & "*"""
    End If
    If Text7.Text <> vbNullString Then
        strWhere = strWhere & " AND [Work Done] LIKE """ & Text7.Text & "*"""
    End If
    If Text8.Text <> vbNullString Then
        strWhere = strWhere & " AND [Date Of Completion] LIKE """ & Text8.Text & "*"""
    End If
    If Text9.Text <> vbNullString Then
        strWhere = strWhere & " AND [Air Test Certificate Number] LIKE """ & Text9.Text & "*"""
    End If
    If Text10.Text <> vbNullString Then
        strWhere = strWhere & " AND [Assessment Score - Material] LIKE """ & Text10.Text & "*"""
    End If
    If Text11.Text <> vbNullString Then
        strWhere = strWhere & " AND [Assessment Score - Priority] LIKE """ & Text11.Text & "*"""
    End If
    If Text12.Text <> vbNullString Then
        strWhere = strWhere & " AND [Assessment Score - Total] LIKE """ & Text12.Text & "*"""
    End If
    If strWhere <> vbNullString Then
        strSQL = strSQL & " WHERE " & Mid$(strWhere, 5)
    End If
    
    'this will do the grid loading for you - so you can get rid of the other code
    LoadTheGrid strSQL
 
End Sub

[hscott]

The FlexGrid now seems to activate when I click search but no records appear.

Option Explicit
Private rsSearch As ADODB.Recordset
Private cn As ADODB.Connection
 
Private Sub Form5_Load()
LoadTheGrid "SELECT * FROM Asbestos"
End Sub
 
Private Function GetRecordset(ByVal strSQL As String) As ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\Documents and Settings\Harry Scott\My Documents\Homework\Computing\Asbestos Register\Database.mdb"
cn.CursorLocation = adUseClient
cn.Open
Set rsSearch = New ADODB.Recordset
rsSearch.CursorLocation = adUseClient
Set rsSearch = cn.Execute(strSQL)
Set rsSearch.ActiveConnection = Nothing
cn.Close
Set GetRecordset = rsSearch
End Function
 
Private Sub LoadTheGrid(ByVal strSQL As String)
Dim rs As ADODB.Recordset
Dim iCol As Integer, iRow As Integer
Dim fld As ADODB.Field
Set rs = GetRecordset(strSQL)
iRow = 0
Me.MSFlexGrid1.Rows = rs.RecordCount
Me.MSFlexGrid1.Cols = rs.Fields.Count
Do Until rs.EOF
iCol = 0
For Each fld In rs.Fields
Me.MSFlexGrid1.TextMatrix(iRow, iCol) = fld.Value
iCol = iCol + 1
Next fld
iRow = iRow + 1
rs.MoveNext
Loop
End Sub
 
Private Sub CmdSearch_Click()
Dim strSQL As String, strWhere As String
strSQL = "SELECT * FROM Asbestos"
If Text1.Text <> vbNullString Then
    strWhere = strWhere & " AND [Sample Number] LIKE """ & Text1.Text & "*"""
End If
If Text2.Text <> vbNullString Then
    strWhere = strWhere & " AND [Unit Number] LIKE """ & Text2.Text & "*"""
End If
If Text3.Text <> vbNullString Then
    strWhere = strWhere & " AND [Product Type] LIKE """ & Text3.Text & "*"""
End If
If Text4.Text <> vbNullString Then
    strWhere = strWhere & " AND [Asbestos Type] LIKE """ & Text4.Text & "*"""
End If
If Text5.Text <> vbNullString Then
    strWhere = strWhere & " AND [Location In Unit] LIKE """ & Text5.Text & "*"""
End If
If Text6.Text <> vbNullString Then
    strWhere = strWhere & " AND [PBC Job Number] =LIKE """ & Text6.Text & "*"""
End If
If Text7.Text <> vbNullString Then
    strWhere = strWhere & " AND [Work Done] LIKE """ & Text7.Text & "*"""
End If
If Text8.Text <> vbNullString Then
    strWhere = strWhere & " AND [Date Of Completion] LIKE """ & Text8.Text & "*"""
End If
If Text9.Text <> vbNullString Then
    strWhere = strWhere & " AND [Air Test Certificate Number] LIKE """ & Text9.Text & "*"""
End If
If Text10.Text <> vbNullString Then
    strWhere = strWhere & " AND [Assessment Score - Material] LIKE """ & Text10.Text & "*"""
End If
If Text11.Text <> vbNullString Then
    strWhere = strWhere & " AND [Assessment Score - Priority] LIKE """ & Text11.Text & "*"""
End If
If Text12.Text <> vbNullString Then
    strWhere = strWhere & " AND [Assessment Score - Total] LIKE """ & Text12.Text & "*"""
End If
If strWhere <> vbNullString Then
    strSQL = strSQL & " WHERE " & Mid$(strWhere, 5)
End If
LoadTheGrid (strSQL)
End Sub
 
Private Sub Form5_Unload(Cancel As Integer)
rsSearch.Close
cn.Close
Set rsSearch = Nothing
Set cn = Nothing
End Sub

[PilgrimPete]

OK. Looks like we're not getting any records returned.
If you put a breakpoint in LoadTheGrid, how many rows are returned (what does rs.RecordCount contain?).

[hscott]

rs.RecordCount = 0

[PilgrimPete]

That'll be it then.
Have you tried pasting the query into Access again?
Could you post it here too?
Would you expect to get records for that criterion? How about if you remove the search text?

[hscott]

!!! IT WORKS !!!!
I have just realised my mistake. As I am using ADO I should have used % instead of * in my wildcard searches.
e.g.

strWhere = strWhere & " AND [Assessment Score - Total] LIKE """ & Text12.Text & "%"""

Thanks very much for your help PilgrimPete.

[PilgrimPete]

No probs.
Sorry it took so long!

[Pace]

lol that was like a mini adventure, well done

[namitao]

HI
I am looking at your code, I get an error when i run the code with some minor changes. I am using VB and SQL server.
The error I get is
Operation not allowed when the object is closed

The code is
Option Explicit

Private rsSearch As ADODB.Recordset
Private conSQL As ADODB.Connection

Private Sub frmHandsetSearch_Load()
LoadTheGrid "SELECT * FROM Handesets"
End Sub

Private Function GetRecordset(ByVal strSQL As String) As ADODB.Recordset

Set conSQL = New ADODB.Connection

Dim strSQLServer As String
Dim strSQLDatabaseToOpen As String
Dim strConnectString As String
Dim rsRecords As ADODB.Recordset

strSQLServer = "sfdaqsql1"
strSQLDatabaseToOpen = "techlog_copy"
strConnectString = "Provider=sqloledb;" & _
"Integrated Security=SSPI;" & _
"Data Source=" & strSQLServer & ";" & _
"Initial Catalog=" & strSQLDatabaseToOpen & ";"

conSQL.CursorLocation = adUseServer
conSQL.Open (strConnectString)

Set rsSearch = New ADODB.Recordset
rsSearch.CursorLocation = adUseServer
Set rsSearch = conSQL.Execute(strSQL)
rsSearch.Close
Set rsSearch.ActiveConnection = Nothing
conSQL.Close
Set GetRecordset = rsSearch
End Function

Private Sub LoadTheGrid(ByVal strSQL As String)
Dim rs As ADODB.Recordset
Dim iCol As Integer, iRow As Integer
Dim fld As ADODB.Field
Set rs = GetRecordset(strSQL)
iRow = 0
Me.MSFlexGrid1.Rows = rs.RecordCount
Me.MSFlexGrid1.Cols = rs.Fields.Count
Do Until rs.EOF
iCol = 0
For Each fld In rs.Fields
Me.MSFlexGrid1.TextMatrix(iRow, iCol) = fld.Value
iCol = iCol + 1
Next fld
iRow = iRow + 1
rs.MoveNext
Loop
End Sub

Private Sub cmdSearch_Click()
Dim strSQL As String, strWhere As String
strSQL = "SELECT * FROM Handsets"

If Text1.Text <> vbNullString Then
strWhere = strWhere & " AND [Telephia Asset Number] LIKE """ & Text1.Text & "*"""
End If
If Text2.Text <> vbNullString Then
strWhere = strWhere & " AND [ESN] LIKE """ & Text2.Text & "*"""
End If
If Text3.Text <> vbNullString Then
strWhere = strWhere & " AND [MIN] LIKE """ & Text3.Text & "*"""
End If
'If Text4.Text <> vbNullString Then
' strWhere = strWhere & " AND [Keyword LIKE """ & Text4.Text & "*"""
'End If
'If Text5.Text <> vbNullString Then
' strWhere = strWhere & " AND [Location In Unit] LIKE """ & Text5.Text & "*"""
'End If
LoadTheGrid (strSQL)
End Sub

Private Sub Form5_Unload(Cancel As Integer)
rsSearch.Close
conSQL.Close
Set rsSearch = Nothing
Set conSQL = Nothing
End Sub

[namitao]

It is not resolved

[PilgrimPete]

Where do you get the error?

[namitao]

Me.MSFlexGrid1.Rows = rs.RecordCount

[PilgrimPete]

Then I'd guess it is this line in GetRecordset:

Set rsSearch.ActiveConnection = Nothing

You can't use a disconnected recordset with a server-side cursor. Try commenting that line out.

[namitao]

I get another error Invalid use of nulls at

Me.MSFlexGrid1.TextMatrix(iRow, iCol) = fld.Value

Please help

[PilgrimPete]

Try this:

if isnull(fld.Value) = true then
    Me.MSFlexGrid1.TextMatrix(iRow, iCol) = vbnullstring
else
    Me.MSFlexGrid1.TextMatrix(iRow, iCol) = fld.Value
end if

[namitao]

It worked, I see data in my flex grid but it gives me all the data in the table. I want it for a specific data as per the filter.
Also in the flex grid how do I get the column names

[PilgrimPete]

You missed this bit:

[color=red]
If strWhere <> vbNullString Then
    strSQL = strSQL & " WHERE " & Mid$(strWhere, 5)
End If[/color]
LoadTheGrid (strSQL)

[namitao]

Just when I thught I was done , I have another error
"Invalid column name 1234."
I get the error at
Set rsSearch = conSQL.Execute(strSQL1)

I am using 1234 as my ID number

[PilgrimPete]

Can you do a

debug.print strSQL1

and post the results?