Searching through recordsets (1 Viewer)

   Dim schID As Integer
   Dim Schools As DAO.Database
   Dim sch As DAO.Recordset
   Set Schools = CurrentDb
   Set sch = Schools.OpenRecordset("tblSchool")
   
   With sch
  .FindFirst "SchoolID = 968"
      If .NoMatch Then
           MsgBox ("No Records Found!")
           .MoveFirst
       Else
            schID = .AbsolutePosition
       End If
   End With

Function SearchAndReturn() As Long
      ' Rx_ Base template to search for value in field and return the primary key (autocounter)
      ' in the Immediate window tyhpe     Debug.Print SearchAndReturn()
      ' if it returns -1   something went horribly wrong,  0  no records found, or it returns the primary key
      Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
      Dim Result As Long
      ' add an argument to the function to pass in the string name and substitute it for the 'Hoss 25-32'
        On Error Resume Next
10    strSQL = "SELECT Wells.ID_Wells, Wells.Well_Name FROM Wells WHERE (((Wells.Well_Name)='Hoss 25-32'));"
        ' Replace the SQL above with the table and fieldname
20    Set db = CurrentDb
30    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges) ' dbopensnapshot and dbseechanges required for tables linked to SQL Server
40    SearchAndReturn = -1 ' indicates an error, mission aborted, IM Office dis-avows your effort
50    If rs.RecordCount > 0 Then
60    rs.MoveFirst
70    Result = rs.Fields(0) 'this means the first field returned in this case the primary key, you can also use rs.fields("name") to get the same result.
80    SearchAndReturn = Result
90    Else
      ' Insert Well Not Found subroutine or messagebox  here
100   SearchAndReturn = 0  ' indicates no record found function returns 0
110   End If
115  If Err.Number <> 0 then SearchAndReturn = -1
      ' Clean Up this mess to make it look like we were never here and recycle the variables memory space
120   Set rs = Nothing
130   Set db = Nothing
End Function

'this recordset searches through a table finds a record in a feild that matches a variable then gets it ID number and saves it to a variable
   Dim strSN As String
   strSN = Me.SchoolName.Value
   'strSN = "Aldro"
   Dim schID As Integer
   Dim Schools As DAO.Database
   Dim sch As DAO.Recordset
   Set Schools = CurrentDb
   Set sch = Schools.OpenRecordset("tblSchool", dbOpenDynaset)
   
   With sch
  .FindFirst "SchoolName = """ & strSN & """ "
      If .NoMatch Then
           MsgBox ("No Records Found!")
           .MoveFirst
       Else
            schID = sch!SchoolID 'this gets information from the ID field from record found
End With