How to view data from linked Access tables in 1 form?

[Carl Philip]

Hello people!

I am new to Visual Basic and I am trying to show data from 3
linked Acess-tables on 1 form.
Those tables are:
1. A table containing information about computers.
Each record/computer has a primary key named HWPrim
(from HardWare Primary key)
2. A table containing software packages.
Its primary key is SWPrim (from SoftWare Primary key)
3. A table which links both previous tables: it contains nothing but the primary keys from those 2 tables.
This table shows which software packages are linked to
(or installed on) which computer.

Now, what I would like to do is the following:
I need to make a form which shows the data of 1 computer in seperate fields. Furthermore, the form should contain a grid or sheet which shows all software which has been linked to that computer, so that in my application I could see which software was installed on which computer.
For the rest the form should only have something like an ADO Data Control so that I can browse between all computers in the database and that the grid or sheet on the form will always show the software of the computer shown.

I have seen that the Data Form Wizard can show data from 2 linked tables if you choose the "Master/Detail" type of form, but I cannot find out how I could possible do this with my 3 linked tables.
Anybody help me?
Thank you very much in advance!
Carl Nauwelaerts
Brussels, Belgium

[Andrew_Fryer]

Use a recordset with the following source

SELECT h.*, s.*
FROM computers h, software s, main m
WHERE m.HWPRIM = h.HWPRIM
AND m.SWPRIM = s.HWPRIMT;

That or something like it should work....

[Iouri]

If you want to see data from 3 different tables you can do one of the following things:
1. Create 3 data controls and bind them to your 3 different tables which you will show on your form in 3 data grids

2.Qreate a query in your DB which will be a join of these 3 tables and show this query in the datagrid

Iouri Boutchkine
[email protected]

[Carl Philip]

Hello Iouri;

Thank you for your help, but the layout I would like for the form is different from what your solutions would show, at least I think.

First, let me remind of how my tables are linked:
HARDWARE---(link)---HSLINK---(link)---SOFTWARE
The HSLink-table only links the HARDWARE and SOFTWARE tables and only contains the primary keys of those 2 tables (ie HWPrim and SWPrim).

Now I would like to make a form which looks like this:
- Computer model (= a field from table hardware)
- Computer make (= a field from table hardware)
- A Grid with all software from the SOFTWARE table which have been linked to that computer through the HSLink-table.
- An ADO Data Control or something with wich I can browse through the different computers.
When I do browse, that grid should always show the software which has been installed on the current computer.

My big problem is that I do not know how to get data from 3 linked tables with only one ADO Data Control. You can do that with the Data Form Wizard in the "Add From" window: If you choose the "Master/Detail" type of form in that wizard, it will create a form of the sort I would like, but it can only create such a form from 2 linked tables, and I have 3 linked tables...

I have created a query in my Access database which does it, but of course that query shows ALL computers, together with all linked software.

Could you help me any further? I am really a newbie when it comes to VB, and the 2 books I bought don't give me any answer.
Thanks again in advance!
Carl Philip

[Carl Philip]

Dear Andrew;

Thanks a lot for your reply, but as I am a total newbie to VB, I don't even know where I should put that sort of code. I know that it is SQL though.

I have put some extra explanations in a reply to Iouri's post, so if you are still interested in helping me, you could read it.
My reply shows how I could ALMOST do what I want to do by using the Data Form wizard from the "Add Form" window, so I think that it will all be clear to you if you read it.

Thanks again!
Carl Philip