Hello all,

I am definitely a beginner with VB 6.0. I have a project where I need to open an access database and display the data from multiple tables. I don't have a problem displaying data from an individual table. I stumble when I need to combine info from two other tables to display the data from one table.

here is what I'm doing for multiple databases and recordsets where each recordset represents infividual tables in the database:

dim db, db2, db3 as database 'same database source
dim rs. ms, mm as recordset 'each recorset will represent a different table

Set db = OpenDatabase("C:\..\..\book.mdb")
Set rs = db.OpenRecordset("artist")
Set db2 = OpenDatabase("C:\..\..\book.mdb")
Set ms = db.OpenRecordset("cd")
Set db3 = OpenDatabase("C:\..\..\book.mdb")
Set mm = db.OpenRecordset("made")

I have to display the fname and lname which comes from the same "artist" table. I will rs.movenext until rs.EOF
to iterate through the "artist"

txtArtistFirstName = rs.fields("fname")
txtArtistLastName = rs.fields("lname")

All other fields are available with their respective recordset. BUT I also have to display the artist's CD name and genre. Those fields,
CDname, genre are easily found in the "cd" table. But in order to know
the name of title and genre of the cd attributed to the current artist
I have to use the "made" table where CDID is matched with AID

Now, here is the select statement I need to use to assign the value
to the txtCDName. I know that the syntax is wrong but what do I use
intead, so that is where I need help.

txtCDTitle = "select cd.cdTitle
from "cd"
where cd.CDID = (select made.CDID
from "made", "artist"
where made.aid = artist.aid)

The only thing I know is that the recordset made from the artist
table points to the artist.aid that I am using in the sql statement
artist.aid = rs.Fields("aid")

I hope this post is not confusing.

Any help is greatly appreciated. Thank you in advance.