Subform does not show up on main form

Subform does not show up on main form

Postby JohnKlassen » Mon May 05, 2014 12:51 pm

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.png
Main vendor form without subform
Main vendor form.png (25.32 KiB) Viewed 13124 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 definition.png
Vendor All defintion
Vendor All definition.png (22.46 KiB) Viewed 13124 times

and here is the definition for the subform:
Vendor subform definition.png
Vendor subform definition
Vendor subform definition.png (21.65 KiB) Viewed 13124 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.png
Vendor_projects form general defintion
Vendor_projects form general defintion.png (15.63 KiB) Viewed 13124 times

and the browse definition for the vendor_projects form.
Vendor_projects form browse definition.png
Vendor_projects form browse defintion
Vendor_projects form browse definition.png (21.41 KiB) Viewed 13124 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
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: Subform does not show up on main form

Postby massiws » Tue May 06, 2014 10:36 am

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>
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy

Re: Subform does not show up on main form

Postby JohnKlassen » Tue May 06, 2014 1:00 pm

Max,

I changed the SQL in the subform, vendor_projects as follows:
Vendor_projects form with updated SQL.png
Vendor_projects form with updated SQL
Vendor_projects form with updated SQL.png (15.92 KiB) Viewed 13117 times


The subform still does not show up on the main form.

John
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: Subform does not show up on main form

Postby massiws » Thu May 08, 2014 6:30 am

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
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy

Re: Subform does not show up on main form

Postby JohnKlassen » Sat May 10, 2014 11:39 am

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_projects form general defintion with projects table.png
vendor_project form with table of projects
Vendor_projects form general defintion with projects table.png (17.67 KiB) Viewed 13099 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
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: Subform does not show up on main form

Postby massiws » Mon May 19, 2014 8:24 am

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)
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy

Re: Subform does not show up on main form

Postby JohnKlassen » Mon May 26, 2014 1:23 pm

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.png
Default filter for browse screen
Default filter for browse screen.png (24.15 KiB) Viewed 13033 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.png
proj_vend_id as non-visible field
proj_vend_id as non-visible field.png (24.57 KiB) Viewed 13033 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.
Vendor label attached to subform.png
Subform is attached to this 'vendor' label
Vendor label attached to subform.png (12.62 KiB) Viewed 13033 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 1:26 pm

Re: Subform does not show up on main form

Postby JohnKlassen » Fri May 30, 2014 12:04 pm

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.png
Main vendor form with Projects subform
Main vendor form with projects subform.png (37.3 KiB) Viewed 13003 times

Here is the General tab for the main form:
General tab for vendor main form.png
General tab for vendor main form
General tab for vendor main form.png (12.17 KiB) Viewed 13003 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.png
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 13003 times

When you double click on the label, 'Last 5 Projects for this Vendor on the main form:
Label for subform.png
Label for subform
Label for subform.png (36.96 KiB) Viewed 13003 times

You define the field as a subform:
All tab for subform field.png
All tab for subform field
All tab for subform field.png (29.48 KiB) Viewed 13003 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.png
Definition of subform field
Definition of subform field.png (25.86 KiB) Viewed 13003 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.png
General tab for projects subform
General tab for projects subform.png (16.39 KiB) Viewed 13003 times

Finally, here is the 'browse' definition for the 'Projects' subform:
Browse definition for the Projects sub form.png
Browse definition for the Projects sub form
Browse definition for the Projects sub form.png (26.17 KiB) Viewed 13003 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
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: Subform does not show up on main form

Postby massiws » Sat May 31, 2014 9:19 pm

Ok!
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy


Return to General



cron