SEEK/FIND METHODS

[NW]

I’m trying to use seek/find methods without a success.<br>I have listed my table structures & coding below & hoping to get an answer soon.<br><br>Table names:<br>1. AirMain – contains records created from a text file. <br>2. InvaliPC- contains only invalid records from this file<br><br>Structure of files:<br>Table - Airmain Table - InvalidPC<br>Address Line1 Addres Line2<br>Address Line2 Address Line2<br>Town Town<br>County County<br>PostCode PostCode<br>URN ReturnPostcode <br> URN<br><br>** Both files been indexed by URN<br><br><br>Form Load()<br>&nbsp;Set db = DBEngine.OpenDatabase(App.Path & &quot;\AIRTOURS.MDB&quot<br>&nbsp;Set rs = db.OpenRecordset(&quot;AIRMAIN&quot;, dbOpenDynaset) <br>&nbsp;Set rsInvPc = db.OpenRecordset(&quot;InvalidPC&quot;, dbOpenDynaset)<br>End Sub<br><br>After extracting records from InvalidPc to a text file- I’m running an external “postcode cleaning” process to correct these invalid records & generated postcode will be stored under “ReturnPostCode” field.<br><br>Once this process finish, I want to copy only updated recs. (where ReturnPostCode&lt;&gt;””) to the AirMain table.<br>This is my coding to do a search on Airmain table<br><br>Dim u<br>While Not rsInvPc.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;u = rsInvPc.Fields(&quot;urn&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rs.FindFirst &quot;URN =&quot; & u<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If rs.NoMatch Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;Cannot&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox rs.PercentPosition<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;‘&nbsp;&nbsp;rs.Seek &quot;=&quot;, u<br>&nbsp;&nbsp;&nbsp;&nbsp;' MsgBox rs.PercentPosition<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rsInvPc.MoveNext<br>&nbsp;Wend<br><br>(This always return a 0 (zero) for rs.PercentPosition)<br><br>Can any one tell me where/what’s wrong with this code?<br>Any help will greatly appreciate.<br>Thanks<br>

 

[JimHorton]

If you use Seek, you must open the rs as dbOpenTable, not dbOpenDynaset.&nbsp;&nbsp;Then you must set the index on which you'll seek, ie <br>rs.index = &quot;indexname&quot; (Often this will be &quot;PrimaryKey&quot<br>Also, if u is a string, you need to add the quotes, if numeric the code for findfirst looks ok, but instead of rsInvPc.fields(&quot;URN&quot, you can use rsInvPc!Urn.&nbsp;&nbsp;It's easier to read and code, and I think there's a small performance gain.<br>--Jim

 

[NW]

Jim<br>Thank you very much for your reply.<br>