Query not updating fast enough

[legos]

I have the following code that updates a table called importlist. The table importlist is then used to make a query called stored2. Stored2 is a query that displays the matches between 2 tables. The matches to stored2 to are then put in a table called stored and displayed on a form. The problem is that i'm only getting one record being put into stored when stored2 has many more records than that. Any suggestions on why this is happening?
the SQL for the query is shown below, followed by the code that updates import list
SQL Query:
SELECT wellinventory.SEC, wellinventory.TWP, wellinventory.RNG, wellinventory.COUNTY, wellinventory.FIELD, wellinventory.LEASE, wellinventory.WELLNUM, wellinventory.OPERATOR, wellinventory.depth, wellinventory.API, wellinventory.serial
FROM wellinventory LEFT JOIN importlist ON wellinventory.API = importlist.api
WHERE (((importlist.api) = wellinventory.API));

VB Code:
For Each vrtSelectedItem In .SelectedItems

Set fs1 = CreateObject("Scripting.FileSystemObject")
Set f = fs1.OpenTextFile(vrtSelectedItem, ForReading)

Do While f.AtEndOfStream <> True
line = f.readline
Set rst2 = db.OpenRecordset("importlist")
With rst2
.Addnew
!api = line
.Update
End With
Loop
f.Close
Next vrtSelectedItem
Set rst2 = db.OpenRecordset("stored2")
Set rst1 = db.OpenRecordset("stored")
For count = 1 To rst2.RecordCount
With rst1
.Addnew
!Section = rst2!Section
!Township = rst2!Township
!Range = rst2!Range
!CountyParish = rst2!CountyParish
!Field = rst2!Field
!Well = rst2!Well
!WellNumber = rst2!WellNumber
!Company = rst2!Company
!State = rst2!State
!Depth = rst2!Depth
!FileName = rst2!FileName
!APINumber = rst2!APINumber
!SerialNumber = rst2!SerialNumber
.Update
End With
Next count

Durible Outer Casing to Prevent Fall-Apart

 

[legos]

i ended up fixing it by using the dcount function instead of recordcount. I also forgot to put a rst2.movenext. heres the new code:
Set rst2 = db.OpenRecordset("stored2")
Set rst1 = db.OpenRecordset("stored")
For count = 1 To dcount("*", "stored2")
With rst1
.Addnew
!Section = rst2!Section
!Township = rst2!Township
!Range = rst2!Range
!CountyParish = rst2!CountyParish
!Field = rst2!Field
!Well = rst2!Well
!WellNumber = rst2!WellNumber
!Company = rst2!Company
!State = rst2!State
!Depth = rst2!Depth
!FileName = rst2!FileName
!APINumber = rst2!APINumber
!SerialNumber = rst2!SerialNumber
.Update
End With
rst2.movenext
Next count

Durible Outer Casing to Prevent Fall-Apart

 

[PHV]

Anyway I don't see why you use that:
FROM wellinventory LEFT JOIN importlist ON wellinventory.API = importlist.api
WHERE (((importlist.api) = wellinventory.API));
instead of that:
FROM wellinventory INNER JOIN importlist ON wellinventory.API = importlist.api
;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244