Sync Main FORM with SubForm

[sap4ora]

I have two tables in a one-to-many relationship as following:

Table1: [ pkeyColumnID, ProductType, Factory] ……..One Side
Table2: [fkeyColumnID, ProductType, Factory, Status,……..] ………..many side


Created main form based on query1 that lists ProductType and it’s location
per factory
The ProductType and Factory are ComboBox and are non-bound

Created Submain form based on a select query2 that lists items out of table2
(simple select Query)

Q: I have added the submain form to the main form to have users just enters
the ProductType from a drop list and Factory then the subform should show the
details based on the users selection?

Could you please help?

Main form:
Data: Record Source: Query1

Subform:
Data: Record Source: Query2 and Source Subject: Subform
LinkChildFields: fkeyCoulmnID
LinkMasterFields: pkeyCoulmnID

I had attempted to use the ProductType and Factory in linking the main to
the sub but with no success.




Thanks,

 

[Khoa]

Hi there.
You seem to reduplicate your data, and your DB may need to be normalized.
(The ProductType, Factory fields exist in both table ?)... but it is another
issue. Here is an instant solution for your request:
1. Create an unbound Mainform (No record Source).
2. Add ComboBox, name it Combo1, set the Row Source to: SELECT DISTINCT
Table1.ProductType FROM Table1 ORDER BY Table1.ProductType;
3. Add another ComboBox, name it Combo2, set the Row Source to: SELECT
DISTINCT Table1.Factory FROM Table1 ORDER BY Table1.Factory;
4. Add your Subform that you already created to the above form. Set:
LinkChildFields: ProductType;Factory
LinkMasterFields: Combo1;Combo2
Hope this help.
FYI: If you success, then try to change the Row Source of Combo1 and Combo2,
replace "Table1" by "Table2", then you'll see that in this case, you don't
even need the Table1 !

 

[sap4ora]

Hello,

I know about the data duplications in both of the tables. My plan is to
clean it up. I followed what you had suggested, still not getting what I’m
after:

1- When I tried the combo box Row Source “SELECT statement†without the
pkeyCoulmnID I get an empty drop down list?
2- I have added the pkeyCoulmnID, now I get total list for ProductType and
Factory including repeated value. Very much is an out put from a regular
select statement?
3- I make my selections and the SubForm shows nothing, it doesn’t seem they
are connected?


I’m still after creating a main form that includes two combo boxes from
table1, and a subform from table2 that reflects user’s selection from the
combo boxes in table1? The two tables are connected via a pkeyCoulmnID?



Thanks,

Hi there.
You seem to reduplicate your data, and your DB may need to be normalized.
(The ProductType, Factory fields exist in both table ?)... but it is another
issue. Here is an instant solution for your request:
1. Create an unbound Mainform (No record Source).
2. Add ComboBox, name it Combo1, set the Row Source to: SELECT DISTINCT
Table1.ProductType FROM Table1 ORDER BY Table1.ProductType;
3. Add another ComboBox, name it Combo2, set the Row Source to: SELECT
DISTINCT Table1.Factory FROM Table1 ORDER BY Table1.Factory;
4. Add your Subform that you already created to the above form. Set:
LinkChildFields: ProductType;Factory
LinkMasterFields: Combo1;Combo2
Hope this help.
FYI: If you success, then try to change the Row Source of Combo1 and Combo2,
replace "Table1" by "Table2", then you'll see that in this case, you don't
even need the Table1 !

I have two tables in a one-to-many relationship as following:

Table1: [ pkeyColumnID, ProductType, Factory] ……..One Side
Table2: [fkeyColumnID, ProductType, Factory, Status,……..] ………..many side


Created main form based on query1 that lists ProductType and it’s location
per factory
The ProductType and Factory are ComboBox and are non-bound

Created Submain form based on a select query2 that lists items out of table2
(simple select Query)

Q: I have added the submain form to the main form to have users just enters
the ProductType from a drop list and Factory then the subform should show the
details based on the users selection?

Could you please help?

Main form:
Data: Record Source: Query1

Subform:
Data: Record Source: Query2 and Source Subject: Subform
LinkChildFields: fkeyCoulmnID
LinkMasterFields: pkeyCoulmnID

I had attempted to use the ProductType and Factory in linking the main to
the sub but with no success.




Thanks,

 

[Khoa]

Hi,
Do you type the name of the fields correctly - If correct, it should show
the unique value in each Combo Box. Let's try another way, step by step:
Assume you already have the Mainform (Unbound) with 2 Combo Boxs and the
Subform in it.
1. Select Combo1, set :
Row Source Type : Table/Query
Row Source: Table2
Click on the ... button to the right, (answer Yes), it should open SQL
Statement in Design mode. Double click "ProductType"; Sort Ascending. Double
click the gray area to open Query Properties dialog. Set Unique Values: Yes.
Unique Records: No.
Close the Query Builder. (Yes).
Save your form. Now open it in in view mode, click the Combo1 - it should
show a dropdown list with Unique value.
2. Repeat with Combo2, replace "ProductType" by "Factory"
3. Click on your Subform. Make sure the Source Object is the name of your
Subform. Set:
LinkChildFields: ProductType;Factory
LinkMasterFields: Combo1;Combo2

Good luck.
(You could even reduce the List of Combo2 base on Combo1 either, but it
should be in another Post).

Hello,

I know about the data duplications in both of the tables. My plan is to
clean it up. I followed what you had suggested, still not getting what I’m
after:

1- When I tried the combo box Row Source “SELECT statement†without the
pkeyCoulmnID I get an empty drop down list?
2- I have added the pkeyCoulmnID, now I get total list for ProductType and
Factory including repeated value. Very much is an out put from a regular
select statement?
3- I make my selections and the SubForm shows nothing, it doesn’t seem they
are connected?


I’m still after creating a main form that includes two combo boxes from
table1, and a subform from table2 that reflects user’s selection from the
combo boxes in table1? The two tables are connected via a pkeyCoulmnID?



Thanks,

Hi there.
You seem to reduplicate your data, and your DB may need to be normalized.
(The ProductType, Factory fields exist in both table ?)... but it is another
issue. Here is an instant solution for your request:
1. Create an unbound Mainform (No record Source).
2. Add ComboBox, name it Combo1, set the Row Source to: SELECT DISTINCT
Table1.ProductType FROM Table1 ORDER BY Table1.ProductType;
3. Add another ComboBox, name it Combo2, set the Row Source to: SELECT
DISTINCT Table1.Factory FROM Table1 ORDER BY Table1.Factory;
4. Add your Subform that you already created to the above form. Set:
LinkChildFields: ProductType;Factory
LinkMasterFields: Combo1;Combo2
Hope this help.
FYI: If you success, then try to change the Row Source of Combo1 and Combo2,
replace "Table1" by "Table2", then you'll see that in this case, you don't
even need the Table1 !

I have two tables in a one-to-many relationship as following:

Table1: [ pkeyColumnID, ProductType, Factory] ……..One Side
Table2: [fkeyColumnID, ProductType, Factory, Status,……..] ………..many side


Created main form based on query1 that lists ProductType and it’s location
per factory
The ProductType and Factory are ComboBox and are non-bound

Created Submain form based on a select query2 that lists items out of table2
(simple select Query)

Q: I have added the submain form to the main form to have users just enters
the ProductType from a drop list and Factory then the subform should show the
details based on the users selection?

Could you please help?

Main form:
Data: Record Source: Query1

Subform:
Data: Record Source: Query2 and Source Subject: Subform
LinkChildFields: fkeyCoulmnID
LinkMasterFields: pkeyCoulmnID

I had attempted to use the ProductType and Factory in linking the main to
the sub but with no success.




Thanks,