recordset problem

D

dave

ok i have a recordset object called Records and a string called myString i
then have the following code:

Records.MoveFirst

Do While Not Records.EOF

myString = myString & "<field1>"
myString = myString & Records(0)

myString = myString & "<field2>"
myString = myString & Records(1)

myString = myString & "<field3>"
myString = myString & Records(2)

Records.MoveNext

Loop

this works fine for the first three iterations then on the fourth Records(2)
says it contains Null even though it does not. Also the loop stops after that
iteration even though there is another row of data. any ideas on what could
be going on. thanks in advance
 
D

Dirk Goldgar

dave said:
ok i have a recordset object called Records and a string called
myString i then have the following code:

Records.MoveFirst

Do While Not Records.EOF

myString = myString & "<field1>"
myString = myString & Records(0)

myString = myString & "<field2>"
myString = myString & Records(1)

myString = myString & "<field3>"
myString = myString & Records(2)

Records.MoveNext

Loop

this works fine for the first three iterations then on the fourth
Records(2) says it contains Null even though it does not. Also the
loop stops after that iteration even though there is another row of
data. any ideas on what could be going on. thanks in advance
Click to expand...

I'm inclined to think your recordset doesn't contain what you think it
does. Have you stepped through the code, examining the properties and
fields of the recordset object in the Locals window? You haven't shown
us how the recordset is opened or what its table or query source is, so
there's not much more we can say.
 
D

dave

Dirk Goldgar said:
I'm inclined to think your recordset doesn't contain what you think it
does. Have you stepped through the code, examining the properties and
fields of the recordset object in the Locals window? You haven't shown
us how the recordset is opened or what its table or query source is, so
there's not much more we can say.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...

this is how i create the recordset

Set MyDB = OpenDatabase("C:\Documents and Settings\dave\Desktop\servers.mdb")
Set Records = MyDB.OpenRecordset("Select * from Users")
Records.OpenRecordset

Also I have stepped through the code watching the contents of Records(2) and
they do say that they contain Null at the 4th iteration and even if i just
use Records.moveLast they still contain just Null even though i am looking at
the table and see that it contains a string(and there is another row of data
that my code ignores)
 
D

Dirk Goldgar

dave said:
this is how i create the recordset

Set MyDB = OpenDatabase("C:\Documents and
Settings\dave\Desktop\servers.mdb")
Set Records = MyDB.OpenRecordset("Select * from Users")
Records.OpenRecordset

Also I have stepped through the code watching the contents of
Records(2) and they do say that they contain Null at the 4th
iteration and even if i just use Records.moveLast they still contain
just Null even though i am looking at the table and see that it
contains a string(and there is another row of data that my code
ignores)
Click to expand...

This line ...
Records.OpenRecordset
Click to expand...

.... doesn't make any sense, since you've already opened the recordset.
As far as I can tell, it just opens another recordset from the first and
then discards it. However, I don't see how that would cause the problem
you're reporting.

Also, looking at this line:
Set MyDB = OpenDatabase("C:\Documents and
Settings\dave\Desktop\servers.mdb")
Click to expand...

Is that the same database as the one in which the code is running? If
so, you would do better to use the CurrentDb function to get a reference
to that database:

Set MyDB = CurrentDb

That would be more efficient and would not create two separate
connections to the database, which can sometimes lead to synchronization
problems. If that is not the current database, however, ignore this
point. Again, I don't see at the moment how this could be the cause of
your problem, but it's an eccentricity in your code that makes me
wonder.

Could it be that you're just getting the records returned out of the
order you expect? Your query string doesn't contain an ORDER BY clause,
so the order of records in the recordset will be arbitrary, and they may
not be presented in the same order as you see when you open the table
directly, especially if the table has no primary key. Are there any
records in the table that have Null in the field you're looking at? Can
you verify, by looking at the other fields in the recordset as you step
through it, that you're looking at a record that ought not to have Null
in that field?
 
D

dave

Dirk Goldgar said:
This line ...


.... doesn't make any sense, since you've already opened the recordset.
As far as I can tell, it just opens another recordset from the first and
then discards it. However, I don't see how that would cause the problem
you're reporting.

Also, looking at this line:


Is that the same database as the one in which the code is running? If
so, you would do better to use the CurrentDb function to get a reference
to that database:

Set MyDB = CurrentDb

That would be more efficient and would not create two separate
connections to the database, which can sometimes lead to synchronization
problems. If that is not the current database, however, ignore this
point. Again, I don't see at the moment how this could be the cause of
your problem, but it's an eccentricity in your code that makes me
wonder.

Could it be that you're just getting the records returned out of the
order you expect? Your query string doesn't contain an ORDER BY clause,
so the order of records in the recordset will be arbitrary, and they may
not be presented in the same order as you see when you open the table
directly, especially if the table has no primary key. Are there any
records in the table that have Null in the field you're looking at? Can
you verify, by looking at the other fields in the recordset as you step
through it, that you're looking at a record that ought not to have Null
in that field?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...

Ok i removed the openRecordset ( i was just trying anything at that point)
and switched to Set MyDB = CurrentDb and now it works though it doesn't go
through the table from top to bottom as i expected its not real important but
i would prefer it if i got them in that order. any ideas on how to make it do
that
 
D

dave

nevermind i found the answer it was because i changed"Select * From Users" to
just "Users" thanks for all your help
 
D

Dirk Goldgar

dave said:
nevermind i found the answer it was because i changed"Select * From
Users" to just "Users" thanks for all your help
Click to expand...

Great! Doing it that way will normally return the records in the same
order that Access would use if you opened the table directly in
datasheet view. That's still an arbitrary order, normally the order of
the primary key fields, if there is such a key. If you want to impose a
specific, reliable order on the records, use a SELECT statement with an
ORDER BY clause.
 
Top