RecordSet shows DOUBLE the actual no. of records

[Bill R.]

I have the following code...

Set db = CurrentDb()
Set rs = db.OpenRecordset("SelectedFiles")
With rs
Limit = .RecordCount
.MoveFirst
For Item = 1 To Limit
If !FDSSelected Then
Import.StatusBarText = "Importing " & _
!FDSFullPath & "\" & !FDSFileName
Call ImportTextFile....
.Edit
!FDSSelected = False
.Update
End If
.MoveNext
Next Item
End With

Set rs = Nothing
Set db = Nothing


It works fine on my development system, but on a users
machine, the Limit is set to twice the actual count.
It was working OK a week or so ago, & I suspect something
ELSE changed on his system...possibly the DAO/ADO
functions. I can't remember where to find them. Would
this cause such a problem & where can I locate them. I've
been there once, & thought it was under tools/options, but
I don't see it now.

Thanks for the help, you folks here are Great!. -Bill.

 

[Ted Allen]

Hi Bill,

I'm not sure if this will take care of your problem, but
it is often recommended to use .MoveLast prior to
referring to the record count of a recordset, as it may
be unreliable if you don't.

I don't think this problem would be created by a change
in references (which I think is what you were referring
to), but you can find them under Tools|References from
within the VBA editor.

-Ted

 

[Dirk Goldgar]

I have the following code...

Set db = CurrentDb()
Set rs = db.OpenRecordset("SelectedFiles")
With rs
Limit = .RecordCount
.MoveFirst
For Item = 1 To Limit
If !FDSSelected Then
Import.StatusBarText = "Importing " & _
!FDSFullPath & "\" & !FDSFileName
Call ImportTextFile....
.Edit
!FDSSelected = False
.Update
End If
.MoveNext
Next Item
End With

Set rs = Nothing
Set db = Nothing


It works fine on my development system, but on a users
machine, the Limit is set to twice the actual count.
It was working OK a week or so ago, & I suspect something
ELSE changed on his system...possibly the DAO/ADO
functions. I can't remember where to find them. Would
this cause such a problem & where can I locate them. I've
been there once, & thought it was under tools/options, but
I don't see it now.

Thanks for the help, you folks here are Great!. -Bill.

This is very fishy. I have to assume that "SelectedFiles" is the name
of a local table, or the reference to the .RecordCount property would
not get the correct value for anybody. But I can't think of any change
to DAO or Jet that would cause an incorrect value to be returned under
those circumstances. Are you sure the user hasn't somehow duplicated
the records in the table, so that there are actually twice the number of
records in his copy of the table?

 

[John Vinson]

Set rs = db.OpenRecordset("SelectedFiles")

Is SelectedFiles a Query? If so, try opening it in datasheet view. I
suspect that there may be a join to a table with two records causing
duplication of all the records in the other tables.

 

[Bill R.]

Dirk/Ted/John,
Thanks so much for all the help. Yes, "SelectedFiles" is
a table in a local DB, & no, there are no relationships to
it anywhere. It starts out empty at every run, and I
populate it with a dir list of "potential" files for
importing. The user then selects the ones they want &
Clicks an import button. All the selected files are
imported fine, then the program runs out of files & gives
me the "No Current Record" error on the movenext function
at record #365. If I stop the code (or if it errors
out),the table in data view shows the correct number of
records(364). This is what's so baffling. There are 364
records in the table, but my Limit has been set to 728 via
the recordcount function. As a quick attempt to get the
user working again, I copied the table to a new name,
deleted the old table, then renamed the new one to the
proper name ("SelectedFiles"). I have good & bad news....
it fixed the problem, but in doing so, I covered my
tracks. This smacks of a corrupt table, but now I can't
duplicate the error. I know, I whished I'd saved the old
one. Sorry. ;( Also, on the users box, the tools / ref
is grayed out. What do I need to do. I was into the
module code but still it was unavailable to me. Thanks
again, sorry about the rambling on.... -Bill.

 

[John Vinson]

It starts out empty at every run, and I
populate it with a dir list of "potential" files for
importing. The user then selects the ones they want &
Clicks an import button.

.... and then impatiently clicks it again?

 

[Bill R]

.... and then impatiently clicks it again?

Good point, John, but I don't think he did because - 1.
the file names are displayed at the bottom, so he can see
the imports actually taking place. (about 3-5 seconds per
file), 2. when the program errors out, there is still only
364 rows in the table. Besides, even if he did click
twice, I would go thru the Limit = .recordcount
instruction each time. Also, the files are being imported
into a separate table. The "Selected Files" table only
containes the list of file names, the path to get them,
and a checkbox column. I really appreciate the help. I'll
try to save the table if we can recreate the error. I
think there must be some recordcount "property" associated
with tables maybe?, that wasn't getting reset even by
exiting Access & rebooting Win2k. Thanks again, -Bill.