Linking two different tables on one form WITHOUT using Data Environment Deisgner 
Author Message
 Linking two different tables on one form WITHOUT using Data Environment Deisgner

I can link two tables on a form using the data designer parent-child
and SHAPE sql, but can you do this from ADO code. I am using a sql
string. The sql string is complex, like SELECT * from tbl1, tbl2 where
Order = Order for example. What I want to be able to do is populate
fields that belong to the first table on the form as textboxes, and
the fields from the 2nd table as a datagrid, but have the recordset
linked on the Order field.

Any ideas most welcome, I know this is easy to do using the data
environment but can't figure it out with code.

Thanks

Eamonn



Mon, 27 Sep 2004 00:25:22 GMT  
 Linking two different tables on one form WITHOUT using Data Environment Deisgner


Quote:
> I can link two tables on a form using the data designer parent-child
> and SHAPE sql, but can you do this from ADO code. I am using a sql
> string. The sql string is complex, like SELECT * from tbl1, tbl2 where
> Order = Order for example. What I want to be able to do is populate
> fields that belong to the first table on the form as textboxes, and
> the fields from the 2nd table as a datagrid, but have the recordset
> linked on the Order field.

> Any ideas most welcome, I know this is easy to do using the data
> environment but can't figure it out with code.

> Thanks

> Eamonn

Hi Eamonn,

Open the recordset for your form using SQL something like this:

Dim rst as ADODB.Recordset
Dim strSQL as String

Set rst = New ADODB.Recordset

strSQL = "SHAPE {SELECT * FROM tbl1} APPEND ({SELECT * FROM tbl2}
        RELATE Order TO Order) AS OrdDetail"
rst.Open strSQL, myConnection

Then, as you move from row to row in this recordset you can populate your
textboxes using the fields from tbl1, and set the datasource of your
datagrid to rst("OrdDetail").Value which returns the sub-recordset of
detail items relating to the current row in tbl1.

hth,
Richard



Mon, 27 Sep 2004 07:14:02 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Trying to bind data from two different tables to two different controls

2. linking two or more data controls on one form

3. Input Data from Single Form into Two Linked Tables

4. Is that possible to link two tables with different length of the key

5. sort two different recordsets into one table

6. linking two tables -- one oracle and the other jet

7. URGENT: Link two fields in one table?

8. Linking two tables to enable inserting value from one to other

9. URGENT: Link two fields in one table?

10. Changing the linked table path without the linked table manager

11. Query data from tables in two different databases

12. Getting data from two tables in different databases

 

 
Powered by phpBB® Forum Software