RecordsetClone

  • Thread starter Thread starter Brett Steele
  • Start date Start date
B

Brett Steele

We have an application that uses the following code:

Dim rstClone As Recordset
Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" & Trim(UCase(strvarListDescription)) & "'"

rstClone.Find strClone

If rstClone.EOF Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
rstClone.Close

What we are seeing is that if we let this code run, it never finds a match,
even though the match exists. If we put the following code in to pause the
execution of the code:

'WE put this timer here because code was executing too fast.
'This slows the code down so SQL has enough time run it
squery
Finish = 900
PauseTime = 3 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.

the find always finds a match. What this indicates to us is that the entire
recordset is not built with the Set rstClone = Me.RecordsetClone command and
the Find executes before the recordset is actually completed. Does this
make sense to anyone? We have other areas where we use exactly the same
logic and have never had a problem. We have even tried going to the end of
the recordset before issuing the find and that doesn't work either.

Thanks in advance.
 
Hi !
Because of problems with the cloned recordset I allways
save the current record of the form, before I worked with
the cloned recordset

Application.RunCommand acCmdSaveRecord
and then
set rs = me.recordsetclone

Hope this help.
Niels
 
Hi Brett,

What happens if you just search the recordsetclone without going through a
separate recordset object? Also, you should be using the NoMatch property
since there is no guarantee that the EOF condition will be true when a match
is not found.

'Dim rstClone As Recordset
' Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" & Trim(UCase(strvarListDescription)) & "'"

with me.recordsetclone
.Find strClone
If rstClone.nomatch Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
End with
 
Thanks Sandra,

I tried this and it still does not find the match. If I put a MsgBox in
front of this code, it finds the match. If I take the MsgBox out, it does
not. It just appears that the code is executing faster than the
recordsetclone can be created?

Any other ideas?

Brett

Sandra Daigle said:
Hi Brett,

What happens if you just search the recordsetclone without going through a
separate recordset object? Also, you should be using the NoMatch property
since there is no guarantee that the EOF condition will be true when a match
is not found.

'Dim rstClone As Recordset
' Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" & Trim(UCase(strvarListDescription)) & "'"

with me.recordsetclone
.Find strClone
If rstClone.nomatch Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
End with


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brett said:
We have an application that uses the following code:

Dim rstClone As Recordset
Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" & Trim(UCase(strvarListDescription))
& "'"

rstClone.Find strClone

If rstClone.EOF Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
rstClone.Close

What we are seeing is that if we let this code run, it never finds a
match, even though the match exists. If we put the following code in
to pause the execution of the code:

'WE put this timer here because code was executing too
fast. 'This slows the code down so SQL has enough time
run it
squery
Finish = 900
PauseTime = 3 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.

the find always finds a match. What this indicates to us is that the
entire recordset is not built with the Set rstClone =
Me.RecordsetClone command and the Find executes before the recordset
is actually completed. Does this make sense to anyone? We have
other areas where we use exactly the same logic and have never had a
problem. We have even tried going to the end of the recordset before
issuing the find and that doesn't work either.

Thanks in advance.
Click to expand...
Click to expand...
 
Hi Brett,

Is this a Jet or SQL backend database? If it is Jet, try using FindFirst
instead of Find and see what happens.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brett said:
Thanks Sandra,

I tried this and it still does not find the match. If I put a MsgBox
in front of this code, it finds the match. If I take the MsgBox out,
it does not. It just appears that the code is executing faster than
the recordsetclone can be created?

Any other ideas?

Brett

Sandra Daigle said:
Hi Brett,

What happens if you just search the recordsetclone without going
through a separate recordset object? Also, you should be using the
NoMatch property since there is no guarantee that the EOF condition
will be true when a match is not found.

'Dim rstClone As Recordset
' Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" &
Trim(UCase(strvarListDescription)) & "'"

with me.recordsetclone
.Find strClone
If rstClone.nomatch Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
End with


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brett said:
We have an application that uses the following code:

Dim rstClone As Recordset
Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" &
Trim(UCase(strvarListDescription)) & "'"

rstClone.Find strClone

If rstClone.EOF Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
rstClone.Close

What we are seeing is that if we let this code run, it never finds a
match, even though the match exists. If we put the following code
in to pause the execution of the code:

'WE put this timer here because code was executing too
fast. 'This slows the code down so SQL has enough
time run it
squery
Finish = 900
PauseTime = 3 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.

the find always finds a match. What this indicates to us is that
the entire recordset is not built with the Set rstClone =
Me.RecordsetClone command and the Find executes before the recordset
is actually completed. Does this make sense to anyone? We have
other areas where we use exactly the same logic and have never had a
problem. We have even tried going to the end of the recordset
before issuing the find and that doesn't work either.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Sandra,

Sorry..yes, it is an ADP with a SQL Server backend. The Nomatch option you
sent earlier does not work as well because that is a DAO construct as
opposed to an ADO construct.

Thanks,
Brett

Sandra Daigle said:
Hi Brett,

Is this a Jet or SQL backend database? If it is Jet, try using FindFirst
instead of Find and see what happens.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brett said:
Thanks Sandra,

I tried this and it still does not find the match. If I put a MsgBox
in front of this code, it finds the match. If I take the MsgBox out,
it does not. It just appears that the code is executing faster than
the recordsetclone can be created?

Any other ideas?

Brett

Sandra Daigle said:
Hi Brett,

What happens if you just search the recordsetclone without going
through a separate recordset object? Also, you should be using the
NoMatch property since there is no guarantee that the EOF condition
will be true when a match is not found.

'Dim rstClone As Recordset
' Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" &
Trim(UCase(strvarListDescription)) & "'"

with me.recordsetclone
.Find strClone
If rstClone.nomatch Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
End with


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brett Steele wrote:
We have an application that uses the following code:

Dim rstClone As Recordset
Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" &
Trim(UCase(strvarListDescription)) & "'"

rstClone.Find strClone

If rstClone.EOF Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
rstClone.Close

What we are seeing is that if we let this code run, it never finds a
match, even though the match exists. If we put the following code
in to pause the execution of the code:

'WE put this timer here because code was executing too
fast. 'This slows the code down so SQL has enough
time run it
squery
Finish = 900
PauseTime = 3 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.

the find always finds a match. What this indicates to us is that
the entire recordset is not built with the Set rstClone =
Me.RecordsetClone command and the Find executes before the recordset
is actually completed. Does this make sense to anyone? We have
other areas where we use exactly the same logic and have never had a
problem. We have even tried going to the end of the recordset
before issuing the find and that doesn't work either.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
That's what I figured (after I posted the .NoMatch bit anyway :-)).

One other thing to try is to do a MoveFirst prior to the Find.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brett said:
Sandra,

Sorry..yes, it is an ADP with a SQL Server backend. The Nomatch
option you sent earlier does not work as well because that is a DAO
construct as opposed to an ADO construct.

Thanks,
Brett

Sandra Daigle said:
Hi Brett,

Is this a Jet or SQL backend database? If it is Jet, try using
FindFirst instead of Find and see what happens.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brett said:
Thanks Sandra,

I tried this and it still does not find the match. If I put a
MsgBox in front of this code, it finds the match. If I take the
MsgBox out, it does not. It just appears that the code is
executing faster than the recordsetclone can be created?

Any other ideas?

Brett

Hi Brett,

What happens if you just search the recordsetclone without going
through a separate recordset object? Also, you should be using the
NoMatch property since there is no guarantee that the EOF condition
will be true when a match is not found.

'Dim rstClone As Recordset
' Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" &
Trim(UCase(strvarListDescription)) & "'"

with me.recordsetclone
.Find strClone
If rstClone.nomatch Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
End with


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Brett Steele wrote:
We have an application that uses the following code:

Dim rstClone As Recordset
Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" &
Trim(UCase(strvarListDescription)) & "'"

rstClone.Find strClone

If rstClone.EOF Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
rstClone.Close

What we are seeing is that if we let this code run, it never
finds a match, even though the match exists. If we put the
following code in to pause the execution of the code:

'WE put this timer here because code was executing
too fast. 'This slows the code down so SQL has
enough
time run it
squery
Finish = 900
PauseTime = 3 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total
time.

the find always finds a match. What this indicates to us is that
the entire recordset is not built with the Set rstClone =
Me.RecordsetClone command and the Find executes before the
recordset is actually completed. Does this make sense to anyone?
We have other areas where we use exactly the same logic and have
never had a problem. We have even tried going to the end of the
recordset before issuing the find and that doesn't work either.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Yeah...we tried that... We even tried to do a MoveLast to force the
receordsetclone to be completed before the MoveFirst, but to no avail. The
really troubling part is that we are doing this type of thing elsewhere and
have never had a problem. However, it could just be that we have always
been searching for something in the first 50 or so rows of the recordset.

Thanks,
Brett


Sandra Daigle said:
That's what I figured (after I posted the .NoMatch bit anyway :-)).

One other thing to try is to do a MoveFirst prior to the Find.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brett said:
Sandra,

Sorry..yes, it is an ADP with a SQL Server backend. The Nomatch
option you sent earlier does not work as well because that is a DAO
construct as opposed to an ADO construct.

Thanks,
Brett

Sandra Daigle said:
Hi Brett,

Is this a Jet or SQL backend database? If it is Jet, try using
FindFirst instead of Find and see what happens.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brett Steele wrote:
Thanks Sandra,

I tried this and it still does not find the match. If I put a
MsgBox in front of this code, it finds the match. If I take the
MsgBox out, it does not. It just appears that the code is
executing faster than the recordsetclone can be created?

Any other ideas?

Brett

Hi Brett,

What happens if you just search the recordsetclone without going
through a separate recordset object? Also, you should be using the
NoMatch property since there is no guarantee that the EOF condition
will be true when a match is not found.

'Dim rstClone As Recordset
' Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" &
Trim(UCase(strvarListDescription)) & "'"

with me.recordsetclone
.Find strClone
If rstClone.nomatch Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
End with


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Brett Steele wrote:
We have an application that uses the following code:

Dim rstClone As Recordset
Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" &
Trim(UCase(strvarListDescription)) & "'"

rstClone.Find strClone

If rstClone.EOF Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
rstClone.Close

What we are seeing is that if we let this code run, it never
finds a match, even though the match exists. If we put the
following code in to pause the execution of the code:

'WE put this timer here because code was executing
too fast. 'This slows the code down so SQL has
enough
time run it
squery
Finish = 900
PauseTime = 3 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total
time.

the find always finds a match. What this indicates to us is that
the entire recordset is not built with the Set rstClone =
Me.RecordsetClone command and the Find executes before the
recordset is actually completed. Does this make sense to anyone?
We have other areas where we use exactly the same logic and have
never had a problem. We have even tried going to the end of the
recordset before issuing the find and that doesn't work either.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Hi Brett,

I'm sorry I haven't been much help on this - I searched google and found
about 3 other cases that reported similar problems but none of these were
resolved. I'll dig a bit more and try to find an answer for you.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brett said:
Yeah...we tried that... We even tried to do a MoveLast to force the
receordsetclone to be completed before the MoveFirst, but to no
avail. The really troubling part is that we are doing this type of
thing elsewhere and have never had a problem. However, it could just
be that we have always been searching for something in the first 50
or so rows of the recordset.

Thanks,
Brett


Sandra Daigle said:
That's what I figured (after I posted the .NoMatch bit anyway :-)).

One other thing to try is to do a MoveFirst prior to the Find.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brett said:
Sandra,

Sorry..yes, it is an ADP with a SQL Server backend. The Nomatch
option you sent earlier does not work as well because that is a DAO
construct as opposed to an ADO construct.

Thanks,
Brett

Hi Brett,

Is this a Jet or SQL backend database? If it is Jet, try using
FindFirst instead of Find and see what happens.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Brett Steele wrote:
Thanks Sandra,

I tried this and it still does not find the match. If I put a
MsgBox in front of this code, it finds the match. If I take the
MsgBox out, it does not. It just appears that the code is
executing faster than the recordsetclone can be created?

Any other ideas?

Brett

Hi Brett,

What happens if you just search the recordsetclone without going
through a separate recordset object? Also, you should be using
the NoMatch property since there is no guarantee that the EOF
condition will be true when a match is not found.

'Dim rstClone As Recordset
' Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" &
Trim(UCase(strvarListDescription)) & "'"

with me.recordsetclone
.Find strClone
If rstClone.nomatch Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
End with


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Brett Steele wrote:
We have an application that uses the following code:

Dim rstClone As Recordset
Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" &
Trim(UCase(strvarListDescription)) & "'"

rstClone.Find strClone

If rstClone.EOF Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
rstClone.Close

What we are seeing is that if we let this code run, it never
finds a match, even though the match exists. If we put the
following code in to pause the execution of the code:

'WE put this timer here because code was executing
too fast. 'This slows the code down so SQL has
enough
time run it
squery
Finish = 900
PauseTime = 3 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total
time.

the find always finds a match. What this indicates to us is
that the entire recordset is not built with the Set rstClone =
Me.RecordsetClone command and the Find executes before the
recordset is actually completed. Does this make sense to
anyone? We have other areas where we use exactly the same logic
and have never had a problem. We have even tried going to the
end of the recordset before issuing the find and that doesn't
work either.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
A couple of other thoughts/remarks - the Movefirst or some other navigation
is required else the Find will cause an error. You probably already know
this :-)

You indicated that you've tried doing Movelast prior to the find. Was this
done on the form's recordset or on the recordsetclone? What happens if you
try it on the form's recordset?

Also, have you tried filtering the recordset prior to doing the find.
Granted, this may be a bit of a work-around but at least it would get you
going.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Sandra said:
Hi Brett,

I'm sorry I haven't been much help on this - I searched google and
found about 3 other cases that reported similar problems but none of
these were resolved. I'll dig a bit more and try to find an answer
for you.


Brett said:
Yeah...we tried that... We even tried to do a MoveLast to force the
receordsetclone to be completed before the MoveFirst, but to no
avail. The really troubling part is that we are doing this type of
thing elsewhere and have never had a problem. However, it could just
be that we have always been searching for something in the first 50
or so rows of the recordset.

Thanks,
Brett


Sandra Daigle said:
That's what I figured (after I posted the .NoMatch bit anyway :-)).

One other thing to try is to do a MoveFirst prior to the Find.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brett Steele wrote:
Sandra,

Sorry..yes, it is an ADP with a SQL Server backend. The Nomatch
option you sent earlier does not work as well because that is a DAO
construct as opposed to an ADO construct.

Thanks,
Brett

Hi Brett,

Is this a Jet or SQL backend database? If it is Jet, try using
FindFirst instead of Find and see what happens.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Brett Steele wrote:
Thanks Sandra,

I tried this and it still does not find the match. If I put a
MsgBox in front of this code, it finds the match. If I take the
MsgBox out, it does not. It just appears that the code is
executing faster than the recordsetclone can be created?

Any other ideas?

Brett

Hi Brett,

What happens if you just search the recordsetclone without going
through a separate recordset object? Also, you should be using
the NoMatch property since there is no guarantee that the EOF
condition will be true when a match is not found.

'Dim rstClone As Recordset
' Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" &
Trim(UCase(strvarListDescription)) & "'"

with me.recordsetclone
.Find strClone
If rstClone.nomatch Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
End with


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Brett Steele wrote:
We have an application that uses the following code:

Dim rstClone As Recordset
Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" &
Trim(UCase(strvarListDescription)) & "'"

rstClone.Find strClone

If rstClone.EOF Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
rstClone.Close

What we are seeing is that if we let this code run, it never
finds a match, even though the match exists. If we put the
following code in to pause the execution of the code:

'WE put this timer here because code was
executing too fast. 'This slows the code down
so SQL has
enough
time run it
squery
Finish = 900
PauseTime = 3 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total
time.

the find always finds a match. What this indicates to us is
that the entire recordset is not built with the Set rstClone =
Me.RecordsetClone command and the Find executes before the
recordset is actually completed. Does this make sense to
anyone? We have other areas where we use exactly the same logic
and have never had a problem. We have even tried going to the
end of the recordset before issuing the find and that doesn't
work either.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
One more thing - try using Like instead of =

RecordsetClone.Find "FieldName LIKE '" & value & "%'"

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Sandra said:
A couple of other thoughts/remarks - the Movefirst or some other
navigation is required else the Find will cause an error. You
probably already know this :-)

You indicated that you've tried doing Movelast prior to the find. Was
this done on the form's recordset or on the recordsetclone? What
happens if you try it on the form's recordset?

Also, have you tried filtering the recordset prior to doing the find.
Granted, this may be a bit of a work-around but at least it would get
you going.


Sandra said:
Hi Brett,

I'm sorry I haven't been much help on this - I searched google and
found about 3 other cases that reported similar problems but none of
these were resolved. I'll dig a bit more and try to find an answer
for you.


Brett said:
Yeah...we tried that... We even tried to do a MoveLast to force the
receordsetclone to be completed before the MoveFirst, but to no
avail. The really troubling part is that we are doing this type of
thing elsewhere and have never had a problem. However, it could
just be that we have always been searching for something in the
first 50 or so rows of the recordset.

Thanks,
Brett


That's what I figured (after I posted the .NoMatch bit anyway :-)).

One other thing to try is to do a MoveFirst prior to the Find.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Brett Steele wrote:
Sandra,

Sorry..yes, it is an ADP with a SQL Server backend. The Nomatch
option you sent earlier does not work as well because that is a
DAO construct as opposed to an ADO construct.

Thanks,
Brett

Hi Brett,

Is this a Jet or SQL backend database? If it is Jet, try using
FindFirst instead of Find and see what happens.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Brett Steele wrote:
Thanks Sandra,

I tried this and it still does not find the match. If I put a
MsgBox in front of this code, it finds the match. If I take the
MsgBox out, it does not. It just appears that the code is
executing faster than the recordsetclone can be created?

Any other ideas?

Brett

Hi Brett,

What happens if you just search the recordsetclone without
going through a separate recordset object? Also, you should be
using the NoMatch property since there is no guarantee that
the EOF condition will be true when a match is not found.

'Dim rstClone As Recordset
' Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" &
Trim(UCase(strvarListDescription)) & "'"

with me.recordsetclone
.Find strClone
If rstClone.nomatch Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
End with


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Brett Steele wrote:
We have an application that uses the following code:

Dim rstClone As Recordset
Set rstClone = Me.RecordsetClone

Dim strClone As String
strClone = "ListDescription = '" &
Trim(UCase(strvarListDescription)) & "'"

rstClone.Find strClone

If rstClone.EOF Then
DoCmd.CancelEvent
Exit Sub
Else
Me.Bookmark = rstClone.Bookmark
End If
rstClone.Close

What we are seeing is that if we let this code run, it never
finds a match, even though the match exists. If we put the
following code in to pause the execution of the code:

'WE put this timer here because code was
executing too fast. 'This slows the code down
so SQL has
enough
time run it
squery
Finish = 900
PauseTime = 3 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total
time.

the find always finds a match. What this indicates to us is
that the entire recordset is not built with the Set rstClone =
Me.RecordsetClone command and the Find executes before the
recordset is actually completed. Does this make sense to
anyone? We have other areas where we use exactly the same
logic and have never had a problem. We have even tried going
to the end of the recordset before issuing the find and that
doesn't work either.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top