I have a project having front end and backend DB with multiuser login logout function. Each user has its own FE. In each FE, there is are reports that i want to be shared with other users too at the same time (Similar to data in backend). Is there any work around in which a report is shared with other users on another pc.
What if each user opens the same report with the same parameters so they are looking at the same report showing the same data, but each in their own FE. How is that scenario different than the sharing you are talking about?
My project is for Ms Access 2010 that supports pivot table reports. Pivot reports are changable easily by user and saved in only his specific FE. I want it to be saved for other users too. I know that Change a FE will never ever change the FE on other users computer.
So i got one idea of having another db having only these reports which is placed on BE path. FE db contains only links to these reports. This way, every user can open these reports and can change and save it, provided no other user have simoltaneously opened the same report.
An interesting idea, but the problem will always be either (a) destructive interference, when people "collide" on a report or (b) lock collisions that lead to potential database corruption.
This sounds like you do not have people "trapped" in a tight environment (where they cannot see anything but a switchboard form). The question is whether your database is stable if your users aren't bound to a switchboard or dispatcher. At least from your description, people have access to change reports, which means they see a command ribbon. Which means they can do things to the back end they should not be able to do.
Understand, I'm not saying that your solution is wrong for your problem. It is just that normally, a solution for shared databases involves a locked-down environment.
So i got one idea of having another db having only these reports which is placed on BE path. FE db contains only links to these reports. This way, every user can open these reports and can change and save it, provided no other user have simoltaneously opened the same report..
I know that pivot tables are not in Ms Access 2013/2016 but I hate why they removed it. Thats why i prefer Ms access 2010. I have both reports i.e. normal reports and pivot table reports. I used pivot table reports to allow users to have powerful analysis of Data e.g. Sales By Customer by Month. (Normal Reports do not support such Dynamic Reports)(Now Don't say me to use Cross-Tab Queries )
I hate to break it to you, but Galaxiom and The Doc Man *ARE* VBA experts. I dunno about Galaxiom, but Doc was an Access MVP for a few years. Discounting their advice on Access just because you don't like what they're saying isn't really an idea I'd recommend.
I do respect The Doc Man replies. I was only frustrated due to useless replies suggesting me to withdraw my objective. I do not withdraw my objective and will implement my idea that was appreciated by The Doc Man (Thanks Doc Man).
I used the term VBA expert in general and does not mean to make someone else inferior. I do not know here who is expert and who is not.
Moreover, crosstab queries are itself dynamic but normal reports are not dynamic. so this will not works for me. If you can make reports dynamic too, then plz share how?
Its not difficult and the technique is very powerful.
Use code like this in the Report_Open event
The recordsource strSQL1 is based on a suitable crosstab query
Code:
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Handler
'===================================================
'Dynamic report designed to be used with Crosstab Queries
'This code is especially "tuned" for crosstab queries.
'As I like to have control over the layout, thus I have the lay-out designed first with "coded" controls.
'Then the dynamic filling becomes very easy. The raw text I use to help with this is:
'Making the column header and detail data flexible is possible,
'but needs some VBA code in the OpenReport event.
'To start doing this you need to place the fields "coded" in the report.
'The column headings should be called "lblCol1", "lblCol2", "lblCol3", etc.
'The "detail" fields should be called "Col1", "Col2", "Col3", etc.
'The report query has two row header columns and a Total column,
'therefore the first field is effectively column 4 (count starts at 0 so I used intI=3)
'but this could differ for you.
'Make sure that the number of Columns is not bigger than the number placed.
'The program code has no protection against that.
'=====================================================
Dim intI As Integer
Dim Rs As DAO.Recordset
strSQL1 = ""[color="#FF0000"]<== type your recordsource here[/color]
Set Rs = CurrentDb.OpenRecordset(strSQL1)
'Place crosstab column headers - [COLOR="Red"]in example below these start at column 4 - adapt as necessary[/COLOR]
For intI = 5 To Rs.Fields.Count - 1
Me("lblCol" & intI - 4).Caption = Rs.Fields(intI).Name
Me("lblCol" & intI - 4).visible = True
Next intI
'Place controls in Detail section
For intI = 5 To Rs.Fields.Count - 1
Me("Col" & intI - 4).ControlSource = Rs.Fields(intI).Name
Me("Col" & intI - 4).visible = True
Next intI
'Place Total field
'Me.ColTotal.ControlSource = "=SUM([" & rs.Fields(2).Name & "])"
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & err.Number & " in Report_Open procedure : " & err.Description
Resume Exit_Handler
End Sub
Here is one example of a dynamic crosstab report. I have many others ...
As I said, I do not wish to say that your solution is wrong if your problem requires it. We cannot see your problem "up close" for obvious reason - distance being first, but not the only reason. However, I was actually reacting to other comments you had made.
I will give you an overview of something I did for my biggest project, and it DID involve dynamically created reports. To use this, I built a form with some option groups. In order for it to work, they each had to represent potential filters on tables that ALSO existed in the database and for which appropriate formal Relations existed.
On the form, I had people select from the various options. For example, I had the ability to look at a person's work or a project's work or the list of ALL projects. Where the "ALL" was selected, I turned off the project and person combo boxes. Where "PERSON" was select, I turned on the person selector. Where "PROJECT" was selected, I turned on the project selector.
For my case, another possible selector was "Machines" and I could pick "ALL" or "SINGLE" or "SPECIFIC O/S" - and again, I had selectors. I also had date filtration and a status filtration, again with various ways of selecting.
The next part of this was that I had a bunch of pre-formatted reports that would use a dynamic query. I split each report's query into several parts - the SELECT clause and the FROM clause were essentially constant as part A, the WHERE clause with all the filters was part B, and the ORDER BY clause was part C. That was usually a constant for a given report.
The idea is you would select a report by clicking a particular Option Group button. That report name selection told me which fields to enable for further selection, so I would enable each option group for the selectable fields (and disable anything that didn't apply.) Then my users could select the options. When done, they could click the button to Open (or Print) the report.
When they clicked that button, my code stepped in and altered the report's .RecordSource by taking the two constant parts, building the dynamic filter statement, and updating the .RecordSource property for that report. The code looked at each option group and insert a string to the PartB variable appropriate to the selection. For instance, if you wanted to see info on project, your selection opened the report for systems by project. If you wanted it based on the system admin, you would open the report for systems by admin. Etc.
The code for ONE PART of that might be...
Code:
Select Case ogUser
Case 0 'case 0 represented the "ALL" selection
PartB = PartB & " AND [UserID] <> 0 "
Case 1
PartB = PartB & " AND [UserID] = " & CStr( cboUser ) & " "
Case Else
MsgBox "Invalid User Selection", vbOKOnly, "Internal Error"
GoTo AbortSelection
End Case
Then when ALL of the various option groups had contributed to PartB, I joined the PartA, PartB, and PartC strings into a single query that had the requirements all filled in.
This took a long time to set up - but to the users it was a matter of clicking a desired report, choosing the selectivity, and clicking the Open (or Print) button.
For a CrossTab report, you might need to experiment to see what the appropriate query or report resembles, but it will start with TRANSPOSE (rather than SELECT). Do a few different reports like that and you will begin to understand the things you need to include in the dynamic part of that query.
Even better (simpler), you COULD use the approach to just build a dynamic query that is based on a TRANSPOSE rather than a SELECT, and you could then customize the query to show what you wanted, even to given proper column headers in the query using the "field AS other-name" syntax. If you cared to take the approach, the Open (or Display) and Print buttons could even be supplemented with an Export to Excel button. There are DoCmd.OpenQuery options to allow all sorts of things once you have the query.
I'm not saying this would be easy. It would be quite tedious. It might take a lot of testing to get the parts to work together. But if you want a truly dynamic report in spreadsheet format and still have it in a protected environment, this is one approach.
The reason I hesitated earlier is that this can be a daunting solution. It is decidedly NOT for the faint of heart. It will make your brain hurt until you get it going enough to see the light at the end of the tunnel. Testing would be ugly because of all the options that would have to be tested. But when it works, you look like a genius. Of course, Thomas Edison once said that "Genius is one percent inspiration and ninety-nine percent perspiration."
Further to Doc's response there is material at Martin Green's site re Dynamic Reports. You may get some ideas to mix/match with your needs and Doc's suggestion, ridders' material etc.
The info at the link in my previous post may be an option if you really need to have pivot table/report. You could talk to the supplier for details.
Good luck.
Checkout this Image of my Pivot Table Reports. These are quick to build and are very dynamic. Also these are changable easily to charts and user can add remove fields like the way they want. Also there are lots and lots of options for user like the one i like is Calculated Fields
But my question was mainly to share reports with other users. Like one junior employee creates a report and share it with Manager etc.
Attachments
Advanced Report Graph Income Expense By Fiscal Year.png
The problem with sharing is that there is only one place that IS shared, and that is the back end file. The multiple front-end files, because they are each on a different machine, would have to open up sharing in a way that most security managers would not allow. But the moment you share something dynamic in the back-end file, you open up that file to a dangerous level of exposure.
I can't tell from your picture how this works in a production copy, but if that WAS from a production copy, you are running it wide open, no protection whatsoever. The navigation pane is exposed and the ribbon is there to allow various actions.
This IS your choice, but you risk a LOT by running that way. All it takes is one well-meaning but totally clueless individual to leave you with a corrupted back end. That was my original point.
If I understand the question you can use the "In" operator to Select Data from another file in a query.
In SQL it would be typed in as:
Code:
SELECT * FROM ExternalQuery In 'C:\ExternalFolder\ExternalFile.accdb'
In the graphical interface, open the properties window and enter the external location in the Source Database box. Then when you click on the Show Table button, you can select queries as well as tables
taifoor - regarding the other thread, there is no doubt. You are asking for something not consistent with stable databases. Either you let your people have design mode so they can customize the world (and in the process, leave behind an impossible mess); or you can give people some pre-defined items. However, as was noted, if you allow for ANOTHER utility to become involved with a COPY of the raw data, you can allow your users to do what they please with that copy.
It should be possible to use either Word and MailMerge or Excel and an exported query to allow all sorts of formatting. But Access itself is NOT designed for end users to take control of the infrastructure. It is just not a good idea to have people making changes to shared items when they are not trained in details of using MS Access. All it takes is ONE well-meaning but misinformed soul to introduce data corruption EVERYWHERE.
I cannot speak for your environment, but where I worked, we had to tightly monitor what a user could do and we had to build the database to protect against accidents. Having a person "adjust" database parameters or (worse) "adjust" data records would have made the entire database useless and untrustable. And if you can't trust it, why bother with it?
)
