SEARCH A RECORDSET FOR A SPECIFIC VALUE 2

[PrgrmsAll]

I have two recordsets open in Access. I want to cycle through the IDs in the first rs, and trying to determine if they exist in the second rs. Is there a function for doing a search or will i have to just cycle through the records in the second rs, too?

This is what I currently have:


Set rsMAIN = db.OpenRecordset("SELECT * FROM MAIN")
Set rsLOOKUP = db.OpenRecordset("SELECT * FROM LOOKUP")

While Not rsMAIN.EOF
Debug.Print rsMAIN.Fields("ID")
' // This is where I would do a lookup/search
' // on rsLOOKUP for rsMAIN.ID
rsMAIN.MoveNext
Wend


Thank you in advance!

 

[MajP]

Can't you just build a query joining Main and Lookup, to return matches?

 

[PrgrmsAll]

Not really because, I have update, append, delete actions to do to a record depending on whether it is found or not.

 

[MajP]

I mean use a query to return the matched records, then you could loop through just that set.

if not maybe something

dim id as integer
dim strCriteria as string
do while not rsMain.eof
id = rsMain.fields("yourIDfield")
strCriteria = "LookupIDField = " & Id
if not nz(dlookup("lookupIDField","lookup",strCriteria)) then
do something
end if
rsmain.movenext
loop

 

[PHV]

Have a look at the FindFirst method of the DAO.Recordset object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PrgrmsAll]

I think the FindFirst method will work, thanks both of you for your help.