Finding a record in a recordset

[ALSav]

Hi

I need some help with finding a record in a recordset. I don't want a recordset of the found data returned, all I want to know is if it is present or not. Basically I have a recordset rsX containing details of a medical practice including a field containing the id of the senior GP (GP_id) for that practice. What I want to do is check that that GP is in listed in another recordset rsY which lists all GPs (by GP_id)associated with the practice i.e. a GP can't be the senior GP if they are not a member of the practice.

I've been using recordset.Find() but with little success


Dim id As String

id = rsX!GP_id

check = rsY.Find("GP_id = '" & id & "'", , adSearchForward)

If check = -1 Then
MsgBox ("This GP is not listed as a member of the practice&quot
End If

Any ideas?

 

[Tevesh]

I am using something like the following and it seems to work well. The only thing you have to remember is to reset the position in rsY to the begining before preforming another search (rsY.MoveFirst). I belive the reason this works is that if what you are looking for is not in the recordset the find process runs off the end and thus sets the EOF flag. I put the BOF flag in just for completeness.

Hope this helps
Jonathan


Dim id As String

id = rsX!GP_id

rsY.Find "GP_id = '" & id & "'", , adSearchForward

If rsY.EOF or rsY.BOF Then
MsgBox ("This GP is not listed as a member of the practice&quot
End If

 

[ALSav]

Thanks Jonathan
that seems to do the trick