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
Solved

RECORDSET FIND WITH 2 FIELDS

Posted on 2001-08-09
3
781 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
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
corrupt Databases 9 62
Problem with vba code 4 36
how to insert parameter value in table 2 16
Access Schema 6 21
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

790 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