Linking data in Subform to another Subform

[Jon Gellat via AccessMonster.com]

Hi,

I have a form which, from a combo box selection, shows a list of engines
and data on a sub form.

I have second subform from another table to show engine specifics.

What i need is when i click on an entry in the first subform, the second
subform is updated just to show that specific engine specifications.

That probably wasn't explained excellently but i hope you catch my drift!

Anyone know how to link the 2 subforms in this way?

Thanks!

 

[Guest]

As long as you have the parent/child relationships established between the
two forms the data should appear. You have let's say a GM327 Engine. The
first form displays general information about the GM327 engine. This table
would contain a field similar to EngineID which would identify the GM327
engine uniquely. If you have several GM327 engines that you want specific
information on you would have a table such as EngineDetails that contains
EngineID and a field to identify each individual engine...maybe SerialNumber.
Link the two forms using EngineID in the parent/child relationship. When the
user selects GM327 on the first form then all of the records that contain
GM327 as an EngineID will appear in your subform. If you have these field
names setup the same in each table the wizard will automatically choose this
relationship for you when you place the subform on the main form (if you have
the wizard enabled).

 

[Jon Gellat via AccessMonster.com]

I'll try and explain better what it is i have..

This is to do with ship engines and customers..

Table 1 Has customers ID Number and Name

Table 2 Has The above Plus the ship or ships each customer has (i.e the
name of the ship) and the type of ship/s.

Table 3 has all the engine data like serial number/power/kw etc. And also
has the customer name/id and what ship it's on.

So basically on the form i have the combo box with all the customer
names..this links into subform 1 which lists all the ships that customer
has..and then what i want to do is be able to click on any specific ships
on subform 1 and for it to then display the engine information on subform 2.

I just can't get access to do it!

 

[Guest]

So if I understand correctly you have something similar to:

tblCustomer
--------------
CustomerID
CustomerName

tblShips
---------
ShipID
CustomerID
ShipType

tblEngines
------------
ShipID
SerialNumber
Etc....

If that's true your setup might be like this to maintain what you currently
have.....

The main form is fed by tblCustomers which contains SubformA (Ship info)
which is fed by tblShips linked to the main form by the CustomerID field. The
Ship subform contains SubformB (Engine info) which is fed by tblEngines and
is linked to the Ships subform by the ShipID field.

You could do away with the main form by putting an unbound combo box in the
header of the Ships form that contains a distinct list of the customers. Use
the wizard to create the combo box and tell it that you want to search for
records on your form. Selecting the appropriate customer displays the ships
that they have and the corresponding engines within the ships. The
information in the Engines form will synchronize as you move from record to
record on the Ships form.

 

[Jon Gellat via AccessMonster.com]

I'm still having problems with this..

Basically when someone goes into the form..they'll be choosing a customer
name from the combo box at the top (frm table1)

When a name is selected from the combo box, the form shows all the vessels
which that customer has (from Table2)

At the moment this is fine, However there is a subform (from table3) which
is also updated by the combo box and shows ALL the engines the customer has.

What i want is for that subform (from table3) to show nothing until one of
the vessels is clicked on and for it to then show the information for that
vessel only. So instead of it showing all the engines for all the
vessels..i just want it to show the engine/engines for the vessel selected
in the form.

I don't know how hards a task this is but i'm going out of my mind thinking
about it!

Would i have to create another combo box to take information from the form
i.e the list of vessels the customer has, which would then update the
subform?

Below is a kind of layout of the form now:

-------
combo box (list of customers)
---------


Customer Name [________]

Table 2 info:

Vessel Type Date Sold
[----] [----] [----]
[----] [----] [----]

Table3 Subform:

Vessel Type KW S/N
[----] [----] [----]
[----] [----] [----]


Any help would be great!!!

 

[Guest]

If your subform view is datasheet or continuous form you'll always see all
corresponding records on the form. If you only want to see one record at a
time you'll have to change to form view.

Each subform record synchronizes to the parent form. If you're displaying
Customer1 on the main form then the subform will display Customer1 ships and
the engine subform will display Customer1 engines on the Customer1 ships
(individually if viewed in form view). This is all dependent on the forms
carrying the correct parent/child relationships.

What do you mean by...

"there is a subform (from table3) which is also updated by the combo box"

Do you have code behind the combo box?

 

[Jon Gellat via AccessMonster.com]

All 3 tables are related by all having a customer name field. the combo box
finds data on the form based on the value i select from the combo box..so
when i select a customer name..both forms show the details for that
specific customer.

I want the form to show all the data from Table2 (i.e all the vessels the
customer has) but i don't want it to show all the information on the
subform for table 3 (ie. all the engines and the engine specifics).

So on the form, i want to see all the vessels a customer has and when i
click on a specific vessel i want to then see what engines that vessel has
and the details.

I feel that this may be the death of me.