I have this piece of code that does

[EdRev]

I have this piece of code that doesn't quite work for me.

Adodc1.recordset.requery 'for when i need to do another search

Adodc1.Recordset.Find "File_No = '" & srchCriteria & "'"
cmdUpdate.Visible = True
cmdDelete.Visible = True
cmdCancel.Visible = True
cmdSearch.Visible = True
cmdReturn.Visible = True

If Adodc1.Recordset.EOF Then
MsgBox ("No record exist for " & srchCriteria)
End If

The code will find an exact match based on the criteria. But somehow, if it doesn't find a match it displays the first record of the table.

any help will be greatly appreciated.

 

[MikeBronner]

I haven't tried this myself, but maybe this will work:

If Adodc1.Recordset.EOF Then
MsgBox ("No record exist for " & srchCriteria)
Adodc1.Recordset = Nothing
End If

This way, if nothing is found in the recordset, it will flush it and won't display a record. You might want to consider copying the content of the origiinal record set to a second one, so that you won't change the values in your database.

Hope this helps,
Good luck!

Michael Bronner

 

[EdRev]

thanks michael,

i tried it,
set adodc1.recordset=nothing

but still give me the first record of the database.

 

[sparrow]

What about

If Adodc1.Recordset.NOMATCH Then

This is checing for a matching record rather than end of file


Paul

 

[EdRev]

thanks Paul,

i tried it but ADO Recordset doesn't support the NoMatch method.

Please feel free to toss in any more ideas. I'm kinda stuck right now.

 

[MikeBronner]

Hmm, I had an idea:

If (Adodc1.RecordCount = 0)Then
MsgBox ("No record exist for " & srchCriteria)
Adodc1.visible = false
End If

would that work for you?

 

[TimLarkin]

You are requerying then searching the record-set. If a record in the record-set doesn't match the criteria, your record-set record will remain the record at which you used the 'Find' method(since you use requery, that will be the first record). Instead, query the DB for a record-set with the criteria, and use .eof/.bof or record count to establish if you have a match.