[AccessD] Error #: 3709 "The search key was not found in any record"
Robert
robert at servicexp.com
Wed Jul 15 18:54:07 CDT 2015
I will take your advice and switch the record to dbOpenDynaset and all
others to dbOpenSnapshot.
WBR
Robert
-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan
Waters
Sent: Wednesday, July 15, 2015 10:26 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Error #: 3709 "The search key was not found in any
record"
Hi Robert,
I haven't been following this too closely, but I do want to suggest that you
not walk through a recordset using dbOpenForwardOnly. After much experience
with problems I just couldn't solve, I gave up on dbOpenForwardOnly except
to look for a yes or no answer in a large recordset.
Now, if I need to walk through a recordset, I use dbOpenSnapshot (or
dbOpenDynaset).
So I would say that you should replace all instances of dbOpenForwardOnly
with dbOpenSnapshot if you need to walk through a read-only recordset.
Good Luck!
Dan
-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Robert
Sent: Wednesday, July 15, 2015 6:04 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Error #: 3709 "The search key was not found in any
record"
I looked at the code, and there is no MoveLast only MoveNext as the
recordset is dbOpenForwardOnly. Is it not good to use dbOpenForwardOnly?
WBR
Robert
-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Robert
Sent: Wednesday, July 15, 2015 6:42 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Error #: 3709 "The search key was not found in any
record"
All this time I didn't know about the MoveFirst "gotcha", and I don't think
I've ever run into that, but I will make the necessary changes. I did know
about the moving through the record set (MoveLast then MoveFirst) before
retrieving RecoundCount for an accurate Record Count.
All that said, do you think the omission would cause a 3709 error, and not
just a No Record error?
WBR
Robert
-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Charlotte Foust
Sent: Tuesday, July 14, 2015 2:15 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Error #: 3709 "The search key was not found in any
record"
I don't see MoveFirst in there anywhere, just a MoveLast. EOF and BOF can
potentially be true when you don't start on the first record, and Record
Count in a DAO recordset can return -1 if you don't movelast then movefirst
before calling it.
Charlotte Foust
(916) 206-4336
On Mon, Jul 13, 2015 at 5:22 PM, Robert <robert at servicexp.com> wrote:
> Ok so I'm at my wits-end here; for some reason I have a customer that
> is intermittently getting the above error.
>
> 1) There is no corrupted records, I can add and or delete records, I
> can delete all records, etc.
> 2) There are no primary keys on memo fields.
> 3) I can compact and repair both back end db's with no problem
> 4) I have run both db's through JetCOMP.
>
> I simply cannot find any problems with the data or structure of these
> databases.
>
> MS Access 2007 .mde front end
> Ms Access 2002 .mdb back end (both) One is secured and the other is not.
>
> Here is the code (I know it's not very efficient but...:-) ) that is
> generating the error maybe 2 to 3 times a day. (this function is
> called every 5-10 minutes during a 10 hour day)
>
>
>
>
> **********************************************************************
> *************************
>
>
> '---------------------------------------------------------------------
> ------------------
> '. Procedure : SyncroniseEventTbl
> '. DateTime : 11/15/2006 19:57
> '. Author : Robert
> '. Revision :
> ' Purpose : Synchronize The ServiceCall Table and The Event Table (2
> different db's)
>
> '---------------------------------------------------------------------
> ------------------
> '
> Public Function SyncroniseEventTbl() As Boolean
> Dim db As DAO.Database
> Dim rstSCTbl As DAO.Recordset
> Dim rstEvents As DAO.Recordset
> Dim lRN As Long
> Dim dOldDate As Date
> Dim dNewDate As Date
>
> Dim dSchTimeStart As Date
> Dim dSchTimeEnd As Date
>
> 'Let do a quick orphan kill run before we start
> 10 On Error GoTo HandleErr
> 20 DeleteScheduleEventOrphan
>
> 30 Set db = CurrentDb
> 40 Set rstSCTbl = db.OpenRecordset("qryselShedulerViewCal",
> dbOpenForwardOnly)
> 50 Set rstZones = db.OpenRecordset("qrySelZones", dbOpenDynaset)
> 60 Set rstEvents = db.OpenRecordset("Event", dbOpenDynaset)
> ''Debug.Print "Syncing Tbls"
> 70 PMIncrement 1, "Opening and Retrieving Schedule data ..."
> 80 With rstSCTbl
> 90 If .RecordCount > 0 Then
> 100 DoEvents
> 110 Do Until .EOF
> 120 PMIncrement 1, "Opening and Retrieving Schedule data ..."
> 130 CalendarLocked = False
> 140 rstEvents.FindFirst "RepairNumber= " & CLng(!RepairNumber)
>
> 150 If rstEvents.NoMatch = True Then 'Lets add the event to the
> table
> 160 If IsNull(!DateScheduled) = True Then 'We don't want to add
> any none scheduled items
> GoTo MoveHere:
> 170 End If
>
>
>
>
> **********************************************************************
> ****************************
>
>
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
More information about the AccessD
mailing list