one report to rule them all....

[shades714]

I have a DB that I want to minimize reports. So right now I have a main form and several button each that leads to forms for each department. On the department form there are several buttons that will open specific forms. What I am trying to do is set it up so that someone in department #1 opens the same exact form as someone in department #2 but the report will only display their data. Ive tried several of the suggestions I found by googling but every one fails.

[ArviLaanemets]

I assume your Departments form is based on Departments table or on query from this table, and there is a department id field available in this table/query

You create a report with either Departments table or some querystring (with must have Departments table as one of source tables) as it's Source. The query source must contain department id field! The report source (table or query), when opened/run directly, must return the info about all departments.

You create an event script (e.g. OnClick event of some button to open the report. To open the report you use something like (I assume your DepartmentID field is an autonumeric one - in case it is text, you have to enclose the value returned from Me.DepartmentIDControl between ' characters)

DoCmd.OpenReport "YourReportName", acViewReport, "WHERE DepartmentID = " & Me.DepartmentIDControl
or
DoCmd.OpenReport "YourReportName", acViewReport, "WHERE DepartmentID = '" & Me.DepartmentIDControl & "'"

[June7]

So user opens form that does not have filter applied but then you want to open a report that is filtered?

What code did you attempt?

[June7]

EDIT: Accidental duplicate post.

[Welshgasman]

I have a DB that I want to minimize reports. So right now I have a main form and several button each that leads to forms for each department. On the department form there are several buttons that will open specific forms. What I am trying to do is set it up so that someone in department #1 opens the same exact form as someone in department #2 but the report will only display their data. Ive tried several of the suggestions I found by googling but every one fails.

And you cannot say what those instructions were?

[shades714]

clarifying setup.
main form (frm_MAIN): buttons that lead to department forms (HR, IT, FIN, TNG)
department forms (frm_DEPT_HR, frm_DEPT_IT; frm_DEPT_TNG; frm_DEPT_TNG) : identical except for department title at the top.
tables: tbl_PERSON_ALL (HAS EVERYONE REGARDLESS OF DEPARTMENT) plus additional tables that are already split by department (tbl_PERSON_HR)
queries: qry_PERSON_ALL (AGAIN, HAS EVERYONE), as well as a query for each department feeding from the main table. qry_PERSON_HR; qry_PERSON_IT; etc.
report: (rpt_SMRR_PERSONNEL; rpt_SMRR_TNG; etc.
My objective is to get all department forms to open (as an example) rpt_SMRR_PERSONNEL and only be able to see the personnel in the respective departments.
I do not use auto numbering ID but rather individual names but as for a specific field to differ then I use the Department name
I hope this helps clarify what I am trying to do.
As for where I went, unfortunately I went to several websites in an effort to try and do it myself so I really don't remember any one particular site.

[ArviLaanemets]

...main form (frm_MAIN): buttons that lead to department forms (HR, IT, FIN, TNG)
department forms (frm_DEPT_HR, frm_DEPT_IT; frm_DEPT_TNG; frm_DEPT_TNG) : identical except for department title at the top.

When forms are practically identical, why bother with separate ones? You can simply have a single departments form, where the user can select the wanted department from unbound combo, ant the form will be filtered. Or on open the form will be filtered depending on the user.

tables: tbl_PERSON_ALL (HAS EVERYONE REGARDLESS OF DEPARTMENT) plus additional tables that are already split by department (tbl_PERSON_HR)

Are those department-specific tables having entirely different structures, or they are fully/mostly identical? When later, then you better have all this info in single table - it will be much easier to manage and to use.

queries: qry_PERSON_ALL (AGAIN, HAS EVERYONE), as well as a query for each department feeding from the main table. qry_PERSON_HR; qry_PERSON_IT; etc.
report: (rpt_SMRR_PERSONNEL; rpt_SMRR_TNG; etc.

Again, the same as above.

I do not use auto numbering ID but rather individual names but as for a specific field to differ then I use the Department name

And what happens, when the name of some department is changed at some point? All employees from this department are starting to work in new one without any link to old one. Or the old department name is replaced in all tables with new one (you probably must do this manually), without any reminder of old one. And you have to edit any forms, queries, and reports manually where this department code was used. Having department id used instead, you can either edit the department name whenever needed, and you don't have to change anything else in your database. Or in case you need to keep the history of your department names, you can have a separate table from where the department name is read depending on date.

Somehow I have a strong feeling your database structure is originating from some Excel workbook. Anyway, it is not normalized in any way!

[shades714]

The forms are identical except for name titles at the top. The departments will not have access to the other departments and therefore putting in the ability to select your department defeats that restriction. I have already worked the issue of restricted access. As for the tables, like I said there is a main table that has everyone then I have a query that feed from that table, I then have queries for each department so I have the data both together and separated. I guess the best way I can compare it is to sports stats in that every team has the same setup but the specific data is different per player per team. As for data change well that is not an issue I need to worry about as that has already been accounted for.

[mike60smart]

Hi
Can you upload a copy of the database?

[CJ_London]

Whatever logic you are using to decide which department form to open can be used to apply a criteria or filter to a single department form or report

[Welshgasman]

If you already have in place logic for

As for data change well that is not an issue I need to worry about as that has already been accounted for

, why can't you use the same logic for a report?
For most people that would be one report, with criteria in the report source to indicate what data is required? In your case department?

[June7]

A "query for each department" and "identical forms" is a maintenance nightmare. If they really are identical then should have only one query/form and apply filter criteria when opening (just as you wish to do for report). Title (form Caption) can be dynamic with code or don't bother with a tailored title.

Have you hidden Navigation Pane and disabled ribbon/right click menus/function keys? If you don't go to these lengths, then users are not restricted in any way.

[shades714]

Im still a little confused as to why everyone is complaining or questioning how I have it set up so please let me clarify. How it is set up will not change because it can not as it is part of a large system. I do have it set to lock out changes as well as disable editing type features. I just need to know how I can get it so that the buttons on the separate dept forms can use the same reports and only pull up their dept info rather than everyone/anyone else's. For propriety protection I can not and for privacy reasons I will not upload a copy of the database.

[Welshgasman]

Because it sounds like a lot of duplication for no reason, and duplication is generally to be avoided in databases.
Just use criteria in the report. You know what dept the user belongs to, or purport to, so use that. Inspect the open report syntax.

[June7]

Post #2 provides examples of code that open a report with filter criteria. Exactly what is not clear about the info provided?