MS Access Database & VB6.0

Hello all!
I have an application in VB6.0 which connects to MS Access database table. I am able to search for a record and display results in a VB forms' texboxes. But now the problem I am having is that, I have 2 tables joined together by primary keys i.e. Table1 and table2. Table1 is the parent and table2 is dependent on table1. the relationship is 1 to many (table1->table2). I want in my vb form to search for a particular record and then display th contents of both table1 and table2 in one form.
How do I write a query that will do that. For one table, I am able to do that
e.g.

dim dbs as new adodb.connection
dim rst as new adodb.recordset

set rst = new adodb.recordset

search = msgbox("Input search item")

rst.open "select * from table1",dbs,1,2
rst.find id='"+ search +"'"

That is the code above will work for 1 table. but now that I want for the second table, how do i do it..

Thnak you

Phokojoe:

I think you need to get your Access book out and read about using parameters with Access queries.

I may not have this exactly right, but at least it wil get you in the right direction.

You have:

dim dbs as new adodb.connection
dim rst as new adodb.recordset

set rst = new adodb.recordset

search = msgbox("Input search item")

rst.open "select * from table1",dbs,1,2
rst.find id='"+ search +"'"

Change you code to something like this:

dim dbs as new adodb.connection
dim rst as new adodb.recordset

set rst = new adodb.recordset

rst.open

PARAMETERS [Enter ID Number] Text;
SELECT * FROM table1, table2
WHERE table1.ID=table2.ID


Like I said this may not be exactly right, but play with it a little and I think you can figure it out.

Good Luck

If the relationship is one is to many, then my idea of your form is like a header-details form. where header (table1) portion contains the key and the details (table2) contains the content.

Suggestion:

search for the key on table1 then display the content on the textboxes.

then on the text_change event of your textbox in your form that holds the key, create another select statement that uses value of the key textbox to fetch records on table2.

--if you use a join statement for the two tables, you may not be able to update the records if in case you need to edit it.