Hi,
I am trying to get a subform to show up on a main form. I have looked at many videos and read the wiki on subforms multiple times but I can't get it to work. I also did some searches on 'subform' in the forum but there were over 300 hits and the ones I looked at didn't help me resolve this problem.
The main edit form displays information about a vendor. I want a subform that shows all of the projects (from the projects table) that this vendor has been involved in. Here is the main vendor form:
I want the 'vendor' name field (vend_company) to be the common field between the main form and the subform. When I click on the 'Vendor' label, I get the following for the 'All' definition:
and here is the definition for the subform:
Here is the general defintion for the 'vendor_projects' form which is the subform I want displayed on the 'vendor' form:
and the browse definition for the vendor_projects form.
I realize that there may be some problems with the SQL and some of the definitions but I can't even get a blank subform to show on the main form. I tried to use the subform wizard but it created the subform in a separate tab for the vendor form and I couldn't figure out how to move it to the main form. Some of the subform examples in the forum use a 'lookup' field on the main form but I didn't see a reason for a 'lookup' field for the vendor name since I either selected the vendor from the browse screen or created a new vendor using 'Add Record'.
As before, I would appreciate your pointing me in the right direction.
Thanks,
John
Welcome to the nuBuilder forums!
Please register and login to view forums and other content only available to registered users.
Please register and login to view forums and other content only available to registered users.
Subform does not show up on main form
-
- Posts: 503
- Joined: Thu May 24, 2012 2:08 am
- Location: Milan, Italy
- Contact:
Re: Subform does not show up on main form
John, are you sure about the SQL in subform?
It should be:
It should be:
Code: Select all
SELECT * FROM <subform-table> INNER JOIN <main-table> ON <sub_main-table_id> = <main-table_id>
-
- Posts: 148
- Joined: Wed Dec 05, 2012 4:56 am
Re: Subform does not show up on main form
Max,
I changed the SQL in the subform, vendor_projects as follows: The subform still does not show up on the main form.
John
I changed the SQL in the subform, vendor_projects as follows: The subform still does not show up on the main form.
John
-
- Posts: 503
- Joined: Thu May 24, 2012 2:08 am
- Location: Milan, Italy
- Contact:
Re: Subform does not show up on main form
John, the subform gets records from sub-table, in your case, vendor_projects: in subform -> All tab, you should set Table field => vendor_projects and Primary Key => proj_vend_id.
Max
Max
-
- Posts: 148
- Joined: Wed Dec 05, 2012 4:56 am
Re: Subform does not show up on main form
Max,
I am confused. I don't see any place on the vendor_projects subform where there is an All tab. The only place I could see to specify the Table Field and the Primary key field is the General tab. I changed the Table field to 'Projects' since that is the table that contains the data for the subform. The Primary key for the Projects table is 'project_id'. I made the above change but the subform still does not show up on the main form.
What causes the subform to show up on the main form even if there is no data for the subform?
Thanks for your help so far.
John
I am confused. I don't see any place on the vendor_projects subform where there is an All tab. The only place I could see to specify the Table Field and the Primary key field is the General tab. I changed the Table field to 'Projects' since that is the table that contains the data for the subform. The Primary key for the Projects table is 'project_id'. I made the above change but the subform still does not show up on the main form.
What causes the subform to show up on the main form even if there is no data for the subform?
Thanks for your help so far.
John
-
- Posts: 503
- Joined: Thu May 24, 2012 2:08 am
- Location: Milan, Italy
- Contact:
Re: Subform does not show up on main form
John, sorry for delay, General is the right tab.
In subform you have this SQL:
so I think the common field (FK) is vendor_id, not vend_company: isn't it?
You can try this:
In subform you have this SQL:
Code: Select all
SELECT * FROM projects INNER JOIN vendor ON proj_vend_id = vend_id
You can try this:
- in subform -> Default Filter for Browse Screen insert #vendor_id# (or maybe just #id#, if vendor_id is the PK in vendor table);
- in the browse definition for the vendor_projects form add a new row with: Display: proj_vend_id - Visible: No (fill other fields like you want)
-
- Posts: 148
- Joined: Wed Dec 05, 2012 4:56 am
Re: Subform does not show up on main form
Max,
I made the changes you recommended and I still don't see a sub-form.
In the 'vendor' edit form, I clicked on the 'Vendor' label and then went to the subform tab. I changed the Default Filter for Browse Screen value to "#vendor_id#. I also tried just '#id#'. I also added the 'proj_vend_id' as a non-visible field in the browse definition for the 'vendor_projects' form. I am still confused as to what causes a subform to be displayed. Does every definition including the SQL statements have to be perfect? Should the subform show up in the main form just by specifying the name of the subform in the 'Browse in Subform' field for the 'vendor' name field?
I am assuming that the subform is attached to the 'vendor' label. I am double-clicking on the 'vendor' label and then clicking on the subform tab to specify the name of the subform, 'vendor_projects' in the 'Browse in Subform' field.
I appreciate all of our help.
Thanks,
John
I made the changes you recommended and I still don't see a sub-form.
In the 'vendor' edit form, I clicked on the 'Vendor' label and then went to the subform tab. I changed the Default Filter for Browse Screen value to "#vendor_id#. I also tried just '#id#'. I also added the 'proj_vend_id' as a non-visible field in the browse definition for the 'vendor_projects' form. I am still confused as to what causes a subform to be displayed. Does every definition including the SQL statements have to be perfect? Should the subform show up in the main form just by specifying the name of the subform in the 'Browse in Subform' field for the 'vendor' name field?
I am assuming that the subform is attached to the 'vendor' label. I am double-clicking on the 'vendor' label and then clicking on the subform tab to specify the name of the subform, 'vendor_projects' in the 'Browse in Subform' field.
I appreciate all of our help.
Thanks,
John
-
- Posts: 148
- Joined: Wed Dec 05, 2012 4:56 am
Re: Subform does not show up on main form
Max,
I finally got the sub form to show. I thought that the sub form was tied to the first field in the main form. I finally realized you have to add a special field in the browse form for the main form and define that field as a sub form. For the benefit of anyone else who may have problems getting sub forms to work, I will describe the who process here.
This is what the final product likes like: Here is the General tab for the main form: In the 'Reorder Form Objects' definition for the main form, it is critical to add an additional field for the sub form. In my case I called this field 'FMVendorProjects' with a 'Title' of 'Last 5 Projects for this Vendor' which is what shows on the main form. When you double click on the label, 'Last 5 Projects for this Vendor on the main form: You define the field as a subform: Note that type is subform. Title is what displays on the main form. Tab Title is important, Tab Number, Column Number and Order have some impact on where the subform shows up in the main form.
Here is the definition of the subform field: The 'Browse in subform' points to the form definition of the subform. The 'Default Filter for Browse Screen' is '#id#'. You can play with the other fields to determine where the subform is placed and how it is displayed.
Here is the General tab for the projects subform: Finally, here is the 'browse' definition for the 'Projects' subform: Note the 'Rows per page' is filled in. I have a maximum of 5 rows. The 'proj_vend_id' is the foreign key and is critical. It must be defined in the Browse definition. I have it defined as not visible.
Hopefully this will be helpful to others.
John
I finally got the sub form to show. I thought that the sub form was tied to the first field in the main form. I finally realized you have to add a special field in the browse form for the main form and define that field as a sub form. For the benefit of anyone else who may have problems getting sub forms to work, I will describe the who process here.
This is what the final product likes like: Here is the General tab for the main form: In the 'Reorder Form Objects' definition for the main form, it is critical to add an additional field for the sub form. In my case I called this field 'FMVendorProjects' with a 'Title' of 'Last 5 Projects for this Vendor' which is what shows on the main form. When you double click on the label, 'Last 5 Projects for this Vendor on the main form: You define the field as a subform: Note that type is subform. Title is what displays on the main form. Tab Title is important, Tab Number, Column Number and Order have some impact on where the subform shows up in the main form.
Here is the definition of the subform field: The 'Browse in subform' points to the form definition of the subform. The 'Default Filter for Browse Screen' is '#id#'. You can play with the other fields to determine where the subform is placed and how it is displayed.
Here is the General tab for the projects subform: Finally, here is the 'browse' definition for the 'Projects' subform: Note the 'Rows per page' is filled in. I have a maximum of 5 rows. The 'proj_vend_id' is the foreign key and is critical. It must be defined in the Browse definition. I have it defined as not visible.
Hopefully this will be helpful to others.
John
-
- Posts: 503
- Joined: Thu May 24, 2012 2:08 am
- Location: Milan, Italy
- Contact: