Searching through recordsets

[sam1fletcher]

I am trying to find a certain record in my record set then grab is ID number and assign it to a variable.

when i set up my record set i get an error "Operation is not supported for this typ of object"

Code:

   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

Any Ideas??

 

[Rx_]

Seem to remember that the quotes make a difference on the FindFirst


rs.FindFirst "City = ""Denver """
If Not rs.NoMatch Then
'keep processing.
End If

 

[sam1fletcher]

cheers RX they did make a difference do you know any way of when i find a record to grab its ID number which is a different feild of the table

 

[Rx_]

Honestly, I hate using that method in the first place.
Could you consider using a function like this?
I keep this as a template and modify it to suit each need.
For example: Add a string argument to pass into the function to complete the query. Or replace the name of the well with a reference to a form's text box.

My preference is to use a function to get a result.
Check it out and tell me what you think?

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

If you like this, a Thanks is always appreciated

 

[sam1fletcher]

Cheers RX for the function looks like a good method but looks like it just searches for a particalar value after a condition.

if you take my example i have a table School which has 2 feilds schoolID and schoolName i have managed to find the school by name then i want to be able to get the schoolID value from the result?? i have used a recordset.findfirst method to do this any ideas??

sam

 

[Rx_]

"SELECT Wells.ID_Wells, Wells.Well_Name FROM Wells WHERE (((Wells.Well_Name)='Hoss 25-32'));"
Exactly the same result.
If you open up a new query and drop your table in - add the two fields then put a value in the criteria - you can substitute your SQL statement for mine.

After that works, change the SQL to be:
=' & Me.TextBoxSchoolname & '));"
Now the value in your search text box will be substituted for the SQL criteria.
The function will return primary key ID (assuming there is an autocounter)
Then move your recordset to that Primary ID

 

[Rx_]

Try this - in stead of the absolute position

SchoolID = rs!SchoolID

 

[sam1fletcher]

RX what a legend that worked thanks. could you tell me the exact code to use a variable in the firndfirst methos. i got this but doesnt seem to work .findfirst "SchoolName =" & strNM

 

[sam1fletcher]

cracked it cheers rx

 

[sam1fletcher]

Anyone who wants the code

'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