Search functionality problem

[rundmc]

Hi,
Im doing a search on an Access table and the following code works in retrieving records that exist in the table however it throws up an error when the record is not found. Has anyone got any idea how to overcome this, it would bemuch appreciated
Thanks



Private Sub CmdSearch_Click()
MSFlexGrid1.FormatString = &quot; <|PC ID |<PC Vendor |<PC Make/Model |<Memory |<Operating System |<Location |< Business Dept ID&quot;


Dim adoconnection As ADODB.Connection
Set adoconnection = New ADODB.Connection

adoconnection.Open (&quot;Provider=Microsoft.jet.oledb.4.0;&quot; & &quot;Data Source =H:\AssetTracking.mdb&quot


MSFlexGrid1.Visible = True

Set rs = New ADODB.Recordset




rs.Open &quot;select * from PC where PCMakeModel ='&quot; & TxtMakeModel.Text & &quot;' and BusinessDeptID ='&quot; & TxtBU.Text & &quot;'&quot;, adoconnection, adOpenDynamic, adLockOptimistic





Dim irow As Integer

With MSFlexGrid1


Rows = 1 'counter used to assign the row number
rs.MoveFirst
While Not rs.EOF
Rows = Rows + 1
rs.MoveNext
Wend
.Rows = Rows
.Cols = 8

irow = 1 'counter used to assign the row number

rs.MoveFirst

Do Until rs.EOF
.TextMatrix(irow, 1) = rs!PCID
.TextMatrix(irow, 2) = rs!PCVendor
.TextMatrix(irow, 3) = rs!PCMakeModel
.TextMatrix(irow, 4) = rs!Memory
.TextMatrix(irow, 5) = rs!OperatingSystem
.TextMatrix(irow, 6) = rs!Location
.TextMatrix(irow, 7) = rs!BusinessDeptID
rs.MoveNext
irow = irow + 1

Loop
rs.Close

Set rs = Nothing

End With




End Sub

 

[CajunCenturion]

Two things - the error is caused because, based on the code that it shown, you're doing a .MoveFirst before checking the .EOF and .BOF properties of the recordset.

Secondly, you could improve upon the process by only making one pass thru the recordset.

rs.Open &quot;select * from PC where PCMakeModel ='&quot; & TxtMakeModel.Text & &quot;' and BusinessDeptID ='&quot; & TxtBU.Text & &quot;'&quot;, adoconnection, adOpenDynamic, adLockOptimistic

Dim irow As Integer

With MSFlexGrid1
.Rows = .FixedRows
If ((Not rs.EOF) Or (Not rs.EOF)) Then
rs.MoveFirst
Do While (Not rs.EOF)
irow = .rows - 1
.TextMatrix(irow, 1) = rs!PCID
.TextMatrix(irow, 2) = rs!PCVendor
.TextMatrix(irow, 3) = rs!PCMakeModel
.TextMatrix(irow, 4) = rs!Memory
.TextMatrix(irow, 5) = rs!OperatingSystem
.TextMatrix(irow, 6) = rs!Location
.TextMatrix(irow, 7) = rs!BusinessDeptID
.rows = .rows = 1
rs.MoveNext
Loop
If (.Rows > .FixedRows) Then
.Rows = .Rows - 1
End If
End If
End With Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein