Link between Sub-form in a form

[Tessai2003]

Hello,

I am trying to create a simple database for one of my customer...

To make it simple I have 2 tables, a main table (prospect) and a contact table

Prospect table Contac table
-------------------- ------------
main_id (autonumber) contact_id(autonumber
main_name(text) contact_name(text
main_surname contact_details
...?

I made a form with the prospect table and added a sub-form with the contact table

What I wanted to do is to link those 2 forms and as result show in the form only the contact related to the original main table
In brief, not see another contact of another record...
If I'm not clear do not hesitate to let me know ;-)

Can someone explain me what relationship I have to make ?
Wich primary key as to be used ?
Are my tables complete ?

It's not working when I'm trying it... I cannnot add records ni the sub-form... or I see all the records even for other

Thanks for your help,

Stephane

 

[PHV]

Seems that the contact table lacks a foreign key pointing to prospect table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Tessai2003]

Hello PHV,

To be honest I could guess that something was missing ;-)

But what exactly ? I'm not very used to primary or foreign key...

Can you propose me something ?

Cheers,

Stephane

 

[Betsvigi9]

Can I ask why you want the contact details to appear in a sub form?

Sub forms are usually used for one to many relationships. For example, one main ID and name has many contact details attached to it. If there is more than one set of contact details per main ID record, then you would need to set the main ID as the key field and then also have the main ID in the contact details table as the linking field. The sub form would then be linked via that field.

If you only have one contact details record per main ID then you would only need to create one table.

I might be missing the point of what you are trying to achieve though!

 

[Tessai2003]

This is indeed the case, there should have more than 1 contact to be added for 1 prospect...

This is only an example, I have to add another fields but I don't get the principe to add many contact related to 1 form...

When I wanted to add more contacts via a button or anything else... I always received all the contacts created and related to prior records...

I want to find a workaround... so I thought of a sub-form...

Ah yes, I also want to be able to see wich contacts are part of the prospect X or Y

Thanks,

 

[PHV]

Take a look here:

http://www.abcdataworks.com/fundamentals.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Betsvigi9]

Have a look at the Northwind sample database which is supplied by microsoft with Access. Look at the relationships for examples of key fields and linking tables. Then look at how the forms and subforms are linked.

You should find Northwind.mdb on your C: drive under Program Files\Microsoft Office\Office (or Office10)\Samples\