Seek (ADO) finds same absoluteposition for two different indexes

The function below takes a serial number entered in the "Serach" control and then seeks that value in the data table called "PCM Interfaces (Main Table)". If seek does not return EOF then the absolute position is found and then the form is set to show that record. This function is just a form record locator.The problem is that the same AbsolutePosition (1) is found for records 1 and 2, when their respective serial numbers are searched. So the symptoms are that when serial number 1 is searched it locates record 1. When serial number 2 is searched it locates record 1. When serial number 3 is searched it locates record 2. And searching from that point on finds record (n-1) for the serial number serached. I believe this function worked at one time during development when I had "dummy" test data in the data table. Once I deleted that data and started entering valid data it no longer works. Any help would be appreciated.Private Sub Find_Button_Click()Dim lngDesiredRecord As LongDim intAnswer As IntegerIf StrLen(Me![Search].Value) > 0 Then        'Open "PCM Interfaces (Main Table) recordset.        Dim rsMainData As New ADODB.Recordset        With rsMainData        .Open "PCM Interfaces (Main Table)", CurrentProject.Connection, _        adOpenKeyset, adLockOptimistic, adCmdTableDirect        .Index = "PrimaryKey"        .MoveFirst        End With                'Seek to find the record with the entered ("Search" Textbox Control) Serial Number in the RecordSet.        rsMainData.Seek Me![Search].Value, adSeekFirstEQ                'Determine if the Serial Number was found using Seek        If rsMainData.EOF = False Then                    'Get the position of the found record            lngDesiredRecord = rsMainData.AbsolutePosition                        'Go to the found record on the form            DoCmd.GoToRecord acDataForm, "Main Data Entry", acGoTo, lngDesiredRecord                        'Close the recordset            rsMainData.Close            Set rsMainData = Nothing                Else                    intAnswer = MsgBox("The desired Serial Number was not found in the database", vbCritical + vbOKOnly, "No Record Found Warning")                        Search.Value = Null                    End If         Else  intAnswer = MsgBox("The Search Serial Number field is blank, enter a value and try again.", vbExclamation + vbOKOnly, "Blank Field Warning!") End If    End Sub-- Regards, Michael
0
Utf
3/12/2007 2:43:15 PM
access 16762 articles. 3 followers. Follow

3 Replies
873 Views

Similar Articles

[PageSpeed] 31

Hi Mac,Is there a way to do it so I can keep my string control?yes, you can change the code as follows:'~~~~~~~~~~~~~~~~~~~~Private Function FindRecordString()    'if nothing is picked in the active control, exit    If IsNull(Me.ActiveControl) Then       MsgBox "The Search Serial Number field is blank," _        & " enter a value and try again." _        , vbExclamation + vbOKOnly _        , "Blank Field Warning!")      Exit Function    end if    'save current record if changes were made    If me.dirty then me.dirty = false    'declare a variable to hold the value to look up    Dim mStr as string    'set value to look up by what is selected    mStr = Me.ActiveControl    'clear the choice to find    Me.ActiveControl = Null    'find the first value that matches    Me.RecordsetClone.FindFirst "stringfield = '" & mStr & "'"    'if a matching record was found, then move to it    If Not Me.RecordsetClone.NoMatch Then       Me.Bookmark = Me.RecordsetClone.Bookmark    Else       MsgBox "The desired Serial Number was not found" _          & vbcrlf & vbcrlf & "--> " & mStr          , vbCritical + vbOKOnly, "No Record Found Warning"    End IfEnd Function'~~~~~~~~~~~~~~~~~~~~because this method works so well, I never use any other...instead of just telling them the number was not found, you could ask if they wish to add it    if MsgBox("Message" _      ,vbYesNo + vbDefaultButton2 _      , "Add Record) = vbNo then exit functionWarm Regards,Crystal  *      (:  have an awesome day  :)   *MVP AccessRemote Programming and Trainingstrive4peace2006 at yahoo.com   *Mac wrote:> Is there a way to do it so I can keep my string control? Do you have any idea > why after a seek the absoluteposition would be the same (1) for the first two > records and then one off (less) for all the rest throughout the recordset?> > Any suggestions on how to open a recordset so it supports seek and > absolutposition (ADO)?
0
strive4peace
3/13/2007 1:43:37 PM
It worked! I still have other code that uses absoluteposition and now I'm going to use bookmark instead. How do I set a form to show a bookmark once the record is found and the bookmark is recorded in a  variable?-- Regards, Michael"strive4peace" wrote:> Hi Mac,> > Is there a way to do it so I can keep my string control?> > yes, you can change the code as follows:> > '~~~~~~~~~~~~~~~~~~~~> Private Function FindRecordString()> >     'if nothing is picked in the active control, exit>     If IsNull(Me.ActiveControl) Then>        MsgBox "The Search Serial Number field is blank," _>         & " enter a value and try again." _>         , vbExclamation + vbOKOnly _>         , "Blank Field Warning!")>       Exit Function>     end if> >     'save current record if changes were made>     If me.dirty then me.dirty = false> >     'declare a variable to hold the value to look up>     Dim mStr as string> >     'set value to look up by what is selected>     mStr = Me.ActiveControl> >     'clear the choice to find>     Me.ActiveControl = Null> >     'find the first value that matches>     Me.RecordsetClone.FindFirst "stringfield = '" & mStr & "'"> >     'if a matching record was found, then move to it>     If Not Me.RecordsetClone.NoMatch Then>        Me.Bookmark = Me.RecordsetClone.Bookmark>     Else>        MsgBox "The desired Serial Number was not found" _>           & vbcrlf & vbcrlf & "--> " & mStr>           , vbCritical + vbOKOnly, "No Record Found Warning">     End If> > End Function> > '~~~~~~~~~~~~~~~~~~~~> > because this method works so well, I never use any other...> > instead of just telling them the number was not found, you could ask if > they wish to add it> >     if MsgBox("Message" _>       ,vbYesNo + vbDefaultButton2 _>       , "Add Record) = vbNo then exit function> > > Warm Regards,> Crystal>   *>       (:  have an awesome day  :)>    *> MVP Access> Remote Programming and Training> strive4peace2006 at yahoo.com>    *> > > > Mac wrote:> > Is there a way to do it so I can keep my string control? Do you have any idea > > why after a seek the absoluteposition would be the same (1) for the first two > > records and then one off (less) for all the rest throughout the recordset?> > > > Any suggestions on how to open a recordset so it supports seek and > > absolutposition (ADO)?> 
0
Utf
3/13/2007 2:43:44 PM
Hi Mac,glad it worked for you.  There is no reason to show or record the bookmark -- it will not stay constant anyway.  If you want to find the record again, simply record the value of whatever field you used to find it to begin with.Warm Regards,Crystal  *      (:  have an awesome day  :)   *MVP AccessRemote Programming and Trainingstrive4peace2006 at yahoo.com   *Mac wrote:> It worked! I still have other code that uses absoluteposition and now I'm > going to use bookmark instead. How do I set a form to show a bookmark once > the record is found and the bookmark is recorded in a  variable?
0
strive4peace
3/15/2007 3:53:13 PM
Reply:

Similar Artilces:

Sum a column that meets two criteria
I need to sum a column of numbers if it matches two different criteria. I can set up the SUMIF easily for meeting one criteria, but I need to also sum the column if it meets that criteria, and another. For example: A B C 1 150 ABC MS1 2 200 DEF MS0 3 100 LMN MS0 4 125 ABC MS1 5 175 LMN MS1 6 225 DEF MS0 I need to have a formula that would say <<Sum column A IF column B = "DEF" AND column C = "MS0">>. (and so forth for the other combinations). I know there has to be a way to do this, probably using a combination of an IF and SUMIF functions - but i keep...

Lookup two columns
I want to compare the contents of two (adjacent) cells in one sheet with two adjacent cells in another sheet (within one workspace) and if the *pair* of cells are the same, deliver the value in the cell a few columns along (if you know what I mean - like lookup but comparing two cells). The cells are not sorted. Any ideas? Cheers. Bobby If you are comparing A1-B1 sheet 1 to A1-B1 sheet 2, then =IF(AND(Sheet1!A1=Sheet2!A1,Sheet1!B1=Sheet2!B1),"They match","no match") If you have to "lookup" A1-B1 against the whole columns of A and B on sheet2, then kyou co...

Calculate the % increase for two columns
I have a pivot table, the data was first display by date, i know i can use the grouping function to group data into monthly basis. But I want to know that can I set the formula to calculate the months difference between, say the sales amount of June & July, and the % of the difference?? Million thanks If you have a grouped field, you won't be able to add a calculated item to the pivot table. In the source data, you could add a column to calculate the month for each record. Refresh the pivot table, and add the new field Add another copy of the Data field to the data area Right-c...

Count column difference
Hi Using MSExcel 97. I have two columns of data e.g. A1: A4, containing values 5,10, 3, 6 B1:B4, containing values 3, 8, 7, 4 I wish to perform a count (e.g. in C5) of the number of rows where the value in column A exceeds the respective value in column B (in this case count = 3, as A1>B1, A2>B2, and A4>B4). Just cannot get my formula right. Tried using an array (but difficult when comparing the difference between two columns), and COUNT. Thanks in advance for any suggestions. Wizzy ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.co...

Forms in two differant views
Is there a way to use the same sub form showing two differant views within the same Main Form. For example: Can I show on one tab a datasheet view and on the other a regular form view. -- Rose Hi Rose, Create a button on your form and under the "OnClick" event put 'Me.DefaultView = Datasheet Alternatively you could use a checkbox If me.checkbox1 = 0 then Me.DefaultView = 2 ' Datasheet Else Me.DefaultView = 0 'Single Form End if me.repaint This should flick the form between datasheet and form views. HTH, Nick. "Rose" wrote: > Is there a way to use t...

id like to know where i can find a template for repay of a loan
...

Delivery Status Notification (DSN) different if sent from outside
When my users mailboxes are full above quota and not accepting any more mail, the sender receives a differently worded DSN depending on whether they sent email to that user from an internal account (one that is part of the exchange org) or an external account (not part of the Exchange organization). The one sent to the a user that is external is much less human friendly and is causing me a problem with some scirpted automation in my app. Is there a way to make the text the same for both? Examples pasted below: Here is the if sender is internal: Your message did not reach some or all o...

Run two copies of Outlook with different profiles
Hello! I'm trying to run two copies of Outlook with different (outlook)profiles at the same time. Normally i can choose the profile with the /profile switch, but if there is already a copy of outlook running, this switch seems to be ignored. Any ideas how to handle this are welcome. Regards, Reinhard "Reinhard" <reinhard.spieker@bkvibro.de> wrote in message news:60a7e218.0311190408.6523100b@posting.google.com... > Hello! > > I'm trying to run two copies of Outlook with different > (outlook)profiles at the same time. > > Normally i can choose the p...

PrintPreview
Hello, I developed a VC++/MFC SDI application. The program supports PrintPreview. However, I only want the user to go forward ("Next Page") and not backward ("Prev Page"). How do I hide the "Prev Page" and "Two Page" buttons? TIA, Jacques Hello All, I found a nice Print Preview replacement toolbar (with bitmap buttons) on codeguru. To hide the "Prev Page" and "Two Page" buttons, simply remove them from the CMyPreviewView::OnCreate(LPCREATESTRUCT lpCreateStruct) method. The article was written by Robin J. Leatherbarrow. Thank...

Tab Index
Hello, I'm faced with a weird problem that I have never seen before and was hoping someone could enlighten me as to the fix. I am trying to set the Tab Index in a form (access 2000). The form/page contains 15 controls. I am simply trying set them sequentially. 0.1.2.3.4... not a complicated thing (normally). However, I can't get the values to stick. I will enter a value move on and come back and it has been changed? I will enter a value goto another property and the value changes in front of me? Anyone have any ideas what the heck is going on? Thank you, Daniel P If yo...

how do I find a list of databases with correpsponding company name
Hello, I need to know how in GP to pull up a list of our companies with their corresponding SQL database names. I've done this a long time ago and remember it being easy but cannot remember where to do this. SELECT INTERID, CMPNYNAM FROM DYNAMICS..SY01500 "Tim Quiggle" <Tim Quiggle@discussions.microsoft.com> wrote in message news:D0ECF5C5-74F9-494E-8C2E-B37EE12C8D0C@microsoft.com... > Hello, > > I need to know how in GP to pull up a list of our companies with their > corresponding SQL database names. I've done this a long time ago and > rem...

different versions in visio ole-object?
hello i have some problems printing a word document which includes some visio drawingns. In word i can see the visio object like i edited it but when printing the word-document, a completly old version of the visio object is printed out. The printed object is older that 2 weeks. I tryed printing the document from other PC without changes. I tryed copying the ole-object into an other word dokument (copy/paste) without changes. Printing the document from wordpad works great .... I also opened the visio object (doublecklick in word) and edited the object, saved the file and exited back to wo...

Find a value in cells
Hi, all, I have something like this : a 1 b 4 c 5 d 3 I'm doing a max function in the number column and it returns me '5' But, after that, I want to know the letter which corresponds to the max number How can I do that? Thanks Nic -- nicgendron ------------------------------------------------------------------------ nicgendron's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25151 View this thread: http://www.excelforum.com/showthread.php?threadid=386417 Let's say that...

How does clickonce find the local application?
Hi, The shortcut file created by clickonce deployment having an extension .appref-ms only has a link that looks like: http://127.0.0.1/ClickOnceDemo/ClickOnceDemo.application#ClickOnceDemo.application, Culture=neutral, PublicKeyToken=27d66f1bda5f165d, processorArchitecture=msil The file http://127.0.0.1/ClickOnceDemo/ClickOnceDemo.application has the name and version number of the executable assembly. When ClickOnce application is invoked, the framework compares the version of the locally installed app against the version in the deployment manifest. My question is, how does th...

Difference 05-21-04
What is the difference between CRM standard and professional? -Johnny Johnny wrote: > What is the difference between CRM standard and professional? > > -Johnny Does it have anything to do with Outlook integration? -- - I am Johnny! Your Internet penpal. I have a scooter, how about you? "Johnny" <alphascooter-verizon@yahoo.com> wrote in message news:evfBJNwPEHA.3708@TK2MSFTNGP10.phx.gbl... > Johnny wrote: > > > What is the difference between CRM standard and professional? > > > > -Johnny > Does it have anything to do with Outlook inte...

lost two months of received emails
Help I lost about 2 months of received emails. They are not in my deleted folder. I already tried the pst restore utility. Thanks ...

finding values in a sum
if you have an array of numbers and want to identify which of those numbers add up to a specified value, is there a function in excel that can help you to find the correct combination of numbers. eg in a simple example; if the array of numbers was 2,3,5,6 and the specified value was 9, we know the only combination of numbers from this array that would sum to give the value 9 are 6 & 3, however with a larger array of numbers (20) or more, it would be more difficult solve the problem manually. http://groups.google.com/groups?threadm=e3iWLUiYDHA.2960%40tk2msftngp13.phx. gbl See my previo...

Index / Contents Page
Is it possible to insert an index /contents page in Visio? I have a 30 page Visio document and on Page 2 I want to have a contents page so that the reader can reference a page quickly. Is it possible with out having to manually type it i.e. If the page order changes your index / contents is automatically updated as with Word. There should be an example of create a TOC using VBA at www.mvps.org/visio/VBA.htm John... Visio MVP Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm Need VBA examples? http://www.mvps.org/visio/VBA.htm Common Visio Questions http://www.mvps.org/visi...

Multiple Accounts held at the SAME Institution with DIFFERENT logons
I am trying to sync Money 2006 to my Banc of America Investment Services accounts. I have two accounts with two DIFFERENT logons, an IRA and a regular brokerage. Money will sync fine to one or the other, but I can't set it up to sync to both because when I click on Online Services it is already setup so I can't add a different login, etc. -- Daniel Blackmon Project Lead - Software Engineer Worldwide Environmental Products Inc. In microsoft.public.money, Daniel wrote: >I am trying to sync Money 2006 to my Banc of America Investment Services >accounts. I have two accounts w...

feature difference btw 2000 & 2003
X-No-archive: yes I currently have Outlook 2000 and wanted to know if its worthwhile to upgrade to outlook 2003 ? I only use the mail portion of outlook and don't use calendar or any of the other options. Is 2003 more compatible with WinXP ? Thanks, Chris If you are just strictly e-mail (no calendar, contacts, tasks, .etc) then you are missing a good portion of what Outlook 2003 is bringing to the table. However some of things that you might like in Outlook 2003 are: 1) Better security. (bots, pictures, .etc that load from the web in html are blocked by default) 2) Cool new ...

How to find the check form to use for GP 9.0
I don't want to use Deluxe as our check vendor. I want to use Harland. Microsoft MBS sales just gives us Deluxe's phone number, when we request help with obtaining the correct format for Harland checks. Harland has two check formats for GP payable checks. 1) Great Plains Accounting 7.0 - 9.0 2) Great Plains Dynamics Harland isn't sure and can't reference Deluxe. Thanks, John John, From what I can tell from Harland's website, you want the Laser Multi-Purpose checks they show under Microsoft Great Plains. That's the one with the check in the middle, stub on t...

copy data to differences places based on selection
I have a master list of entities name in master name sheets. Let say entity 1 to entitiy 100. If I select Entity 1 to Entity 10, these 1 to 10 entities insert below group1, group2, group 3 in sheet1. It also insert below group4, group5 in sheet2. I don't have preference about how to select the names in master list. Anything like check, or yes is fine to me. I am open to any idea or method. thank you in advance. ...

AfxBeginThread failure
In an MFC pgm under VS2005sp1, I am creating a worker thread using AfxBeginThread. Under certain circumstances which I can't quite pin down, AfxBeginThread returns NULL, indicating it has failed. Is there any way I can find out the cause of the failure, such as an error code? I have tried tracing through the code with the debugger, but what is apparently detecting the error condition is in MS code for which I don't have the source. My specific code is CWinThread* pThread=AfxBeginThread(MyControlFunction,&ti,0/*priority*/, 230000000/*stack size*/); and a test for pThread==NULL im...

"No indexers" on creating "New Shared Services Provider "
Hi to all, I've this problem, I what to create the new and first "Shared Services Provider", but on "Index Server" section I don't find any Index Server (in combo-box). What can I do? Where am I wrong? Bye Dario Concilio Make sure you configured and started the Search services. -- Daniel A. Galant Imagine what we could be... if we could just imagine. "Dario Concilio MCP" <dario.concilio@hotmail.it> wrote in message news:uhcKStDzKHA.5040@TK2MSFTNGP02.phx.gbl... > Hi to all, > I've this problem, I what to creat...

Compare 2 excel files for differences?
Is there something within MS Office 2007 which will do this?. Alternatievly anyone any recommendation? Duplicates This site is great for finding Uniques/Duplicates: http://www.cpearson.com/excel/Duplicates.aspx Also, for things pertaining to Data Validation, you may want to check this out: http://www.contextures.com/xlDataVal01.html ....and if you feel really ambitious: http://www.contextures.com/xlDataVal08.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Dave" <nospam@nospam.com> wrote in message news:85...