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.
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] & "'"
strSQL3 = rst("MICARID")
Forms![MICAR Review Form]![MICARID] = strSQL3
Set rst = Nothing