Repeating subform / Grouped header

[Gladman]

Hi All,

I created a subfrom that graphically displays the performance of a certain sector / employee in a business. From this I have created a main form which I can select branch (London / Birmingham etc.) the form then displays the Branch details in the header and the detail displays the subform for the sales by employee / sector. The bug is that only 1 employee is displayed per page and the header is repeated for the next employee. The subform is related by EmployeeID. If I try BranchID;EmployeeID it still works but only displays that employees sales from that branch only (a feature that will be useful when I can get the display to work )
I want all employees to show below a single header. So, is there a method of me repeating the subform multiple times or to group by the BranchID?

Many thanks

 

[Uncle Gizmo]

>>>So, is there a method of me repeating the subform multiple times<<< I don't think it's the way you should go with this. If you have repeating items, you should list them in a form in datasheet view or continuous form view. This way you don't have to worry about how many items there are. MS Access will cater for it automatically.

 

[Gladman]

Hi Uncle Gizmo,

Thank you for your reply. When I change the main for to a continuous form I get the error that "You can't view a form as a continuous form if it contains a subform, ActiveX Control, a bound chart or Web browser control" and I am not sure how to work around this?

When I set the main form to datasheet view it showed the Branch Details 10 times if there are 10 employees and expanding each shows the specific employees so doesn't give me the desired result

Thanks for your help.

 

[Uncle Gizmo]

I'm not 100% clear on how your data relates together, and I'm not clear on what results you want to see. I may select the the tables in the wrong order, you may require different ordering. However I suspect you want to start off with a Main Form based on the tblBranch.

This table would contain an ID field "branchID"... This main form would show a single branch, London, Birmingham, Other1, Other2, etc... On this main form you would have a subform listing the employees working at the particular branch (selected on the main form).

This subform would display records from the employee table "tblEmployee" you would have a field in the employee table containing the same ID (branchID) as the tblBranch on the main form. This ID would be used in the linking criteria between the main form and subform to display just the list of employees related to the branch selected on the main form.

 

[Gladman]

You have it spot on there but I want to see means I really need to repeat the subform and enter the various employeeID as a parameter. I have created a parameter form that works but I just need it to repeat x times as the charts also display data outside that branch as employees often move and if this happens their charts will begin from 0.

I can sort of see where the issue is and it is in the main form having an employeeID field which is used to relate to the subfield but this is the only way the charts return complete but they are included as a group by in the BranchID. If I relate to branchID the data is incomplete for cross branch employees. I'm hoping there is a method to include a top level detail but the subform calculations will ignore the top level filter, if that makes any sense

 

[Gladman]

I have got a little bit closer with what I want by creating a midway form containing the EmployeeName in a text box and is linked to the graphical subform via the EmployeeID. Both of these are in the form footer so I can set the form to continuous. This subform is the inserted into the Main Form using the BranchID for the relationship. Now the Main form seems to group correctly (using the record count in the lower search bar) but the employees are displayed with 1 employee at a time and I can cycle through by using the arrows in the record count bar.
I know it is not allowed but I need the graphical subform to be in the detail section of the employeeName subform in order to view the info correctly and I feel I'm so close :banghead:

Would a report be able to handle this situation any better?

 

[CJ_London]

sounds to me that all you need to do is make your graphical subform a continuous form and include the employee detail there rather than you 'mainform'

 

[Gladman]

Hi CJ,

I have the employeeID in the graphical subform which is contimuous. This displays all details across all branches in a neat little chart. I need this to be displayed in the main form and relating the graphical sunbform to the main form via the branchID filters the graph down.

That is why I created the EmployeeName form which dispalys the EmplyeeName & BranchID from the main form header. The EmployeeName form relates to the graphical subform via the employee ID (all data is stored in the from footer and that form is now continuous. I want it in the detail section but that makes the form a single form view only and I need continuous) and the BranchID relate to the branch ID of the main form. This gives me the results I wanted but I had to use the arrows in the record count bar at the bottom of the EmployeeName form instead of it being repeated down the list.

I converted my forms to reports and it seems to help the layout but I got the error message "Cannot open any more databases" so I believe I should now use temporary tables to reduce the demand on queries to create the data. Which I got from here; https://access-programmers.co.uk/forums/showthread.php?t=122973

Are reports designed for creating forms with multiple subforms?

 

[CJ_London]

reports are reports and forms are forms. But a report can use a subform and a form can use a subreport. Forms can be printed, but not with the same level of flexibility as a report.

You can easily convert a form to a report, but not the other way round (one reason being reports have additional grouping and sorting functionalities).

Report sorting should be carried out in the report, sorting of the recordsource is ignored.

I often have a main form displaying a report in a subform control.

 

[Gladman]

Thanks for the comments.
To create the reports all I did was open the form and save object as report and then combined them as I did the forms.
I've not had much experience with reports but I will look into going this way as you mention the sorting in the record source is ignored i think this is the only way I can do this.

One last question before I look at how the sorting works;
Is there a preferred method for combining forms and reports? ie. main Form containing a subreport or main report containing subforms? (I just want to keep with best practices)

Thank you bith for your help.

 

[CJ_London]

no real preference, depends on what you are going to do with the data and how you interact with it. In practical terms, forms are generally for the screen and reports for output (printer, pdf, etc).

for example I use a report for displaying a notes history table in a form subform control - means I can use the cangrow/shrink functionality

User can scroll through but not edit - they have a different control for that