Welcome to the nuBuilder forums!

Please register and login to view forums and other content only available to registered users.

Subform does not show up on main form

Locked
JohnKlassen
Posts: 148
Joined: Wed Dec 05, 2012 4:56 am

Subform does not show up on main form

Unread post by JohnKlassen »

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:
Main vendor form without subform
Main vendor form without subform
Main vendor form.png (25.32 KiB) Viewed 21609 times
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:
Vendor All defintion
Vendor All defintion
Vendor All definition.png (22.46 KiB) Viewed 21609 times
and here is the definition for the subform:
Vendor subform definition
Vendor subform definition
Vendor subform definition.png (21.65 KiB) Viewed 21609 times
Here is the general defintion for the 'vendor_projects' form which is the subform I want displayed on the 'vendor' form:
Vendor_projects form general defintion
Vendor_projects form general defintion
Vendor_projects form general defintion.png (15.63 KiB) Viewed 21609 times
and the browse definition for the vendor_projects form.
Vendor_projects form browse defintion
Vendor_projects form browse defintion
Vendor_projects form browse definition.png (21.41 KiB) Viewed 21609 times
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
massiws
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: Subform does not show up on main form

Unread post by massiws »

John, are you sure about the SQL in subform?
It should be:

Code: Select all

SELECT * FROM <subform-table> INNER JOIN <main-table> ON <sub_main-table_id> = <main-table_id>
JohnKlassen
Posts: 148
Joined: Wed Dec 05, 2012 4:56 am

Re: Subform does not show up on main form

Unread post by JohnKlassen »

Max,

I changed the SQL in the subform, vendor_projects as follows:
Vendor_projects form with updated SQL
Vendor_projects form with updated SQL
Vendor_projects form with updated SQL.png (15.92 KiB) Viewed 21602 times
The subform still does not show up on the main form.

John
massiws
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: Subform does not show up on main form

Unread post by massiws »

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
JohnKlassen
Posts: 148
Joined: Wed Dec 05, 2012 4:56 am

Re: Subform does not show up on main form

Unread post by JohnKlassen »

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'.
vendor_project form with table of projects
vendor_project form with table of projects
Vendor_projects form general defintion with projects table.png (17.67 KiB) Viewed 21584 times
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
massiws
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: Subform does not show up on main form

Unread post by massiws »

John, sorry for delay, General is the right tab.
In subform you have this SQL:

Code: Select all

SELECT * FROM projects INNER JOIN vendor ON proj_vend_id = vend_id
so I think the common field (FK) is vendor_id, not vend_company: isn't it?
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)
JohnKlassen
Posts: 148
Joined: Wed Dec 05, 2012 4:56 am

Re: Subform does not show up on main form

Unread post by JohnKlassen »

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#'.
Default filter for browse screen
Default filter for browse screen
Default filter for browse screen.png (24.15 KiB) Viewed 21518 times
I also added the 'proj_vend_id' as a non-visible field in the browse definition for the 'vendor_projects' form.
proj_vend_id as non-visible field
proj_vend_id as non-visible field
proj_vend_id as non-visible field.png (24.57 KiB) Viewed 21518 times
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.
Subform is attached to this 'vendor' label
Subform is attached to this 'vendor' label
Vendor label attached to subform.png (12.62 KiB) Viewed 21518 times
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
JohnKlassen
Posts: 148
Joined: Wed Dec 05, 2012 4:56 am

Re: Subform does not show up on main form

Unread post by JohnKlassen »

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:
Main vendor form with Projects subform
Main vendor form with Projects subform
Main vendor form with projects subform.png (37.3 KiB) Viewed 21488 times
Here is the General tab for the main form:
General tab for vendor main form
General tab for vendor main form
General tab for vendor main form.png (12.17 KiB) Viewed 21488 times
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.
Form object in vendor main form that points to project subform
Form object in vendor main form that points to project subform
Form object in vendor main form that points to project subform.png (31.52 KiB) Viewed 21488 times
When you double click on the label, 'Last 5 Projects for this Vendor on the main form:
Label for subform
Label for subform
Label for subform.png (36.96 KiB) Viewed 21488 times
You define the field as a subform:
All tab for subform field
All tab for subform field
All tab for subform field.png (29.48 KiB) Viewed 21488 times
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:
Definition of subform field
Definition of subform field
Definition of subform field.png (25.86 KiB) Viewed 21488 times
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:
General tab for projects subform
General tab for projects subform
General tab for projects subform.png (16.39 KiB) Viewed 21488 times
Finally, here is the 'browse' definition for the 'Projects' subform:
Browse definition for the Projects sub form
Browse definition for the Projects sub form
Browse definition for the Projects sub form.png (26.17 KiB) Viewed 21488 times
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
massiws
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: Subform does not show up on main form

Unread post by massiws »

Ok!
Locked