Solved

RECORDSET FIND WITH 2 FIELDS

Posted on 2001-08-09
3
808 Views
Last Modified: 2008-02-01
Does anyone know the syntax to using the find method with two fields in the criteria. This is what I currently have but it does not like the syntax:
rsAppliedPayment.Find "PaymentID = " & TempPaymentId & ",Invoice = " & rs!INVOICE, , adSearchForward

I also tried this but it did not work
rsAppliedPayment.Find "PaymentID = " & TempPaymentId & " AND Invoice = " & rs!INVOICE, , adSearchForward
0
Comment
Question by:mp_lynch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 2

Expert Comment

by:hotbudare
ID: 6370967
The second sintax is the right one, the problem might be that one of the fields type is a string or a date. If that's the case you should enclose them properly.
I always prefer to use the BuildCriteria() function, as in:

    strCriteria = "(" & BuildCriteria("[PaymentID]", dbLong, CStr(TempPaymentId))
    strCriteria = strCriteria & ") AND ("
    strCriteria = strCriteria & BuildCriteria("[Invoice]", dbLong, CStr(Nz(TempPaymentId, "")))
    strCriteria = strCriteria & ")"
    rsAppliedPayment.Find strCriteria, , adSearchForward

HTH/EQTA
T.S.U. Mario Osorio
Punto Fijo, Falcon, Venezuela
0
 
LVL 11

Accepted Solution

by:
joekendall earned 25 total points
ID: 6371121
I created a command button on a form (cmdFind). I then had it open a recordset. Then, you can clone this recordset and filter the cloned recordset to a criteria string. Finally, you can bookmark your first recordset and close the cloned recordset. It then gives a message box with confirmation that it found the record.

You just need to change the names of the Recordset, Fields, Database, etc. to your names for the objects.

Thanks!

Joe


Private Sub cmdFind_Click()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sCriteria As String
   
    On Error GoTo ErrHandler
   
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
   
    cn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\mydocu~1\code.mdb"
    rs.CursorLocation = adUseClient
    rs.Open "tblReports", cn, adOpenDynamic, adLockOptimistic
   
    sCriteria = "[Report] = '" & txtReport & "' AND [User] = '" & txtUser & "'"
   
    Call MultiFind(rs, sCriteria)
    MsgBox rs.Fields("Report") & " " & rs.Fields("User")
   
    rs.Close
    cn.Close
   
ExitErrHandler:
    Set rs = Nothing
    Set cn = Nothing
   
    Exit Sub
   
ErrHandler:
    With Err
        MsgBox .Number & vbCrLf & .Description & vbCrLf & .Source
    End With
    Resume ExitErrHandler
   
End Sub

Private Sub MultiFind(ByRef oRS As ADODB.Recordset, sCriteria As String)
    Dim rsClone As ADODB.Recordset
   
    On Error GoTo ErrHandler
   
    Set rsClone = oRS.Clone
    rsClone.Filter = sCriteria
    If rsClone.EOF Or rsClone.BOF Then
        oRS.MoveLast
        oRS.MoveNext
    Else
        oRS.Bookmark = rsClone.Bookmark
    End If
   
    rsClone.Close
   
ExitErrHandler:
    Set rsClone = Nothing
   
    Exit Sub
   
ErrHandler:
    With Err
        MsgBox .Number & vbCrLf & .Description & vbCrLf & .Source
    End With
    Resume ExitErrHandler
   
End Sub
0
 

Author Comment

by:mp_lynch
ID: 6372971
Perfect thanks alot man.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question