Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

RECORDSET FIND WITH 2 FIELDS

Posted on 2001-08-09
3
Medium Priority
?
847 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 100 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

618 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