race condition or?

[Roland Alden]

I have a sequence of code in a click event handler as follows:


stDocName = "ed-person"
If CurrentProject.AllForms(stDocName).IsLoaded = False Then
DoCmd.OpenForm stDocName, acNormal, , , acFormEdit, acWindowNormal
End If

[Form_ed-person].FindUid (q)


FindUid takes a query string of the form "uid={someguid}" and does a

Me.Recordset.MoveFirst
Me.Recordset.Find q

Here's the situation.

If the form ed-person is not loaded it loads fine and displays the first
record; however the FindUid then executes but the form does not scroll to
the record specified by q.

If the form ed-person is loaded already it scrolls to the correct record as
per q.

I've made sure FindUid is not somehow getting bad data the first time
through and it's not. There is something about the execution of the
Me.Recordset.Find that silently fails if it is called in the same thread as
the instantiation of Me (the ed-person form)..

Is there something I need to do between the OpenForm and the called to
FindUid to allow the newly opened form to get ready for handling the FindUid
correctly?

 

[Albert D. Kallal]

[Form_ed-person].FindUid (q)

The above is the wrong syntax to reference a form. In fact, if you use the
above, and the form is NOT loaded, the above actually causes the form to
load, and also causes the events of the form to run. Further more, the form
is not even added to the forms collection when you do the above. So, DO NOT
as a habit reference the forms object directly as a above, as it gives you
no control as to what instance of the form you are referencing (and not to
mention a surprises in terms of the open+load events running).

You should use:

forms!ed-person.FindUid

Or

forms("ed-persion").FindUid....

If the form ed-person is not loaded it loads fine and displays the first
record; however the FindUid then executes but the form does not scroll to
the record specified by q.

You have to be careful, since if multiple copies of the form is loaded, or
perhaps it is used in a sub-form (elsewhere), then as mentioned, your syntax
for referencing the form should not be used.

In fact, there is little, if any cases were you want to reference the form
object directly.

 

[Roland Alden]

Got it. I was confused about all the different syntax to reach the same
object and I now I realize I'm even more confused because they are not the
same

However, improving my syntax has not made my fundamental problem go away.

 

[Albert D. Kallal]

Got it. I was confused about all the different syntax to reach the same
object and I now I realize I'm even more confused because they are not the
same

However, improving my syntax has not made my fundamental problem go away.

stDocName = "ed-person"
If CurrentProject.AllForms(stDocName).IsLoaded = False Then
DoCmd.OpenForm stDocName, acNormal, , , acFormEdit, acWindowNormal
End If

forms(stDocName).FindUid (q)

You are saying the above does not work?

Do the follwing:

msgbox q
Me.Recordset.MoveFirst
Me.Recordset.Find q


You could add a msgbox statement as a above to see what you are passing
here.

 

[Roland Alden]

It "works" in the sense that the form is displayed by OpenForm and the right
value is passed to FindUid (I'm looking with breakpoints in the debugger
rather than message boxes but as far as I can see on the first pass the
correct values are passed, as well as on subsequent calls after the form is
loaded).

But it fails in the sense that on the first run, when the form really does
have to be loaded, the Me.Recordset.Find either fails or perhaps the newly
loaded form fails to refresh its display. Perhaps I will inspect the me.
recordset after the call to Find but before the return.

 

[Roland Alden]

If I insert a breakpoint at Me.Recordset.Find q then everything works. The
form in a not loaded state; I click and run this code which displays the
form (at record #1), I then hit the Me.Recordset.Find breakpoint and step
over it, watching the recordset move from record 1 to some other record (the
correct one); and when I hit "Run" the form will be displaying the correct
record.

If I clear the breakpoint and run at top speed the form will display, but it
will show record 1, NOT the correct record. With the breakpoint ON it works
every time; with the breakpoint OFF it fails every time the form must be
loaded.

My guess is that switching to the Visual Basic window when the breakpoint is
hit also causes some other event queue flushing that allows the form to
repaint itself upon movement of the recordset stimulated by Recordset.Find.
When there is no such break between the OpenForm and the Recordset.Find
then, even though the recordset does move to the correct record (as seen in
the watch window), the visual form on the screen does NOT get updated.

Anyway, I'm back to my original question; is this behavior "by design" or am
I doing something wrong. Or perhaps both

 

[Dirk Goldgar]

If I insert a breakpoint at Me.Recordset.Find q then everything
works. The form in a not loaded state; I click and run this code
which displays the form (at record #1), I then hit the
Me.Recordset.Find breakpoint and step over it, watching the recordset
move from record 1 to some other record (the correct one); and when I
hit "Run" the form will be displaying the correct record.

If I clear the breakpoint and run at top speed the form will display,
but it will show record 1, NOT the correct record. With the
breakpoint ON it works every time; with the breakpoint OFF it fails
every time the form must be loaded.

My guess is that switching to the Visual Basic window when the
breakpoint is hit also causes some other event queue flushing that
allows the form to repaint itself upon movement of the recordset
stimulated by Recordset.Find. When there is no such break between the
OpenForm and the Recordset.Find then, even though the recordset does
move to the correct record (as seen in the watch window), the visual
form on the screen does NOT get updated.

Anyway, I'm back to my original question; is this behavior "by
design" or am I doing something wrong. Or perhaps both

It sounds to me like the form's Recordset hasn't been fully loaded at
time you call the function. That operation takes place in the
background, so pretty much anything you do that inserts a delay before
you call the recordset's Find method allows that operation to complete.
Is this form in an MDB or an ADP (and hence, is its Recordset a DAO
recordset or an ADO recordset)?

What happens if you change your FindUid function to use the form's
RecordsetClone instead of its Recordset, and then use the form and
recordsetclone's Bookmark properties to synchronize the recordsets?

 

[Roland Alden]

It sounds to me like the form's Recordset hasn't been fully loaded at

time you call the function. That operation takes place in the
background, so pretty much anything you do that inserts a delay before
you call the recordset's Find method allows that operation to complete.
Is this form in an MDB or an ADP (and hence, is its Recordset a DAO
recordset or an ADO recordset)?

It's an ADP. The above makes sense. However I'm not following the logic of:

What happens if you change your FindUid function to use the form's
RecordsetClone instead of its Recordset, and then use the form and
recordsetclone's Bookmark properties to synchronize the recordsets?

but perhaps it is because I don't understand entirely what Recordset Clones
are all about. It would seem that if I enter FindUid "too early" and the
underlying recordset is not fully populated (I gather a recordset is a kind
of snapshot/cache of the underlying server's data?) then presumably my
"clone" would be under-populated as well and an effort to do a Find it would
fail too. No?

 

[Dirk Goldgar]

It's an ADP. The above makes sense. However I'm not following the
logic of:


but perhaps it is because I don't understand entirely what Recordset
Clones are all about. It would seem that if I enter FindUid "too
early" and the underlying recordset is not fully populated (I gather
a recordset is a kind of snapshot/cache of the underlying server's
data?) then presumably my "clone" would be under-populated as well
and an effort to do a Find it would fail too. No?

I'm not experienced with ADPs, and have only used ADO a little bit, so
I'm just guessing at possible solutions here. But it has been my
experience (working with MDB files) that working with a form's recordset
directly using the Recordset property is often trickier than working
with the RecordsetClone property. The Recordset property hasn't been
exposed to developers for as long, and it definitely seems to have some
timing quirks. That's why I suggested using the form's RecordsetClone
instead.

Now, I'm not sure, in an ADP, whether the RecordsetClone property is an
ADO Recordset or a DAO Recordset. I'd expect an ADO Recordset. But I
also note that the code generated by the wizards (in Access 2000 and
later) for finding records on forms uses the Recordset.Clone method,
rather than the RecordsetClone property. So I there may be a difference
between the two, and I'm not equipped at the moment to test them out. I
would suggest trying the following distinct versions of your FindUid
routine:

'----- begin version 1 -----
Public Sub FindUid(q As String)

With Me.RecordsetClone
.MoveFirst
.Find q
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
End With

End Sub
'----- end version 1 -----

'----- begin version 2 -----
Public Sub FindUid(q As String)

Dim rs As Object

Set rs = Me.Recordset.Clone
With rs
.Find q
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
End With
Set rs = Nothing

End Sub
'----- end version 2 -----

And here's another one, similar to what you're already doing, just to
see if moving to the last record first helps force the form's recordset
to be fully populated:

'----- begin version 3 -----
Public Sub FindUid(q As String)

With Me.Recordset
.MoveLast
.Find q, 0, adSearchBackward
End With

End Sub
'----- end version 3 -----

If none of those works, you could try shifting the burden to the form
itself -- passing it an OpenArgs parameter that tells it to search for
the value q, and then letting the form execute the search in its Load
(not Open) event. By forcing the code to wait for the Load event, you
might eliminate the timing problem.

 

[Roland Alden]

fabulous tips and background; thanks very much. I will try and few out and
let you know how it turns out.

 

[Roland Alden]

I tried them all and all of them "work" and give exactly the same (wrong)
result. I was especially shocked when the On Load handler didn't do the
trick. As usual, crawling through the code with the debugger causes
everything to work. However, in doing so I did observe this bit of
information:

This is the key piece of code in my FindUID function that is called by the
code that opens the form, or called by the OnLoad handler as the case may be
(makes no difference as far as I can tell).

With Me.RecordsetClone
.MoveFirst
.find q
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
End With

Now what is interesting is if I set breakpoints at point [A] and piont
as in:

With Me.RecordsetClone
.MoveFirst
[A] .find q
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
End With

and then I hit [A] and step through the code I will hit everytime.
However, if I clear [A] and leave a breakpoint on then, when the form is
being opened for the first time, I will NOT hit breakpoint . I.e.,
somehow find q must fail; EOF must be true (makes sense right?) and
therefore does not get executed.

This suggests that the theory that the recordset is not fully populated yet
is sound (the data is coming from an SQL Server although it is on the same
machine) and furthermore it would seem that using recordset "clones" makes
no difference, nor does the trick of waiting for On Load.

Is there some form of "wait until other things are finished" call? That's a
common hack for this sort of thing Or, is the a property of recordsets
that turns true when they thnk they are coherent?

 

[Dirk Goldgar]

I tried them all and all of them "work" and give exactly the same
(wrong) result. I was especially shocked when the On Load handler
didn't do the trick. As usual, crawling through the code with the
debugger causes everything to work. However, in doing so I did
observe this bit of information:

This is the key piece of code in my FindUID function that is called
by the code that opens the form, or called by the OnLoad handler as
the case may be (makes no difference as far as I can tell).

With Me.RecordsetClone
.MoveFirst
.find q
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
End With

Now what is interesting is if I set breakpoints at point [A] and
piont as in:

With Me.RecordsetClone
.MoveFirst
[A] .find q
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
End With

and then I hit [A] and step through the code I will hit everytime.
However, if I clear [A] and leave a breakpoint on then, when the
form is being opened for the first time, I will NOT hit breakpoint
. I.e., somehow find q must fail; EOF must be true (makes sense
right?) and therefore does not get executed.

This suggests that the theory that the recordset is not fully
populated yet is sound (the data is coming from an SQL Server
although it is on the same machine) and furthermore it would seem
that using recordset "clones" makes no difference, nor does the trick
of waiting for On Load.

Is there some form of "wait until other things are finished" call?
That's a common hack for this sort of thing Or, is the a property
of recordsets that turns true when they thnk they are coherent?

I've been looking into this and learning a lot of interesting things
about ADPs and ADO recordsets. Nothing I say now should be taken as
authoritative; it just reflects my conclusions from poking around.

The rows of an ADO recordset are normally fetched from the server
asynchronously. An intial few records, determined by the property
Initial Fetch Size, are brought back when the recordset is opened, and
the remaining records are loaded up in the background. The recordset's
State property during this period has its "fetching" bit set, as well as
its "open" bit -- the constants are defined by the ObjectStateEnum
enumerated type as adStateFetching and adStateOpen. When all the
records have been fetched, the recordset's FetchComplete event fires.

The FetchComplete event is not available directly as an event of an
Access form, but it is possible to define a recordset object, set it to
the form's recordset, and sink its events in your form's module. So
you could, in principle, use the recordset's FetchComplete event to run
your find logic. However, before we go that far, let's see if this
simple, brute-force modification to your FindUid routine works:

'----- start of code -----
Public Sub FindUid(q As String)

With Me.Recordset
Do While (.State And adStateFetching)
DoEvents
Loop
.MoveFirst
.Find q
End With

End Sub
'----- end of code -----