hi..

I want to find a record in a recordset and after that, I want to retrieve data from the previous record of the found record. But when I reached to the .find sTmp, I got this error

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. 3001

here is the code I used..


    cnn.CursorLocation = 3
    cnn.Open "Provider=SQLOLEDB.1;**************"
    sTmp = Trim(frmCustomer.txtCustomerCode.Text)

    sql = "SELECT * FROM Customer Where CustomerCode='" & sTmp & "'"
    rstCust.Open sql, cnn, adOpenStatic, adLockOptimistic

    If rstCust.RecordCount <> 0 Then
        rstCust1.Open "Customer", cnn, adOpenStatic, adLockOptimistic
        rstCust1.Find sTmp
        rstCust1.MovePrevious
    End if


The way of using .Find is ok with DAO. But I can't use it here. Is there any wrong with my code or whatelse I need to do.. Is there other way to point out the required record and go to the previous and next record of it.

Tks alot for any help and reply..

Hi phi
There is a problem in your query. Your query is retrieving only
one record. So moveprevious method is raising an erro.

Try this one

sql = "SELECT * FROM Customer"
rstCust.Open sql, cnn, adOpenStatic, adLockOptimistic

do while not rsCust.EOF
    if rsCust!CustomerCode= '" & sTmp & "'"
        exit DO
    end if
rsCust.moveNext
loop
rsCust.movePrevious

Hi phi kyo,

you don't need to select and then use find method to locate a record in the database or loop through the whole recordset. There are more efficient methods in ADO. Write a sql statement directly to locate the record in database. include the input customer code in the WHERE clause.


Private Sub Command1_Click()

Dim dbConnection As New ADODB.Connection
With dbConnection
    .CursorLocation = adUseClient
    .ConnectionString = "Provider=SQLOLEDB.1;************"
    .Open
End With

Dim rsCustomer As New ADODB.Recordset
With rsCustomer
    .Open "SELECT * FROM Customers WHERE CustomerCode='" & txtCustomerCode.Text & "'", dbConnection, adOpenForwardOnly, adLockReadOnly
End With

If rsCustomer.EOF = False Then
    ' Record found
    MsgBox "Record found", vbInformation
Else
    ' Record not found
    MsgBox "Record not found", vbCritical
End If

rsCustomer.Close
Set rsCustomer = Nothing

dbConnection.Close
Set dbConnection = Nothing

End Sub

Please let us no if you need more help.
   nalla




nalaka hewage