FindFirst Search does not always work?

[kayek]

I am using the following code in a form to search for last name. Most of the time the code works just fine and it finds the person I want. But some of the time it does not. For example, I search for Last Name “Swift”. The code will bring me to last name “BAGGETT”. No where in the “BAGGETT” record is there anything close to the name “Swift”. I can’t figure out why the find brings me to that record.

Dim Criteria As String
Dim SearchName As String ' The name to search for.

SearchName = Me.txtNameSearch.Text

Criteria = "[L_Name1] like '" & SearchName & "*'"
Me.RecordsetClone.FindFirst Criteria
Me.Bookmark = Me.RecordsetClone.Bookmark

If I use FindLast instead of FindFirst it works but it brings me to the last record that fits that criteria and I would like to be at the first.

Any help?

 

[RoyVidar]

It could be the fact that you're using the .Text property of the control, in stead of the .Value property (check it out in the help files), try

[tt] SearchName = Me.txtNameSearch.Value ' or
SearchName = Me.txtNameSearch ' since the value property is default[/tt]

I'd perhaps also include some testing for Null?

Roy-Vidar

 

[kayek]

Using .Value instead of .text did not help.

 

[kayek]

I do notice a pattern. The code works when searching for names starting with a thru st. If I search for anything su or greater in the alphabet this is when I am having the problem.

My RecordSet is a query where it is in order by L_NAME1

 

[RoyVidar]

You could try not assigning the bookmark if no record is found, i e (there's sure some way of getting it out of the clone too, but I'm more used to assigning to a recordset)

[tt] dim rs as dao.recordset
set rs = Me.RecordsetClone
rs.findfirst "[L_Name1] like '" & me!txtNameSearch.value & "*'"
if (not rs.nomatch) then
me.bookmark = rs.bookmark
end if
set rs = nothing[/tt]

Roy-Vidar

 

[kayek]

There are 30267 records in my recordset. The search can find any records 1 thru 25600 but if I search for any records that are record number 25601 thru 30267 it does not bring me to the correct record.

 

[RoyVidar]

Dunno - I don't use DAO much - could issuing a .movelast prior to the .findfirst be something to try? Else just hope someone who has experienced this jumps in - if it's a known problem, it should be searchable, either here, or through your favourite web search engine.

Roy-Vidar

 

[kayek]

You are exactly right. I have ran across this before and forgot.

I added a .movelast and .movefirst just before the .findfirst and it now works.

Thanks so much!
Kaye

Below is my new working code.

dim rs as dao.recordset
set rs = Me.RecordsetClone
rs.MoveLast
rs.MoveFirst
rs.findfirst "[L_Name1] like '" & me!txtNameSearch.value & "*'"

if (not rs.nomatch) then
me.bookmark = rs.bookmark
end if
set rs = nothing

 

[PHV]

I don't think the MoveFirst is necessary.

 

[TheAceMan1]

How are ya kayek . . . . .

I've has similar problems in my history trying to use recordsetclone direct with a large data set. If fact the same problem occurs when opening a form with a large data set and recordsetclone is used. I can't say if this will help, but its a step in the right direction ([purple]time needs to be allowed for a large recordset to load!)[/purple]):

[blue]   dim rs as dao.recordset
   
   set rs = Me.RecordsetClone
   
   [purple][b]rst.MoveLast
   DoEvents     'Allow rs to fully load![/b][/purple]

   rs.findfirst "[L_Name1] like '" & me!txtNameSearch.value & "*'"
   
   if (not rs.nomatch) then    
      me.bookmark = rs.bookmark
   end if

   set rs = nothing[/blue]

See Ya! . . . . . .