one report to rule them all....

[shades714]

maybe i am not clarifying it enough. How the DB is set up cannot change. so i have a main form that the departments are only able to click on their respective department buttons. then in the form for their department they select a button that displays a report. I just want to know the code I would need to use so that instead of makeing exact duplicate reports I only create one. So how can I get the buttons in each of the departments forms to link to the same report but only with their data. I understand everyone wants to try and get me to change how the database works but this just is impossible for the rest of the database (as this is not the only part of it) but it will take up too much time. To that point I will not place a copy of the database on here as it is not just about the confidential information but also the proprietary data sourcing, link connections, form, and report designs/configuration. To remove all that would mean I just put on an empty Access DB with nothing in it.

[Welshgasman]

And we do not know any different way to tell you, that you use criteria for the one report to limit that report to that dept.
Here is some of my code to select a certain ship. Same report for any of the 60 ships.

The code has an option whether to preview the report or not. You can ignore that, unless you want to offer that feature.

Private Sub cmdShip_Click()
On Error GoTo Err_cmdShip_Click


    Dim stRptName As String, stParam As String, stLinkCriteria As String, stDBpath As String, stFTPpath As String
    Dim iPreview As Integer, iDialog As Integer, blnPrintIt As Boolean
    
    stDBpath = CurrentProject.Path & "\"
    stFTPpath = stDBpath & "Gazette\"
    iPreview = acViewPreview
    If Me.ChkPreview Then
       ' iPreview = 2
        iDialog = acWindowNormal
    Else
        iDialog = acHidden
    End If
    
    stRptName = "Main_by_Ship"
    
    stParam = Replace(LCase(Me.cboShip.Value), " ", "_")
    stLinkCriteria = "[Ship] = '" & Me.cboShip.Value & "'"
    
    'DoCmd.CopyObject , stParam, acReport, stRptName
        
    If Me.ChkPreview Then
        DoCmd.OpenReport stRptName, iPreview, , stLinkCriteria, iDialog
    Else
        DoCmd.OpenReport stRptName, iPreview, , stLinkCriteria, iDialog
        DoCmd.OutputTo acOutputReport, stRptName, acFormatPDF, stFTPpath & stParam & ".pdf", False
        DoCmd.Close acReport, stRptName
    End If
    'DoCmd.DeleteObject acReport, stParam


Exit_cmdShip_Click:
    Exit Sub


Err_cmdShip_Click:
    MsgBox Err.Description
    Resume Exit_cmdShip_Click
    
End Sub

[June7]

maybe i am not clarifying it enough. How the DB is set up cannot change. so i have a main form that the departments are only able to click on their respective department buttons. then in the form for their department they select a button that displays a report. I just want to know the code I would need to use so that instead of makeing exact duplicate reports I only create one. So how can I get the buttons in each of the departments forms to link to the same report but only with their data. I understand everyone wants to try and get me to change how the database works but this just is impossible for the rest of the database (as this is not the only part of it) but it will take up too much time. To that point I will not place a copy of the database on here as it is not just about the confidential information but also the proprietary data sourcing, link connections, form, and report designs/configuration. To remove all that would mean I just put on an empty Access DB with nothing in it.

"How the DB is set up cannot change" - really, are you the developer or not? If you are adding (or changing) how report is generated, then you are changing DB setup. No one suggested you change schema, just user interface design. A single form to serve all departments makes much more sense than multiple identical forms, just as you want one report to serve all. Users certainly don't know the difference, just makes life easier for you (developer). But fine, don't change forms, however, you have already been advised numerous times how to accomplish dynamic report. Exactly what do you not understand about what is advised? Either have the same DoCmd.OpenReport code (or expanded code as shown by Welshgasman) in each form's button click event or make a public procedure in a general module with arguments and call that procedure from each button event. How many forms will you have to edit?

Do you want to filter on DepartmentID for a set of records or RecordID for a single record? Example shows for a single record. Adjust as you see fit.

Private Sub cmdReport_Click()
'could be simply
DoCmd.OpenReport "reportname", , , "RecordID=" & Me.RecordID
'or call a public procedure with argument
PrintReport Me.RecordID
End Sub

Code in general module:

Public Sub PrintReport(intID As Integer)
'code here uses parameter passed by argument
End Sub

[shades714]

it doesnt matter if I was the developer or not...it cant change so lets move on. What I am changing has nothing to do with the database. I am simply working to improve something that is protected by proprietary restrictions. So the most feasable thing I can do is redirect every dept to the same report which means I need to figure out the codeing to buttons already in place. And I am sorry if it sounds like my response was being irate or rude. IT wasnt but rather annoyed everyone is sticking to one thing despite me saying that isnt the thing I need help with.

[CJ_London]

Seems to me you are focusing on the advice you don’t want to hear and ignoring the advice to do what you actually want. Whether you have one form or a thousand duplicates, the advice for the code is the same. So suggest you review what has been suggested and if you don’t understand it, come back with your questions and ideally the code you tried and the reason it doesn’t do what you require.

review https://learn.microsoft.com/en-us/of...Cmd.OpenReport in particular the Where parameter

[June7]

it doesnt matter if I was the developer or not...it cant change so lets move on. What I am changing has nothing to do with the database. I am simply working to improve something that is protected by proprietary restrictions. So the most feasable thing I can do is redirect every dept to the same report which means I need to figure out the codeing to buttons already in place.

None of that makes sense. If database cannot be changed because of proprietary restrictions, exactly how can you modify each form to code for report filter criteria? Your GUI frontend is modifiable for everything or nothing. DB is not proprietary if you can make this modification.

As for moving on, coding has been suggested - have you tried it?

[Welshgasman]

And I am sorry if it sounds like my response was being irate or rude.

Oh it does believe me.
We have given you the solution. There might be more complicated ways of doing it, but why go looking for them.

You are free to ask elsewhere as well, but if you do, please advise of the crosspost, but be aware that you will likely get exactly the same answer, which does not appear to be of your liking.

[ArviLaanemets]

There are generally 2 ways to create/modify databases.
1. You determine what is the best and most effective way for program you are using (Access in your case) to work with your data and get your tasks done. You finish the programming, ant there will be no need to work with it anymore unless something cardinally new is needed additionally;
2. You decide beforehand, what your database must look like, and what the program you are using must do, and then you force the program to work as you imagined it at whatever cost it takes. Usually the cost is huge on programming time, on the efficiency of your database, and on the time you spend later to update your database whenever something new happens (e.g. a new department is added).

[Micron]

After reviewing all, it seems the answer is in post 2, which was pointed out in post 15. Why not just focus on that? In case post 2 is not clear, it means you filter the report that gets opened, and the filter criteria is whatever department name is in the department field of the form. Your button click event code would apply the filter to the report. If you have 4 department forms you need to do this to all of them. That is the problem that people were trying to make you aware of. If you had one form that showed the correct department name when opened, you'd only have to code one form. How that would work is unclear here, but it's the most efficient approach.
HTH

[CJ_London]

looks like this is a continuation of this thread

https://www.accessforums.net/showthread.php?t=90155

[Welshgasman]

Indeed, well spotted.
Seems the O/P didn't like the standard advice back then either.