Sequential Access of Access with Joined Tables

[Bratfry]

The Database is Access, I am accessing it with DAO ( I think, at least I define it as below)

Dim db As DAO.Database 'Database Space
Dim Tracks As DAO.Recordset 'Record Set space for tracks
Dim Albums As DAO.Recordset 'Record Set space for Albums

I am trying to loop thru the database and extract data elements from both joined tables. It works great for the first record but the rest don’t have the data from the albums (ie.albums.position and albums.player)table. The code may not be syntax perfect but I think anyone will get the idea of the logic. The logic works, I display all the records but only good data from the tracks table.

I don't pretend to be a VB expert. To many options..

Set Tracks = db.OpenRecordset("Select tracks.title,tracks.id,albums.position, albums.player FROM Tracks INNER JOIN albums ON tracks.cdjid=albums.cdjid ORDER BY tracks.title&quot
For i = 1 To max 'max setup prior logic for tracks table
titlesv = Tracks("Title&quot
IDsv = Tracks("ID&quot
titlelen = Len(titlesv)
Artistlen = Len(Artistsv)
IDlen = Len(IDsv)
Playersv = Albums("CDJID&quot
Positionsv = Albums("Position&quot
If titlelen > 50 Then titlelen = 50
If Artistlen > 40 Then Artistlen = 40
displayline = (titlesv + Trim(Left(stars, (50 - titlelen)))) + Positionsv + " ID" + IDsv
lstdata.AddItem displayline

NextRecordset 'Move to the next record
Next i

 

[AndyWatt]

I think you want Tracks.MoveNext rather than NextRecordset

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"A computer program does what you tell it to do, not what you want it to do." -- Greer's Third Law