FindFirst in recordset where Set recordset is used

[Miles R]

I'm trying to debug some code where FindFirst matching on a name is not getting the correct record.
I'm a bit rusty on my record sets and before I can continue debugging, I'm puzzled by the following code

Set rstBirdRecord = [Form_Bird List].Recordset
rstBirdRecord.FindFirst "BirdName = """ & strBirdName & """"

What is puzzling is why the FindFirst triggers the Current Event of the form [Form_Bird List]
I am sure I must have understood this when I wrote the code years ago, but can't figure it out now. Surely it is finding the record in rstBirdRecord and not [Form_Bird List].Recordset
Or is rstBirdRecord just an alias?

[June7]

What event is this code in?

I just tested this code and it is going to matched record on form. This will trigger Current event.
If there is no match then focus just goes to first record on form.

[Minty]

If you are on the form concerned use the RecordsetClone for searches then set the forms bookmark to match the recordset bookmark if found, not the forms actual recordset.

[Miles R]

Thanks for the replies.
The event is on another form and I do actually want to trigger the Current Event in the Bird list form.
I was just puzzled by whether Set rstBirdRecord = [Form_Bird List].Recordset creates an alias for the original recordset. I'm assuming it does.

Anyway, I have solved the original bug by doing a Requery on the recordset. It seems to have been confused when the record source of the Bird List form was recreated.

[moke123]

A findfirst procedure in the form would look like this

Dim rs as DAO.Recordset
Set rs = Me.recordsetClone

     with rs
         .findFirst "BirdName = """ & strBirdName & """"

     if not .NoMatch then
          Me.bookmark = .bookmark
     else
         'A messagebox if preferred
      end if

     end with

edit: If on another form or subform then you would set the the recordset = YourformReference.RecordsetClone and the bookmark as YourformReference.bookmark = .bookmark

[Miles R]

Thanks for the reply.
Don't think I need a clone as I am working with the original recordset.
I was just trying to figure out if Set rstBirdRecord = [Form_Bird List].Recordset means that they are equivalent. No one has answered that yet.
I think they must be, because if I replace
rstBirdRecord with [Form_Bird List] in the FindFirst line, I get the same result - although there is no Intellisense with [Form_Bird List] but there is with rstBirdRecord

[Minty]

The point of using the RecordsetClone is that you don't mess with the forms recordset unnecessarily, and create strange errors like
"This record has been edited by another User" etc.

As you have discovered using FindFirst will move the forms record pointer regardless of whether it does or doesn't find a record, triggering the current event amongst others.

[moke123]

Form_Bird List

Using the Form_ prefix is generally used when instantiating a form instance. set frm = New Form_SomeForm

[Welshgasman]

Thanks for the reply.
Don't think I need a clone as I am working with the original recordset.
I was just trying to figure out if Set rstBirdRecord = [Form_Bird List].Recordset means that they are equivalent. No one has answered that yet.
I think they must be, because if I replace
rstBirdRecord with [Form_Bird List] in the FindFirst line, I get the same result - although there is no Intellisense with [Form_Bird List] but there is with rstBirdRecord

I would say you do.

[Miles R]

Thanks for the replies.

I have solved the initial problem by doing a Requery of the Recordset before doing the FindFirst, although I don't know why this is needed, as changing the RecordSource with slightly different filters has already done a Requery.

If I get the time, I will dig into this deeper, but it is solved anyway.

[Miles R]

Further info on this - there is sometimes a discrepancy in values from the current row of the record set :
Me!TaxonomySequence.Value gives one number and [Form_Bird List].Recordset.Fields("TaxonomySequence") gives a different number (the correct one) unless I use Requery. (both refer to the record set of [Form_Bird List]).
Maybe its something to do with Dirty values possibly.
Not sure yet.

[Welshgasman]

Try the .Text property of the control.

[Miles R]

Tried that but Me!TaxonomySequence.Text gives an error - object does not support this property or method
Don't forget that M!TaxonomySequence is referring to the Recordset and is not a control.

[moke123]

Don't forget that M!TaxonomySequence is referring to the Recordset and is not a control.

How could we forget it if this is the first time your mentioning it?

I suspect you doing more than what you are telling us. If your adding or editing in the form it won't be in the recordset until you save it and refresh the recordset.

[Miles R]

Moke123,

Thanks for the reply and sorry to be pedantic, but I did mention it further up when I said :
Me!TaxonomySequence.Value gives one number and [Form_Bird List].Recordset.Fields("TaxonomySequence") gives a different number (the correct one) unless I use Requery. (both refer to the record set of [Form_Bird List]).

Yes, I am adjusting the recordset, but from my testing, the record set is updated and the syntax [Form_Bird List].Recordset.Fields("TaxonomySequence") gives the correct value, but Me!TaxonomySequence.Value gives another, which does not seem to be the value in the current record.