Three Tables on one Form

[PALman]

I have three Access 2000 tables linked by one key field named JobNo. In Access I was able to create a form which displayed all the fields from each table and was able to scroll through the records for each JobNo.
However I am having difficulty trying doing the same with a form created in VB6.
I have tried using the Form Data Wizard which gives me a Record Navigation Bar at the bottom of my form but this can only connect to one table. If I create another two bars from the toolbox they too remain bound to one table only.
On the form I display the JobNo field once only from Table 1. I do not show the same field (which holds the same data/jobno) from Tables 2 & 3.
Can anyone help me to link my data / text boxes which are bound to the three table’s fields in such a way that when I move to the next record/job number that all the fields reflect the data for that new record/JobNo. I think I am right in saying I have a one-to-many setup.
I have tinkered with ADODC from the toolbox but do not understand what I am trying to do.
As always any help is much appreciated.
Thanks.

 

[CognosChicago]

Try creating a query from the three tables and use the query in the form instead of the three tables. It's really the same thing, but it may work a little differently in VB6.

 

[JerryKlmns]

PALman

Use a query to display data from the table of the one part of relationship. Text boxes & Labels will suffice.
Use a different query to display data from the table of the many part of the relationship. The join used for the relationship should be transformed to a Where clause of the 2nd query to retrive the many records. Use a grid for this purpose.

ADODC control is easy but lacks flexibility.

I prefer to open a connection to the database, and use it to execute stored procedures-parameter queries-queries and queries created on the fly. The result is a recordset. You do the binding of the text boxes, labels, grids etc, and all the manipulation of the recordset. For a grid you could use a MSFlexGridControl.

If you are learning now use the wizards for hints & tips. If you are planning to use only MS-Access DAO 3.6 library is Ok else start with ADO 2.x Library

Good luck!

 

[PALman]

Thanks CognosChicago and JerryKlmns,
I shall create query as you both suggest.
However I have one question... In the creation of a form based on a query or queries shall I be able to edit all the data from the three tables on the form?
I need to be able to update all three tables directly from the one form.
Thanks

 

[JerryKlmns]

Depends on how you open the recordsets. Explore the various properties of the recordsets & methods. Do keep in mind Data Integrity Rules to treat them correctly, especially for the orphans!
Also be aware that SQL reserved words used in a query DISTINCT & GROUP BY, joins in the WHERE clause and any CrossTab query are NOT updatable Also Harnessing the Power of Updatable Queries, a nice thing to keep in mind