none
Finding record in record set

    Question

  • I need to fix this code to use the record set to determine if their is a related record and if so post the referenced Item to the form.  The search is opening the recordset but if the strSQL2 string is not found for the first strSQL it will find the first item in the Recordset that matches the strSLQ2 parameters eventhough the related record doesn't match the strSQL parameters.

    For example, I have a table with these records.  If my UnitID is Cerritos, the code finds that strSQL parameter.  However, when I then look for the strSQL2 parameter which is set to "Jul/2013", the code looks down the Month/Year column and finds the next record that matches the code and presents the "MICARID" of 163 as the strSQL3 answer.  Instead, it should stop and not give me a match.  I suppose I also need to change the code to report back to the use that no record was found.

    <tfoot></tfoot>
    MICAR Record
    MICARID UnitID Month/Year
    7 Cerritos Jul/2012
    8 Cerritos Aug/2012
    9 Cerritos Sep/2012
    10 Cerritos Oct/2012
    11 Cerritos Nov/2012
    12 Cerritos Dec/2012
    13 Cerritos Jan/2013
    14 Cerritos Feb/2013
    15 Cerritos Mar/2013
    16 Cerritos Apr/2013
    17 Cerritos May/2013
    18 Cerritos Jun/2013
    163 Valencia Jul/2013
    164 Valencia Aug/2013
    165 Valencia Sep/2013
    166 Valencia Oct/2013

        Dim rst As ADODB.Recordset
        Dim strSQL As String
        Dim strSQL2 As String
        Dim strSQL3 As String
       
        Set rst = New ADODB.Recordset
        rst.Open "[MICAR Record]", CurrentProject.Connection, adOpenDynamic, , adCmdTable
        strSQL = "[UnitID]=" & Forms![MICAR Review Form]![UnitID]
        strSQL2 = "[Month/Year]='" & Left(Forms![MICAR Review Form]![CurrentMonth], 3) & "/" & Forms![MICAR Review Form]![CurrentYear] & "'"
        With rst
            .Find strSQL
            .Find strSQL2
            strSQL3 = rst("MICARID")
            Forms![MICAR Review Form]![MICARID] = strSQL3
        End With
       
            rst.Close
            Set rst = Nothing


    Chris Premo

    Friday, July 19, 2013 2:53 PM

Answers

  • I decided to go another angle.  But did find that filtering the rst was the answer.

    Chris Premo

    • Marked as answer by ChrisPremo Monday, July 22, 2013 9:25 PM
    Monday, July 22, 2013 9:25 PM

All replies

  • I suppose what I need to do is filter the Recordset to only those records that match the strSQL parameter.  How do I do that?

    Chris Premo

    Friday, July 19, 2013 2:55 PM
  • Hi,

    Just confirmation, when the result is 163, then 163 will be a parameter for which column to search? Then it will be no record found?


    Jaynet Zhang
    TechNet Community Support

    Monday, July 22, 2013 8:41 AM
  • I decided to go another angle.  But did find that filtering the rst was the answer.

    Chris Premo

    • Marked as answer by ChrisPremo Monday, July 22, 2013 9:25 PM
    Monday, July 22, 2013 9:25 PM