ADO Find and Bookmark, inconsistent results any suggestions?

Hi All,

i'm bit frustrated as all my users are reporting a weird behaviour
from one of our access database. It is an ADP application on SQL
Express 2005 as backend using Access 2003.

No problem of this sort has been reported whatsoever, and no change
have been done to either the dbase structure or the vba code since the
problem came out.

to be very concise, when a form open it will load -using the bookmark
method- the first record available. this is the code:

Dim rs As New ADODB.Recordset

    Set rs = Me.Recordset.Clone
    rs.MoveFirst
    rs.Find "[IdItemOrdered] = " & Me![machineList], 0, 1, 1
    If rs.EOF = False Then
        Me.Bookmark = rs.Bookmark
    End If
    Set rs = Nothing

where Me!machineList contains an ID previously checked with a direct
SQL query if it exist or not ... only then the control move over this
procedure ...

when i debug i can see the Me!machineList correctly populated, but
when it performs the Find method it found
nothing setting rs.EOF to True .. and so skipping the bookmark
operation.

Now this happen only on some records, while it doesn't happen on
others.. and there is no differences between these records... of
course the content is different but they contains the same amount of
data ...

any idea?
i'm completely lost.

thanks
T.

0
pingala
11/2/2007 1:18:19 PM
access 16762 articles. 3 followers. Follow

2 Replies
739 Views

Similar Articles

[PageSpeed] 10

Many possibilities here.  First, like mdb files, ADP are dynamically 
compiled and this compilation is based on the version of windows and SP of 
the client(s); so you have to make sure that everyone is using his or her 
own copy of the ADP file.  You should also decompile the copy of the ADP 
file directly on the client machine before using it to make sure that you 
are casher on this.

Second, beside a compilation problem, there are other things that are known 
to cause memory corruption problem: the use of bit fields that can be null 
(make sure that all of your bit fields cannot be null and have a default 
value of either 0 or 1).  Using RecordsetClone anywhere in your code instead 
of Recordset.Clone is also a well known source of problem.  Even if you are 
using Recordset.Clone in your example, it's possible that the memory is 
already corrupted if you have used RecordsetClone somewhere else.

Before calling rs.Find, make that the rs is valid by calling rs.EOF (and 
rs.BOF?).  You should make this test not only after but also before the call 
to .MoveFirst and .Find.

You are saying that the problem occurs when the form opens; so a possibility 
would be that you are changing the record source of the form directly in the 
OnOpen event or the OnLoad event.  Changing the record source in the OnOpen 
event will force the OnLoad event to be called immediately, before the end 
of the OnOpen event and not after its ending.  This change in the behavior 
of events can lead to subtil bugs and using the debugging will also change 
the overall behavior of these events.

Using the debugger not only can change the order of execution of events (I'm 
thinking in particular about the OnOpen and OnLoad events here) but also the 
memory layout; so instead of using the debugger, use Debug.Print in order to 
trace and debug this kind of problem.

Finally, make sure that you are closing the form before making any of your 
tests and that you are not switching between the design mode and the view 
mode.  When you are switching from design mode to the View mode; the record 
source is not necessarily called again and when it is, it's not necessarily 
with the default values of the controls on the form (for the controls that 
are used as parameters to the record source).

-- 
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"pingala" <pingala@gmail.com> wrote in message 
news:1194009499.779389.139730@22g2000hsm.googlegroups.com...
> Hi All,
>
> i'm bit frustrated as all my users are reporting a weird behaviour
> from one of our access database. It is an ADP application on SQL
> Express 2005 as backend using Access 2003.
>
> No problem of this sort has been reported whatsoever, and no change
> have been done to either the dbase structure or the vba code since the
> problem came out.
>
> to be very concise, when a form open it will load -using the bookmark
> method- the first record available. this is the code:
>
> Dim rs As New ADODB.Recordset
>
>    Set rs = Me.Recordset.Clone
>    rs.MoveFirst
>    rs.Find "[IdItemOrdered] = " & Me![machineList], 0, 1, 1
>    If rs.EOF = False Then
>        Me.Bookmark = rs.Bookmark
>    End If
>    Set rs = Nothing
>
> where Me!machineList contains an ID previously checked with a direct
> SQL query if it exist or not ... only then the control move over this
> procedure ...
>
> when i debug i can see the Me!machineList correctly populated, but
> when it performs the Find method it found
> nothing setting rs.EOF to True .. and so skipping the bookmark
> operation.
>
> Now this happen only on some records, while it doesn't happen on
> others.. and there is no differences between these records... of
> course the content is different but they contains the same amount of
> data ...
>
> any idea?
> i'm completely lost.
>
> thanks
> T.
> 


0
Sylvain
11/4/2007 5:23:18 PM
I forgot to mention the old problem with bookmark and the "Option Compare 
Database" or the "Option Compare Binary".  Don't know if this problem is 
still around or it can apply to the .Find method too but maybe you have a 
problem with .Find or .Bookmark simply because you have used the wrong 
option or have forgotten to write one at the beginning of your module(s).

-- 
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
wrote in message news:eIcgrdwHIHA.4272@TK2MSFTNGP06.phx.gbl...
> Many possibilities here.  First, like mdb files, ADP are dynamically 
> compiled and this compilation is based on the version of windows and SP of 
> the client(s); so you have to make sure that everyone is using his or her 
> own copy of the ADP file.  You should also decompile the copy of the ADP 
> file directly on the client machine before using it to make sure that you 
> are casher on this.
>
> Second, beside a compilation problem, there are other things that are 
> known to cause memory corruption problem: the use of bit fields that can 
> be null (make sure that all of your bit fields cannot be null and have a 
> default value of either 0 or 1).  Using RecordsetClone anywhere in your 
> code instead of Recordset.Clone is also a well known source of problem. 
> Even if you are using Recordset.Clone in your example, it's possible that 
> the memory is already corrupted if you have used RecordsetClone somewhere 
> else.
>
> Before calling rs.Find, make that the rs is valid by calling rs.EOF (and 
> rs.BOF?).  You should make this test not only after but also before the 
> call to .MoveFirst and .Find.
>
> You are saying that the problem occurs when the form opens; so a 
> possibility would be that you are changing the record source of the form 
> directly in the OnOpen event or the OnLoad event.  Changing the record 
> source in the OnOpen event will force the OnLoad event to be called 
> immediately, before the end of the OnOpen event and not after its ending. 
> This change in the behavior of events can lead to subtil bugs and using 
> the debugging will also change the overall behavior of these events.
>
> Using the debugger not only can change the order of execution of events 
> (I'm thinking in particular about the OnOpen and OnLoad events here) but 
> also the memory layout; so instead of using the debugger, use Debug.Print 
> in order to trace and debug this kind of problem.
>
> Finally, make sure that you are closing the form before making any of your 
> tests and that you are not switching between the design mode and the view 
> mode.  When you are switching from design mode to the View mode; the 
> record source is not necessarily called again and when it is, it's not 
> necessarily with the default values of the controls on the form (for the 
> controls that are used as parameters to the record source).
>
> -- 
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "pingala" <pingala@gmail.com> wrote in message 
> news:1194009499.779389.139730@22g2000hsm.googlegroups.com...
>> Hi All,
>>
>> i'm bit frustrated as all my users are reporting a weird behaviour
>> from one of our access database. It is an ADP application on SQL
>> Express 2005 as backend using Access 2003.
>>
>> No problem of this sort has been reported whatsoever, and no change
>> have been done to either the dbase structure or the vba code since the
>> problem came out.
>>
>> to be very concise, when a form open it will load -using the bookmark
>> method- the first record available. this is the code:
>>
>> Dim rs As New ADODB.Recordset
>>
>>    Set rs = Me.Recordset.Clone
>>    rs.MoveFirst
>>    rs.Find "[IdItemOrdered] = " & Me![machineList], 0, 1, 1
>>    If rs.EOF = False Then
>>        Me.Bookmark = rs.Bookmark
>>    End If
>>    Set rs = Nothing
>>
>> where Me!machineList contains an ID previously checked with a direct
>> SQL query if it exist or not ... only then the control move over this
>> procedure ...
>>
>> when i debug i can see the Me!machineList correctly populated, but
>> when it performs the Find method it found
>> nothing setting rs.EOF to True .. and so skipping the bookmark
>> operation.
>>
>> Now this happen only on some records, while it doesn't happen on
>> others.. and there is no differences between these records... of
>> course the content is different but they contains the same amount of
>> data ...
>>
>> any idea?
>> i'm completely lost.
>>
>> thanks
>> T.
>>
>
> 


0
Sylvain
11/4/2007 5:30:56 PM
Reply:

Similar Artilces:

Forwarding more than 2 messages results in messages bundled as attachments
Is there any way to forward multiple emails within Outlook (xp) as seperate emails to the recipient? If you try to highlight more than one message and forward it, it will treat the emails as attachments within the message. Thanks the only way is one at a time... -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Join OneNote...

Find First
I am having trouble making this work. I am trying to do a find in a subform. Dim rs As DAO.Recordset If Not IsNull(Me.Command33) Then Set rs = Forms![fAddGroupCat]![fAddGroupCatSub2].[Form].RecordsetClone rs.FindFirst "[Descr2] = """ & Me.Command33 & """" If rs.NoMatch Then MsgBox "Not found" Else Me.Bookmark = rs.Bookmark End If Set rs = Nothing End If Thank you, Steven Also: How can you tell it to start at the last record an...

Finding Merged Cells
Is there any (easy) way of finding a cell or cells that have been merged? I am trying to sort a (very large) spreadsheet but can't because Excel "...requires the merged cells to be the same size..." I can always select all and remove the merge - but I am wondering why there are merged cells - hence the reason for trying to find them! Many Thanks - I like the macro solution - it certainly works on a test setup - I'll try it on the real spreadsheet tomorrow >-----Original Message----- >Manually, you could divide and conquer. Select half the range, hit ctrl-1 (t...

boolean find criteria in Excel
Is it possible to set up the Excel Find and replace to find a or b or c in a cell of content a,b,c, etc? Thanks! Trent I wouldn't think so. -- Regards, Tom Ogilvy "davista00" <davista00@discussions.microsoft.com> wrote in message news:A20AE105-09AB-4681-AD60-60E08F0345BF@microsoft.com... > Is it possible to set up the Excel Find and replace to find a or b or c in a > cell of content a,b,c, etc? > > Thanks! > Trent ...

Find duplicate cells
Is there a way to find all the cells with identical entry? I'm trying to find duplicate information but I don't know what the duplicate entries are. Find and replace does not have an option to find something like that. TIA, cpliu Hi have a look at http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates -- Regards Frank Kabel Frankfurt, Germany "cpliu" <chanciusliuDeleteThis@yahoo.com> schrieb im Newsbeitrag news:Xns94A16109E4C87chanciusliuDeleteThi@130.133.1.4... > Is there a way to fi...

Trying to find out if I'm hacked.
I am a new Exchange 2003 admin and I'm trying to find out if my sever has gotten hacked. Here are the symptoms: 1. I am getting odd emails from senders liked Bounced Mail and stuff, saying that I've been sending out massive amounts of spam 2. My server is emailing me alerting me that store.exe is allocating more memory than normal. Here is what I have done this far: 1. Checked my server's queue to see if there are bunch of items, there are none. 2. Run my virus scan program several time and found nothing. So on the surface things seem ok, we are still sending and receivi...

Help finding Exchange Server or Administrator
I have been unable to use Outlook because I I can't access an Exchange Server or get an HTTP address from an. THese are the messages I continue to get, that doesn't allow me to move forward with use. Help! Do you have an exchange server account? If so, contact your email administrator. If you don't have an exchange account (or if you don't know if you do, you probably don't) then you may need to create a new profile. What version of outlook do you have? If you have an exchange account and use OWA, see http://www.outlook-tips.net/archives/20030904.htm if you want...

Advanced Find 05-22-06
Hi, I would like to search for services details using Advanced Find and make/create a report with the resources information. For example... I would like to know the users that are working on a specified service? or What resources groups the user belongs and then make a report with that information. If i go throw the resource group area i can create a report with that information, but it doesnt include the user email for example. Any sugestion how this could be done? thx ...

Multiple results with Count
Hello Everybody, In Access 2003, I am trying to get multiple results in a query using Count with the same column and table as my source of data. So far I have been unsuccessful in bringing two results of Count onto a same query. For example: I have a list of amounts paid to different employees in a column. I want to count the rows base on two different criteria (i) rows with amounts more than $1000 each and (ii) rows with amounts less than $500. I want to get the results of these two criteria in same query but in two different fields with distinct names. What would be the structure of ...

Advanced Find in contact's birthday problem
Hi, I'm having a simple problem in MS CRM 3.0 regarding advanced find with Contact's birthday. My filter criteria is Contact's Birthday as entity and operator "Today". The result is nothing. But when I checked the birthday of one of the contact, there exists at least one. I noticed that the advanced find also matching the year. when I changed the birthday from 7/20/1954 to 7/20/2006, the advanced find output the result. How can we go about this problem? True, this is one of the thing I hope Microsoft will implement in the next version (or better service pack :)) ...

ADO FIND question
I have a VB program accessing non-indexed tables (by primary key). I'm using the RECORDSET.OPEN / RECORDSET.FIND syntax, coupled with RECORDSET.MOVENEXT. I would like to be able to stop the quesry when the last record with my specified value is found. How can I do that when the field I'm using is not indexed (even though it's a key field)? Any insight would be appreciated. Thanks! A primary key is an index. I'm not sure that I understand the rest of your question. How is your code supposed to know that the last record to match your criteria is the last record without...

Cannot find GWL97.xla
when I try to open or close a file in other then safe mode xl flashes cannot find GWL97.xla, I am using office 97, everything works of in safe mode. Can anyone Help, I am new at excell, but am used to lotus 123. "Roger Poor" <r.poor@worldnet.att.net> wrote in message news:03cf01c347b4$70ecd340$3501280a@phx.gbl... > when I try to open or close a file in other then safe mode > xl flashes cannot find GWL97.xla, I am using office 97, > everything works of in safe mode. Can anyone Help, I am > new at excell, but am used to lotus 123. I beleive GWL97.xla is a Groupwi...

DirSync of Novell Connector, doesn't find existing users
DirSync of Novell Connector, doesn't find existing users ...

Macro to find the first cell in a column with different data
Hello, I really could use some of your expert and excellent help with a problem I am having in creating a macro to perform a search in "Column A" to locate a change in the data in that column and then move one column to the right and one row down and type "1" in that cell. I really appreciate your help, and Have a Blessed Holiday Season. Perhaps this might also do it here .. Assuming data is in A1 down, of the structure as below: 1 1 1 2 2 3 3 etc Put in B2: =IF(A2<>A1,1,"") Copy down Kill the formulas in col B with an in-place: Copy > Paste spe...

finding hidden tab settings
I appear to have some hidden tab settings from text that I have imported from WordPerfect. How do I find and delete them? I am accustomed to using "Reveal Codes" and am totally lost. I did save the text as text only, but it appears that the tabs came over with the text. ...

OWA 2003 Logoff Button Results in Login Prompt
We are are running Exchange 2003 SP1, and have a separate front-end server. When we click the 'Logoff' button in the upper right-hand corner of the OWA screen, we are immediately prompted to authenticate again. Can anyone tell me what might be causing this issue? On the OWA front-end, we have Basic authentication enabled (so users dont have to enter a domain\ at logon), and forms authentication is not enabled. We do not have SSL enabled as of yet (we in the pilot phase right now), but will for security reasons later this week. Thanks, Pet On Mon, 25 Jul 2005 08:29:52 -0700,...

Autofilter shows no results, but there should be because there are matches
the column that I am trying to use autofilter on has nothing but numbers in it. But when I do a custom filter for a number that begins with '5' (for example), it shows no results. Even though there are plenty of numbers that start with '5'. I do the same excercise on another worksheet that is formatted the same, and it works fine. If I go back to the non-working worksheet and retype the number in each cell the autofilter will work. WHY OH WHY is this so? That happened to me whenever I imported data from a particular database. On import, somehow a little 1/2 space was put i...

Find the top value in a list
I have a worksheet created in EXCEL 2003 which i use to record swimmers times in several lists. One examlpe is in cells D109 to 113. In cells D111 to 113 are recent times and D109 to 110 are empty. As I add current times to the top of the list. I want to be able to retrive the current value at the top of the list and show it in D106. D107 & 108 are used for other values. At present I have the following formula in cell D106 =OFFSET(D108,COUNTBLANK(D109:D113)+COUNTA(D109,D113),0). This works well but makes updating the list difficult. Is there a simpler way to achive the same...

Can find POP3 server but not SMTP?
Hi all I am having a problem connecting to an SMTP server with Outlook 2000 in Windows XP, it says that the SMTP server is not found and gives me a dialogue box to try and reenter it. The POP3 server is the same as the SMTP server (different ports), and it finds the POP3 server no problem and downloads mail, but when it goes to send mails, it tells me that it can't find the server (even though it just found it). Also I have tried pinging the server, and no problem there. I checked the ports - 25 for SMTP, 110 for POP3. I also verified the following settings were correct by comparing wi...

how to find nodes at xml file without complete load the xml file
how to find nodes at xml file without complete load the xml file since the file is very big!! joe wrote: > how to find nodes at xml file without complete load the xml file > since the file is very big!! You can read through the XML document with an Xml(Text)Reader, that is forward only pull parsing where your code only pulls in the current node. The documentation is here <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemXmlXmlTextReaderClassTopic.asp> for .NET 1.x and here <http://msdn2.microsoft.com/en-us/library/system.xml.xmlreader(...

How find appointments that *start* 12a.m.?
I'm using Outlook 2003. Is there any way to find all appointments that *begin* at 12 a.m.? Hint, it is *not* this: FIND (button) > Options (pull down) > Advanced > (Appointments and Meetings) > Advanced (tab) > Field (pull down) > Date/time fields > start (...which only give options like yesterday, today, last week, etc. ... but not a specific *time* like 12 a.m.) The reason I need to see these is, I may have some holdover things from several upgrades and and old Palm that may have been "no time" in the Palm, but somehow got tagged as 12 a.m. at so...

clearing items shown by the AutoComplete feature in the Advanced Find Dialog box
Hi all, How can I clear the items shown by the AutoComplete feature in the "search for the word(s)" field in the Advanced Find Dialog box?? Thank You Max I found that info in the MRU 1 value in the registry here: HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Outlook\Office Finder You can try clearing/deleting that value (of course, the usual cautions apply... export the key first just in case) "max" <a@b.net> wrote in message news:D8ymb.486287$4w.57408106@amsnews03.chello.com... > Hi all, > > How can I clear the items shown by the AutoComplete feat...

Lookup / Array Range / find the value
I have seen similiar examples but not that were not using exact matches to retrieve data. This seems simple and maybe I am over thinking but I have been unable to crack this nut. I have a table where the first tier sales runs between 0 and 20k and awards 1K commission. I want to be able to enter the salse result for various team members and obtain the correct commission amount based on their sales for the period. How should I complete the commission formula for this process? Sales Value Start Sales Value Stop Commission $0 $20,000 $1,000.00 $...

Function to find hyperlink URL
Hi, Is there function to return URL of hyperlink? I don't want to use macros. I'm using Excel 2000 Thanks in advance Nikola Milic >>Is there function to return URL of hyperlink? A worksheet function? Afraid not. -- Jim Rech Excel MVP But you could write your own (a userdefined function). I used ctrl-K (insert|Hyperlink) to create the link: Option Explicit Function GetURL(Rng As Range) As String Application.Volatile Set Rng = Rng(1) If Rng.Hyperlinks.Count = 0 Then GetURL = "" Else GetURL = Rng.Hyperlinks(1).Address ...

How to find the right MLM business online that succeeds for you #5
U can 2 Hi there, Building a MLM business can have it's advantages * Lifetime Residual income * Financial Security * Spend more time with your kids * Travel * Get out of debt * Drive a new car * Purchase a vacation home * Pay off your mortgage But to be successful, you must choose the right company. * Choose a company that is at least 3 years old. * Choose a company that has a product or service everybody can use. * Choose a company with a easy to understand compensation plan. * Choose a company that has a low monthly fee, where most everybody can join. * Choose a company that ...