Solved

RECORDSET FIND WITH 2 FIELDS

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

821 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