Only 1st field in Table returned.

E

Eoin Bairead

Hi - wierdo

here's some nice simple VBA Code to get Author & Title information from a
table in a database.

The 1st field in the table is Record Number, and that's the field that's
returned for EVERY firld requested.
In other words, Author & Title both return 1, then 2, then 3 and so on.

Any ideas where I could be going wrong.
I don't want to re-instal Office, but I will if I have to.


----------------------------------------------------------------------------------------


Set MyDataBase = CurrentDb
Set MyRecordSet = MyDataBase.OpenRecordset("Table")
With MyRecordSet
.MoveFirst
.MoveLast
End With

If MyRecordSet.RecordCount > 0 Then
With MyRecordSet
.MoveFirst
For totin = 1 To MyRecordSet.RecordCount
For ix = 1 To 30
If Not IsNull(MyRecordSet("Author")) Then
Ax = MyRecordSet("Author")
Else
Ax = ""
End If
Tx = MyRecordSet("Title")
End If
Next ix
if totin < MyRecordSet.RecordCount then .MoveNext
Next totin
End With
End If
 
T

Tim Ferguson

here's some nice simple VBA Code to get Author & Title information
from a table in a database.
Click to expand...

No it's not: it's horribly complex. And you don't ever do anything with
the values you get from the recordset, so I don't see how you know what
is being returned. Try this:

' do all the work in the SQL statement
jetSQL = "SELECT TOP 30 Author, Title FROM Table " & _
"ORDER BY RecordNumber ASC;"

' always use the simplest recordset that will satisfy your
' needs. Here you are only iterating in one direction so a
' forward only snapshot is fine.
set rs = db.OpenRecordset(jetSQL, dbOpenSnapshot, dbForwardOnly)

' checking EOF will catch an empty recordset; no need to do all the
' movelast rubbish. Your network manager will love you for it!!
do while not rs.EOF

' really you need to do something sensible with
' the values here...
msgbox rs!Author & vbNewLine & rs!Title

' examine next record
rs.movenext

' and go round again until all thirty records are used up
loop

' tidy up
rs.Close


Hope that helps


Tim F
 
E

Eoin Bairead

Thanks, Tim

That's neater, but it just returns NULLs for all fields.

There's something funny with either VBA or Microsoft Access 10.0 on my
machine.
jetSQL = "SELECT TOP 30 Author, Title FROM Table ORDER BY RecordNumber
ASC;"
set rs = db.OpenRecordset(jetSQL, dbOpenSnapshot, dbForwardOnly)
do while not rs.EOF
msgbox rs!Author & vbNewLine & rs!Title
rs.movenext
loop
rs.Close
Click to expand...

Eoin
 
T

Tim Ferguson

That's neater, but it just returns NULLs for all fields.
Click to expand...

Huh? Are there any data in the table? Do you have On Error Resume Next
activated somewhere? What happens if you step through the code and ask for
a ? rst!Author on the way?

Puzzled,


Tim F
 
Top