Dificult with subform

[jtguardado]

Hello, wonderful forum, as I do not speak English, this is a machine
translation of Google, I hope you understand my question.
I have to make a form with a subform included.
This subform would include data on the main form.
The difficulty is given by the relations between the two tables

Main form
Table 1: Suppliers
id_prov

Subform
Table 2: Articles
id_art
prov_1
prov_2
prov_3

Where the two tables are related as follows
id_prov -> prov_1
-> prov_2
-> prov_3
Where any item can belong to three different suppliers.

The idea is that in all the articles appear subform provider's main form, is
related to any of the three fields on the subform.

Thanks in advance
A greeting from Spain
J. Tomas

 

[Daryl S]

Jtguardado -

The problem is in your table design. Your Articles table should only
contain information about the article (not anything about suppliers). You
should have a separate table for article_suppliers that would have id_art and
id_prov in them as foreign keys. There could be more than one record for any
article or any supplier.

Table 1: Suppliers
id_prov

Table 2: Articles
id_art

Table 3: Article_Suppliers
id_art_sup
id_prov
id_art

With this structure you can use the records in Article_Suppliers for your
subform, linking to the main form on id_prov. If you need other fields from
the Articles table, then create a query joining Article_Suppliers to Articles
on id_art, and use this query as the recordsource for the form.

 

[jubiiab via AccessMonster.com]

Why don't you make the Articles as Mainform and Suppliers as subform instead?

I guess you want to register an article in your system and have information
about who can provide you that article. One article can have many suppliers
right?

Create 3 Tables:
tblArticle [ArticleID_PK][ArticleName] [Price]….[Add all those fields you
need]

tblSupplier [SupplierID_PK] [CompanyName] [Adress] [ContactPerson] [TlfNo]…
[Add all those fields you need]

tblArticleSupplier [ArticleSupplierID_PK] [ArticleID] [SupplierID]

Make the relationships

Create the mainForm. Name it frmArticle

Create qryArticleSupplier and use:
tblArticleSupplier.[ArticleSupplierID] tblArticleSupplier.[ArticleID]
tblArticleSupplier.[SupplierID] tblSupplier.[CompanyName] tblSupplier[Adress]
tblSupplier[ContactPerson] tblSupplier[TlfNo]

In your mainForm you add the qryArticleSupplier as subform and hide those
field you don’t want to show like [ArticleID] you don’t need to see in
subform.

I think you should use Continuous Forms as Default View in your subform. And
make a Delete button. I have made and tested the tables and forms above. It
should work.

Good luck

 

[jubiiab via AccessMonster.com]

qryArticleSupplier is an Query

Why don't you make the Articles as Mainform and Suppliers as subform instead?

I guess you want to register an article in your system and have information
about who can provide you that article. One article can have many suppliers
right?

Create 3 Tables:
tblArticle [ArticleID_PK][ArticleName] [Price]….[Add all those fields you
need]

tblSupplier [SupplierID_PK] [CompanyName] [Adress] [ContactPerson] [TlfNo]…
[Add all those fields you need]

tblArticleSupplier [ArticleSupplierID_PK] [ArticleID] [SupplierID]

Make the relationships

Create the mainForm. Name it frmArticle

Create qryArticleSupplier and use:
tblArticleSupplier.[ArticleSupplierID] tblArticleSupplier.[ArticleID]
tblArticleSupplier.[SupplierID] tblSupplier.[CompanyName] tblSupplier[Adress]
tblSupplier[ContactPerson] tblSupplier[TlfNo]

In your mainForm you add the qryArticleSupplier as subform and hide those
field you don’t want to show like [ArticleID] you don’t need to see in
subform.

I think you should use Continuous Forms as Default View in your subform. And
make a Delete button. I have made and tested the tables and forms above. It
should work.

Good luck

Hello, wonderful forum, as I do not speak English, this is a machine
translation of Google, I hope you understand my question.

[quoted text clipped - 25 lines]

A greeting from Spain
J. Tomas

 

[jtguardado via AccessMonster.com]

Jtguardado -

The problem is in your table design. Your Articles table should only
contain information about the article (not anything about suppliers). You
should have a separate table for article_suppliers that would have id_art and
id_prov in them as foreign keys. There could be more than one record for any
article or any supplier.

Thanks Daryl S, but the problem is not the design of tables, the tables have
to be, as you say jubiiab below, the same item can belong to several
suppliers.
Sorry if the Google translation is not correct.

 

[jtguardado via AccessMonster.com]

Why don't you make the Articles as Mainform and Suppliers as subform instead?

I guess you want to register an article in your system and have information
about who can provide you that article. One article can have many suppliers
right?

Thanks jubiiab S, as you say, some items belonging to various suppliers. The
solution proposed uses me now, but later need to do a list of items belonging
to a vendor and I think I got the same problem, I'll tell you how I solve it
(if you can).
Sorry if the Google translation is not correct.

 

[jubiiab via AccessMonster.com]

If you like I can email you the DB I made with Articles? Maybe it's more easy
to understand when you see the form. Right your email and I send it if you
want or good luck with your project.

 

[jtguardado via AccessMonster.com]

If you like I can email you the DB I made with Articles? Maybe it's more easy
to understand when you see the form. Right your email and I send it if you
want or good luck with your project.

Thanks jubiiab for your interest, just testing and it works perfectly,
fortunately you have changed my point of view regarding the implementation of
the tables, now I can only adjust the entries so that everything is automatic,
but I think I will have no problem with that .
Thanks again.
Sorry I can not speak English, I hope the google translations are correct